首页 > 代码库 > Poi操作Excel
Poi操作Excel
/** 将所有数据导出成Excel,让客户知道哪些记录是异常的(异常的变红色) */
public void exportExcel(ArrayList<ToolImport> prisonerImportList, String dir, String filename) {
dir = dir + "/" + filename + ".xls";// 上传至服务器
File file = new File(dir);
if (file.exists()) {
file.delete();
file = new File(dir);
}
OutputStream output = null;
try {
output = new FileOutputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
// 1.工作表
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Sheet-1");
wb.setSheetName(0, "工具导入-1", HSSFCell.ENCODING_UTF_16);
// HSSFCellStyle setBorder = wb.createCellStyle();
String[] colArray = { "序号", "分监区", "工具编号", "工具名称", "分监区编号",
"固定/流动", "安装位置", "备注", "异常信息" };
// 以下以写表头,表头为第一行
HSSFCellStyle titleStyle = wb.createCellStyle();
titleStyle.setFillForegroundColor(HSSFColor.AQUA.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFRow row0 = sheet.createRow((short) 0);
HSSFCell cell0 = row0.createCell((short) 0);
row0.setHeight((short)800); //设置行高
Region region1 = new Region(0, (short) 0, 0, (short) 8);//合并单元格
sheet.addMergedRegion(region1);
//setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
//setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell0.setEncoding((short) 1);
cell0.setCellStyle(titleStyle);
cell0.setCellValue("工具清点管理系统——工具导入");
sheet.setColumnWidth((short)0, (short)2000); // 设置列宽
sheet.setColumnWidth((short)1, (short)4500);
sheet.setColumnWidth((short)2, (short)4500);
sheet.setColumnWidth((short)3, (short)4500);
sheet.setColumnWidth((short)4, (short)4500);
sheet.setColumnWidth((short)5, (short)4500);
sheet.setColumnWidth((short)6, (short)4500);
sheet.setColumnWidth((short)7, (short)4500);
sheet.setColumnWidth((short)8, (short)12000);
// setBorder.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中
// setBorder.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 居中
//sheet.addMergedRegion(new Region(0,(short)1,1,(short)2));//
HSSFRow row = sheet.createRow((short) 1);
for (int i = 0; i < colArray.length; i++) {// 填写列标题
HSSFCell cell = row.createCell((short) i);
cell.setEncoding((short) 1);
// cell.setCellStyle(titleStyle);
cell.setCellValue(colArray[i]);
}
// 从Excel的第二行开始填充数据
// 定义红色字体单元格
HSSFFont font = wb.createFont();
font.setColor(HSSFFont.COLOR_RED);
HSSFCellStyle cellStyles = wb.createCellStyle();
cellStyles.setFont(font);
for (int i = 0; i < prisonerImportList.size(); i++) {
// 定义数据从第二行开始
ToolImport vo = prisonerImportList.get(i);
row = sheet.createRow((short) i + 2);
for (int j = 0; j < colArray.length; j++) {
HSSFCell cell = row.createCell((short) j);
cell.setEncoding((short) 1);
if (StringUtils.isNotBlank(vo.getValidateStr())) { // 填充单元格为红色字体
cell.setCellStyle(cellStyles);
}
switch (j) {
case 0:
cell.setCellValue(i + 1);
break;
case 1:
cell.setCellValue(vo.getAreaname());
break;
case 2:
cell.setCellValue(vo.getInnerNo());
break;
case 3:
cell.setCellValue(vo.getToolName());
break;
case 4:
cell.setCellValue(vo.getAreaNo());
break;
case 5:
cell.setCellValue(vo.getToolType());
break;
case 6:
cell.setCellValue(vo.getIsWhere());
break;
case 7:
cell.setCellValue(vo.getRemark());
break;
case 8:
cell.setCellValue(vo.getValidateStr());
break;
}
}
}
try {
wb.write(output);
} catch (IOException e) {
e.printStackTrace();
}
try {
output.flush();
output.close();
} catch (IOException e) {
e.printStackTrace();
}
}