ApachePOI如何实现Excel文件的读写操作？

本文共计1261个文字，预计阅读时间需要6分钟。

javapackage com.sefon.construct.utils.excel;

import java.util.List;

public class DataSet { private List sheetList;

public List getSheetList() { return sheetList; }

public void setSheetList(List sheetList) { this.sheetList=sheetList; }}

DataSet.java

package com.sefon.contruct.utils.excel; import java.util.List; public class DataSet { private List sheetList; public List getSheetList() { return sheetList; } public void setSheetList(List sheetList) { this.sheetList = sheetList; } } ExcelOperClass.java

package com.sefon.contruct.utils.excel; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.util.ArrayList; import java.util.List; import org.apache.commons.collections.CollectionUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class ExcelOperClass { private static String EXCEL_2003 = ".xls"; private static String EXCEL_2007 = ".xlsx"; /** * 通过POI方式读取Excel * * @param excelFile */ public static DataSet readExcelPOI(String filePath, Integer cons) throws Exception { File excelFile = new File(filePath); if (excelFile != null) { String fileName = excelFile.getName(); fileName = fileName.toLowerCase(); if (fileName.toLowerCase().endsWith(EXCEL_2003)) { DataSet dataSet = readExcelPOI2003(excelFile, cons); return dataSet; } if (fileName.toLowerCase().endsWith(EXCEL_2007)) { DataSet dataSet = readExcelPOI2007(excelFile, cons); return dataSet; } } return null; } /** * 将数据写入到Excel文件中 * * @param filePath * @param cons * @param dataSet * @throws Exception */ public static void writeExcelPOI(String filePath, DataSet dataSet) throws Exception { if (filePath != null) { File excelFile = new File(filePath); String fileName = excelFile.getName(); Workbook workbook = null; if (fileName.toLowerCase().endsWith(EXCEL_2003)) { workbook = new HSSFWorkbook(); } if (fileName.toLowerCase().endsWith(EXCEL_2007)) { workbook = new XSSFWorkbook(); } // 支持多sheet数据写入 List sheets = dataSet.getSheetList(); if (CollectionUtils.isNotEmpty(sheets)) { for (ExcelSheet excelSheet : sheets) { String sheetName = excelSheet.getSheetName(); String[] headers = excelSheet.getHeaders(); List dataList = excelSheet.getDatasList(); // 创建sheet对象 Sheet sheet1 = (Sheet) workbook.createSheet(sheetName); dataList.add(0, headers); // 循环数据将写入Excel中 int listSize = dataList.size(); for (int i = 0; i < listSize; i++) { Row row = sheet1.createRow(i); String[] columns = dataList.get(i); for (int j = 0; j < columns.length; j++) { String value = columns[j]; System.out.println(value); Cell cell = row.createCell(j); cell.setCellValue(columns[j]); } } } } // 写入到文件输出流中 OutputStream outputStream = new FileOutputStream(excelFile); workbook.write(outputStream); outputStream.flush(); outputStream.close(); } } /** * 读取Excel2003的表单 * @param excelFile * @return * @throws Exception */ private static DataSet readExcelPOI2003(File excelFile, Integer rCons) throws Exception { List sheetList = new ArrayList<>(); InputStream input = new FileInputStream(excelFile); HSSFWorkbook workBook = new HSSFWorkbook(input); // 获取Excel的sheet数量 Integer sheetNum = workBook.getNumberOfSheets(); // 循环Sheet表单 for (int i = 0; i < sheetNum; i++) { ExcelSheet excelSheet = new ExcelSheet(); List datasList = new ArrayList (); String[] headers = null; HSSFSheet sheet = workBook.getSheetAt(i); if (sheet == null) { continue; } String sheetName = sheet.getSheetName(); // 获取Sheet里面的Row数量 Integer rowNum = sheet.getLastRowNum() + 1; for (int j = 0; j < rowNum; j++) { HSSFRow row = sheet.getRow(j); if (row == null) { continue; } if (j > rCons) { //System.out.println("==========="); String[] datas = readRowCellValue2003(row); datasList.add(datas); }else{ if (j == 0) {// 默认第一行为表头 headers = readRowCellValue2003(row); } } } excelSheet.setSheetName(sheetName); excelSheet.setHeaders(headers); excelSheet.setDatasList(datasList); sheetList.add(excelSheet); } DataSet dataSet = new DataSet(); dataSet.setSheetList(sheetList); return dataSet; } /** * 读取Excel2007的表单 * * @param excelFile * @return * @throws Exception */ private static DataSet readExcelPOI2007(File excelFile, Integer rCons) throws Exception { List sheetList = new ArrayList<>(); InputStream input = new FileInputStream(excelFile); XSSFWorkbook workBook = new XSSFWorkbook(input); // 获取Sheet数量 Integer sheetNum = workBook.getNumberOfSheets(); for (int i = 0; i < sheetNum; i++) { ExcelSheet excelSheet = new ExcelSheet(); List datasList = new ArrayList (); String[] headers = null; XSSFSheet sheet = workBook.getSheetAt(i); if (sheet == null) { continue; } String sheetName = sheet.getSheetName(); // 获取行值 Integer rowNum = sheet.getLastRowNum() + 1; for (int j = 0; j < rowNum; j++) { XSSFRow row = sheet.getRow(j); if (row == null) { continue; } if (j > rCons) { // System.out.println("============="); String[] datas = readRowCellValue2007(row); datasList.add(datas); } else { if (j == 0) {// 默认第一行为表头 headers = readRowCellValue2007(row); } } } excelSheet.setSheetName(sheetName); excelSheet.setHeaders(headers); excelSheet.setDatasList(datasList); sheetList.add(excelSheet); } DataSet dataSet = new DataSet(); dataSet.setSheetList(sheetList); return dataSet; } private static String[] readRowCellValue2003(HSSFRow row){ Integer cellNum = (int) row.getLastCellNum(); String[] datas = new String[cellNum]; for (int k = 0; k < cellNum; k++) { HSSFCell cell = row.getCell(k); if (cell == null) { continue; } if (cell != null) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); String cellValue = ""; int cellValueType = cell.getCellType(); if (cellValueType == cell.CELL_TYPE_STRING) { cellValue = cell.getStringCellValue(); } if (cellValueType == cell.CELL_TYPE_NUMERIC) { Double number = cell.getNumericCellValue(); System.out.println("字符串+++==========" + number.intValue()); cellValue = cell.getNumericCellValue() + ""; } datas[k] = cellValue; } } return datas; } private static String[] readRowCellValue2007(XSSFRow row){ Integer cellNum = (int) row.getLastCellNum(); String[] datas = new String[cellNum]; for (int k = 0; k < cellNum; k++) { XSSFCell cell = row.getCell(k); if (cell == null) { continue; } if (cell != null) { cell.setCellType(XSSFCell.CELL_TYPE_STRING); String cellValue = ""; int cellValueType = cell.getCellType(); if (cellValueType == cell.CELL_TYPE_STRING) { cellValue = cell.getStringCellValue(); } if (cellValueType == cell.CELL_TYPE_NUMERIC) { Double number = cell.getNumericCellValue(); System.out.println("字符串+++==========" + number.toString()); cellValue = cell.getNumericCellValue() + ""; } datas[k] = cellValue; } } return datas; } /** * 读取样例2003 */ private static void readSample2003(){ try { DataSet dataSet = readExcelPOI("D:\\工作簿1.xls", 0); System.out.println("================================"); List sheetList = dataSet.getSheetList(); for (ExcelSheet excelSheet : sheetList) { System.out.println(excelSheet.getSheetName()); List dataList = excelSheet.getDatasList(); String[] headers = excelSheet.getHeaders(); for (String string : headers) { System.out.println(string); } System.out.println("================================"); for (String[] data : dataList) { for (int i = 0; i < data.length; i++) { System.out.println(data[i]); } } } } catch (Exception e) { e.printStackTrace(); } } /** * 读取样例2007 */ private static void readSample2007(){ try { DataSet dataSet = readExcelPOI("D:\\工作簿1.xlsx", 0); System.out.println("================================"); List sheetList = dataSet.getSheetList(); for (ExcelSheet excelSheet : sheetList) { System.out.println(excelSheet.getSheetName()); List dataList = excelSheet.getDatasList(); String[] headers = excelSheet.getHeaders(); for (String string : headers) { System.out.println(string); } System.out.println("================================"); for (String[] data : dataList) { for (int i = 0; i < data.length; i++) { System.out.println(data[i]); } } } } catch (Exception e) { e.printStackTrace(); } } /** * 写入样例 * @throws Exception */ private static void writeSample() throws Exception{ List sheetList = new ArrayList<>(); for (int i = 0; i < 3; i++) { ExcelSheet excelSheet = new ExcelSheet(); excelSheet.setSheetName("Sheet样例"+(i+1)); excelSheet.setHeaders(new String[]{"列表1","列表2"}); List datasList = new ArrayList<>(); for (int j = 0; j < 20; j++) { String[] data = new String[]{"数据Cell0"+(j+1),"数据Cell1"+(j+1)}; datasList.add(data); } excelSheet.setDatasList(datasList); sheetList.add(excelSheet); } DataSet dataSet = new DataSet(); dataSet.setSheetList(sheetList); writeExcelPOI("D:\\sampel1.xlsx", dataSet); } public static void main(String[] args) throws Exception { writeSample(); } } ExcelSheet.java

package com.sefon.contruct.utils.excel; import java.util.List; public class ExcelSheet { private String sheetName; private String[] headers; private List datasList; public String getSheetName() { return sheetName; } public void setSheetName(String sheetName) { this.sheetName = sheetName; } public String[] getHeaders() { return headers; } public void setHeaders(String[] headers) { this.headers = headers; } public List getDatasList() { return datasList; } public void setDatasList(List datasList) { this.datasList = datasList; } }