原创

POI中excle样式怎么写

POI中可能会用到一些需要设置EXCEL单元格格式的操作小结:

先获取工作薄对象:

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();

一、设置背景色:

setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

二、设置边框:

public static CellStyle getStyle(Workbook wb ){
CellStyle style = wb.createCellStyle();
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setBorderTop(CellStyle.BORDER_THIN);


return style;
}

三、设置居中:

style.setAlignment(CellStyle.ALIGN_CENTER);//水平居中 
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);//垂直居中 

四、设置字体:

HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小

HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽:

sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值

六、设置自动换行:

setBorder.setWrapText(true);//设置自动换行

七、合并单元格:

Region region1 = new Region(0, (short) 0, 0, (short) 6);

//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
sheet.addMergedRegion(region1);

八、加边框

  HSSFCellStyle cellStyle= wookBook.createCellStyle();
  cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  cellStyle.setBorderBottom(HSSFCellStyle.BorderBORDER_MEDIUM);
  cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
  cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
  cellStyle.setTopBorderColor(HSSFColor.BLACK.index);

一个不完整的例子:

public static HSSFWorkbook generateExcelFiles(File file, StatisticQueryBean bean, List<WGLineBean> beans) {
HSSFWorkbook wb = null;
try {
InputStream inputStream = new FileInputStream(file);
wb = new HSSFWorkbook(inputStream);
HSSFSheet sheet = wb.getSheet("sheet1");
sheet.getRow(2).getCell(2).setCellValue(bean.getCarType());
sheet.getRow(2).getCell(4).setCellValue(bean.getArea());
sheet.getRow(2).getCell(6).setCellValue(bean.getStartTime() + "-" + bean.getEndTime());
CellStyle style = getStyle(wb);
int row = 4;
for (WGLineBean model : beans) {
HSSFRow rows = sheet.createRow(row);
int col = 0;
int quantity;
try {
quantity = model.getTotalQuantity();
} catch (Exception e) {
e.printStackTrace();
quantity = 0;
}
for (int i = 0; i < 15; i++) {
HSSFCell cell = rows.createCell(i);
cell.setCellStyle(style);
}
sheet.getRow(row).getCell(col++).setCellValue(model.getRecordTime());
sheet.getRow(row).getCell(col++).setCellValue(quantity - model.getSum_detection1());
sheet.getRow(row).getCell(col++).setCellValue(model.getSum_detection1());

sheet.getRow(row).getCell(col++).setCellValue(quantity - model.getSum_detection2());
sheet.getRow(row).getCell(col++).setCellValue(model.getSum_detection2());

sheet.getRow(row).getCell(col++).setCellValue(quantity - model.getSum_detection3());
sheet.getRow(row).getCell(col++).setCellValue(model.getSum_detection3());

sheet.getRow(row).getCell(col++).setCellValue(quantity - model.getSum_detection4());
sheet.getRow(row).getCell(col++).setCellValue(model.getSum_detection4());

sheet.getRow(row).getCell(col++).setCellValue(quantity - model.getSum_detection5());
sheet.getRow(row).getCell(col++).setCellValue(model.getSum_detection5());

sheet.getRow(row).getCell(col++).setCellValue(quantity - model.getSum_detection6());
sheet.getRow(row).getCell(col++).setCellValue(model.getSum_detection6());

sheet.getRow(row).getCell(col++).setCellValue(quantity - model.getSum_detection7());
sheet.getRow(row).getCell(col++).setCellValue(model.getSum_detection7());
row++;
}
} catch (Exception e) {
e.printStackTrace();
return wb;
}
return wb;
}

 

正文到此结束
Loading...