Java POI读取/导出Excel数据

最近在做项目中用到了，Java POI来操作Excel数据，可以将Excel数据导入导出到系统之中。在此分享两个方法，希望对大家有所帮助

一、导出Excel

public void exportExcelOutputStream(OutputStream os,List<BusinessIncome> listData, String fileName,String accountingTimeName, String companyName) {try {// 创建Excel文档HSSFWorkbook workbook = new HSSFWorkbook();// 创建sheetHSSFSheet sheet = workbook.createSheet("营业收入");// 创建标题HSSFCellStyle style = workbook.createCellStyle();HSSFCellStyle style1 = workbook.createCellStyle();HSSFCellStyle style2 = workbook.createCellStyle();        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //创建一个居中格式        style.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);        style.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);        style.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);        style.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);                style1.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //创建一个居右格式        style1.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);        style1.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);        style1.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);        style1.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);                style2.setAlignment(HSSFCellStyle.ALIGN_LEFT); //创建一个居中格式        style2.setBorderTop(HSSFCellStyle.SOLID_FOREGROUND);        style2.setBorderLeft(HSSFCellStyle.SOLID_FOREGROUND);        style2.setBorderRight(HSSFCellStyle.SOLID_FOREGROUND);        style2.setBorderBottom(HSSFCellStyle.SOLID_FOREGROUND);        // 设置单元格字体         HSSFFont font = workbook.createFont();         font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);         font.setFontName("宋体");         font.setFontHeight((short) 200);         style.setFont(font);                 // 添加查询条件 sheet.addMergedRegion(new Region(0,(short)0,0,(short)2)); sheet.addMergedRegion(new Region(0,(short)3,0,(short)5)); HSSFRow row0 = sheet.createRow(0); HSSFCell cell0 = row0.createCell(0); cell0.setCellValue(new HSSFRichTextString("编制公司："+companyName)); cell0 = row0.createCell(3); cell0.setCellValue(new HSSFRichTextString("会计期间："+accountingTimeName));        // 创建列表头HSSFRow row1 = sheet.createRow(1);row1.setHeight((short) 300); HSSFCell cell = row1.createCell(0);cell.setCellValue(new HSSFRichTextString("业务类型"));cell.setCellStyle(style);cell = row1.createCell(1);cell.setCellValue(new HSSFRichTextString("营业收入(元)"));cell.setCellStyle(style);cell = row1.createCell(2);cell.setCellValue(new HSSFRichTextString("营业收入占比(%)"));cell.setCellStyle(style);cell = row1.createCell(3);cell.setCellValue(new HSSFRichTextString("营业成本(元)"));cell.setCellStyle(style);cell = row1.createCell(4);cell.setCellValue(new HSSFRichTextString("毛利(元)"));cell.setCellStyle(style);cell = row1.createCell(5);cell.setCellValue(new HSSFRichTextString("毛利率(%)"));cell.setCellStyle(style);//循环表数据int a = 2;for (int i = 0; i < listData.size(); i++) {BusinessIncome stock = (BusinessIncome) listData.get(i);HSSFRow row = sheet.createRow(a);row.setHeight((short) 300); cell = row.createCell(0);cell.setCellValue(stock.getProduct());cell.setCellStyle(style2);cell = row.createCell(1);cell.setCellValue(Double.valueOf(stock.getBusinessIncome()+""));cell.setCellStyle(style1);cell = row.createCell(2);cell.setCellValue(Double.valueOf(stock.getPercent()+""));cell.setCellStyle(style1);cell = row.createCell(3);cell.setCellValue(Double.valueOf(stock.getCosts()+""));cell.setCellStyle(style1);cell = row.createCell(4);cell.setCellValue(Double.valueOf(stock.getGrossMargin()+""));cell.setCellStyle(style1);cell = row.createCell(5);cell.setCellValue(Double.valueOf(stock.getGrossMarginRate()+""));cell.setCellStyle(style1);a++;}sheet.setColumnWidth((short)0,4000);sheet.setColumnWidth((short)1,4000);sheet.setColumnWidth((short)2,4000);sheet.setColumnWidth((short)3,4000);sheet.setColumnWidth((short)4,4000);sheet.setColumnWidth((short)5,4000);workbook.write(os);os.flush();} catch (IOException e) {e.printStackTrace();}}

二、导入Excel方法

public String execute() throws Exception {       String jsonStr = "";      try { InputStream is = new FileInputStream(upload[0]); HSSFWorkbook workbook = new HSSFWorkbook(is); HSSFSheet sheet = workbook.getSheetAt(0); // 获取到Excel文件中的所有行数 int rows = sheet.getPhysicalNumberOfRows(); for (int i = 0; i < rows; i++) { // 读取左上端单元格 HSSFRow row = sheet.getRow(i + 1); if (null != row) { String value = ""; // 获取到Excel文件中的所有的列 int cells = sheet.getRow(1).getPhysicalNumberOfCells(); // 遍历所有列 for (int j = 0; j < cells; j++) { // 获取到列的值 HSSFCell cell = row.getCell(j);  if (cell != null) { switch (cell.getCellType()) { case XSSFCell.CELL_TYPE_BLANK: // 空值 value += "0,"; break; case XSSFCell.CELL_TYPE_FORMULA: // 公式 break; case XSSFCell.CELL_TYPE_NUMERIC: // 数字  //先看是否是日期格式                        if(HSSFDateUtil.isCellDateFormatted(cell)){                            //读取日期格式                         // 如果是Date类型则，取得该Cell的Date值                          HSSFCellStyle cellStyle = workbook.createCellStyle();                       cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy-MM-dd"));                       cell.setCellStyle(cellStyle);                        // 把Date转换成本地格式的字符串                           SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");                        String d = sf.format(cell.getDateCellValue());                        value += d+",";                       }else{     value += cell.getNumericCellValue() + ",";                     }    break; case XSSFCell.CELL_TYPE_STRING: // 字符串 value += cell.getStringCellValue() + ","; break; case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean value += cell.getBooleanCellValue() + ","; break; case XSSFCell.CELL_TYPE_ERROR: // 故障 break; default: value += "0"; break; } }else { value += "0,"; } }                //将数据插入到数据库中                 if(i>=1 && !"".equals(value)) {                      String[] val = value.split(",");                 OtherCurrentAsset existingStock=new OtherCurrentAsset();                existingStock.setAccountime(accountingTimeId);                existingStock.setName(val[0]);                if(val.length>1&&val[1]!=null&&!"".equals(val[1])&&!"null".equals(val[1])){                existingStock.setEndTime(val[1]);                }                if(val.length>2&&val[2]!=null&&!"".equals(val[2])&&!"null".equals(val[2])){                existingStock.setAccount(new BigDecimal(val[2]));                }                if(val.length>3&&val[3]!=null&&!"".equals(val[3])&&!"null".equals(val[3])){                existingStock.setPercent(new BigDecimal(val[3]));                }                 existingStock.setValid(true);                otherCurrentAssetFacade.editOtherCurrentAsset(existingStock);                }          }      }     jsonStr= "{success:true}";      } catch (FileNotFoundException e) {         e.printStackTrace();         jsonStr= "{success:false}";    } catch (IOException e) {         e.printStackTrace();         jsonStr= "{success:false}";    }   renderJson(jsonStr);        return NONE;            }