java-Excel
来源：互联网 发布：阿里云公网ip访问不了 编辑：程序博客网 时间：2024/06/07 17:09
public class ExcelUtils { //默认单元格内容为数字时格式 private static DecimalFormat df = new DecimalFormat("0"); // 默认单元格格式化日期字符串 private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); // 格式化数字 private static DecimalFormat nf = new DecimalFormat("0.00"); public static ArrayList<ArrayList<Object>> readExcel(File file) { if (file == null) { return null; } if (file.getName().endsWith("xlsx")) { //处理ecxel2007 return readExcel2007(file); } else { //处理ecxel2003 return readExcel2003(file); } } /* * @return 将返回结果存储在ArrayList内，存储结构与二位数组类似 * lists.get(0).get(0)表示过去Excel中0行0列单元格 */ public static ArrayList<ArrayList<Object>> readExcel2003(File file) { try { ArrayList<ArrayList<Object>> sheetContent = new ArrayList<ArrayList<Object>>(); ArrayList<Object> colList; HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(file)); HSSFSheet sheet = wb.getSheetAt(0); HSSFRow row; HSSFCell cell; Object value; //读取Excel表单 for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); colList = new ArrayList<Object>(); if (row == null) { //当读取行为空时 //若不是最后一行，则把空行加入到结果集中 sheetContent.add(colList); } else { //若读取的行不为空..... for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { //当该单元格为空 colList.add(""); } else { //当单元格不为空 switch (cell.getCellType()) { //字符串 case XSSFCell.CELL_TYPE_STRING: System.out.println(i + "行" + j + " 列 is String type"); value = cell.getStringCellValue(); break; //数字 case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle() .getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell .getNumericCellValue())); } System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString()); break; //布尔 case XSSFCell.CELL_TYPE_BOOLEAN: System.out.println(i + "行" + j + " 列 is Boolean type"); value = Boolean.valueOf(cell.getBooleanCellValue()); break; //空 case XSSFCell.CELL_TYPE_BLANK: System.out.println(i + "行" + j + " 列 is Blank type"); value = ""; break; //默认 default: System.out.println(i + "行" + j + " 列 is default type"); value = cell.toString(); }// end switch colList.add(value); } }//end for j } sheetContent.add(colList); }//end for i return sheetContent; } catch (Exception e) { return null; } } public static ArrayList<ArrayList<Object>> readExcel2007(File file) { try { ArrayList<ArrayList<Object>> sheetContent = new ArrayList<ArrayList<Object>>(); ArrayList<Object> colList; XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file)); XSSFSheet sheet = wb.getSheetAt(0); XSSFRow row; XSSFCell cell; Object value; for (int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum(); i++) { row = sheet.getRow(i); colList = new ArrayList<Object>(); if (row == null) { //当读取行为空时 //若不是最后一行，则把空行加入到结果集中 sheetContent.add(colList); } else { //若读取的行不为空..... for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) { //当该单元格为空 colList.add(""); } else { //当单元格不为空 switch (cell.getCellType()) { //字符串 case XSSFCell.CELL_TYPE_STRING: System.out.println(i + "行" + j + " 列 is String type"); value = cell.getStringCellValue(); break; //数字 case XSSFCell.CELL_TYPE_NUMERIC: if ("@".equals(cell.getCellStyle().getDataFormatString())) { value = df.format(cell.getNumericCellValue()); } else if ("General".equals(cell.getCellStyle() .getDataFormatString())) { value = nf.format(cell.getNumericCellValue()); } else { value = sdf.format(HSSFDateUtil.getJavaDate(cell .getNumericCellValue())); } System.out.println(i + "行" + j + " 列 is Number type ; DateFormt:" + value.toString()); break; //布尔 case XSSFCell.CELL_TYPE_BOOLEAN: System.out.println(i + "行" + j + " 列 is Boolean type"); value = Boolean.valueOf(cell.getBooleanCellValue()); break; //空 case XSSFCell.CELL_TYPE_BLANK: System.out.println(i + "行" + j + " 列 is Blank type"); value = ""; break; //默认 default: System.out.println(i + "行" + j + " 列 is default type"); value = cell.toString(); }// end switch colList.add(value); } }//end for j } sheetContent.add(colList); }//end for i return sheetContent; } catch (Exception e) { return null; } } public static void writeExcel(ArrayList<ArrayList<Object>> result, String path) { if (result == null) { return; } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("sheet1"); for (int i = 0; i < result.size(); i++) { HSSFRow row = sheet.createRow(i); if (result.get(i) != null) { for (int j = 0; j < result.get(i).size(); j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(result.get(i).get(j).toString()); } } } ByteArrayOutputStream os = new ByteArrayOutputStream(); try { wb.write(os); } catch (IOException e) { e.printStackTrace(); } byte[] content = os.toByteArray(); File file = new File(path);//Excel文件生成后存储的位置。 OutputStream fos = null; try { fos = new FileOutputStream(file); fos.write(content); os.close(); fos.close(); } catch (Exception e) { e.printStackTrace(); } } public static DecimalFormat getDf() { return df; } public static void setDf(DecimalFormat df) { ExcelUtils.df = df; } public static SimpleDateFormat getSdf() { return sdf; } public static void setSdf(SimpleDateFormat sdf) { ExcelUtils.sdf = sdf; } public static DecimalFormat getNf() { return nf; } public static void setNf(DecimalFormat nf) { ExcelUtils.nf = nf; }}<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.16-beta2</version> </dependency>阅读全文
