首页 > 代码库 > 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();
  }
 }