Java导入导出Excel工具类ExcelUtil

导出就是将List转化为Excel（listToExcel）

导入就是将Excel转化为List（excelToList）

导入导出中会出现各种各样的问题，比如：数据源为空、有重复行等，我自定义了一个ExcelException异常类，用来处理这些问题。

 

异常类

 

导出工具类：

　

public Map<String,Object>  exportMessageExcelFile(String title, String[] headers,List dossierList, OutputStream out, String pattern)
       {
           boolean flag = true;
           Map<String,Object> map = new HashMap<String,Object>();
           StringBuffer messageFile = new StringBuffer(); 
           // 声明一个工作薄
              HSSFWorkbook workbook = new HSSFWorkbook();
              // 生成一个表格
              HSSFSheet sheet = workbook.createSheet(title);
              // 设置表格默认列宽度为15个字节
              sheet.setDefaultColumnWidth((short) 15);
              // 生成一个样式
              HSSFCellStyle style = workbook.createCellStyle();
              style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
              style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
              style.setBorderRight(HSSFCellStyle.BORDER_THIN);
              style.setBorderTop(HSSFCellStyle.BORDER_THIN);
              style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
              //产生表格标题行
              HSSFRow row = sheet.createRow(0);
              for (short i = 0; i < headers.length; i++) {
                 HSSFCell cell = row.createCell(i);
                 cell.setCellStyle(style);
                 HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                 cell.setCellValue(text);
              }
              for(int i = 0; i< dossierList.size();i++)
              {
                  Object[] obj =  (Object[]) dossierList.get(i);
                  row = sheet.createRow(i+1);
                  for (int j = 0; j < obj.length; j++) {
                      HSSFCell cell = row.createCell(j);
                      cell.setCellStyle(style);
                      if(j==0)
                      {
                          cell.setCellValue(i+1);//序号
                      }
                      if(j==1)
                      {
                          //Logger.debug("obj[5]"+obj[5]);
                          cell.setCellValue(obj[5]==null?"":obj[5].toString());//办理人
                      }
                      if(j==2)
                      {
                          //Logger.debug("obj[3]"+obj[3]);
                          cell.setCellValue(obj[3]==null?"":obj[3].toString());//办理时间
                      }
                      if(j==3)
                      {
                         // Logger.debug("obj[2]"+obj[2]);
                          cell.setCellValue(obj[2]==null?obj[6]==null?"":obj[6].toString():obj[2].toString());//办理意见
                          if(null!=obj[6]&&!"".equals(obj[6]))
                          {
                              messageFile.append(obj[6].toString()+",");
                          }
                          break;
                      }
                  }
              }
              map.put("messageFile", messageFile.toString().endsWith(",")?messageFile.toString().substring(0, messageFile.toString().length()-1):messageFile.toString());
              try {
                workbook.write(out);
            } catch (IOException e) {
                e.printStackTrace();
                flag = false;
            }finally {
                //清理资源
                try {
                    if(out != null)
                    {
                        out.close();
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
              map.put("flag", flag);
              return map;
       }

 

 测试类：

 public static void main(String[] args) {
           ExportExcel<Object> ex = new ExportExcel<Object>();
           String[] headers = { "学号"};
           List<Object[]>  dataset = new ArrayList<Object[]>();
           dataset.add(new Object[]{"1"});
           dataset.add(new Object[]{"2"});
           dataset.add(new Object[]{"3"});
           dataset.add(new Object[]{"4"});
           dataset.add(new Object[]{"5"});
           try {
            OutputStream out = new FileOutputStream("C://Users//Lenovo//Desktop/aa.xls");
             ex.exportMessageExcelFile("测试POI导出EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
       }