一、POI

1.引入依賴

<!-- 03 xls-->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>3.9</version>
</dependency><!-- 07 xlsx -->
<dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>3.9</version>
</dependency><!-- 日期格式化 -->
<dependency><groupId>joda-time</groupId><artifactId>joda-time</artifactId><version>2.10.4</version>
</dependency>

2.讀取Excel

package com.example;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.XSSFWorkbook;
import org.joda.time.DateTime;import java.io.*;public class Test {public static void main(String[] args) throws Exception {//獲取文件的數據流InputStream inputStream = new FileInputStream("D:\\java\\測試.xls");//創建文件對象Workbook workbook = new HSSFWorkbook(inputStream);//獲取SheetSheet sheet = workbook.getSheetAt(0);//獲取行Row row = sheet.getRow(0);//獲取列Cell cell = row.getCell(0);String stringCellValue = cell.getStringCellValue();System.out.println(stringCellValue);inputStream.close();}
}

3.寫入Excel

package com.example;import org.apache.poi.hssf.usermodel.HSSFCell;
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 java.io.*;public class Test {public static void main(String[] args) throws Exception {//獲取文件的數據流InputStream inputStream = new FileInputStream("D:\\java\\demo.xls");//創建文件對象Workbook workbook = new HSSFWorkbook(inputStream);//獲取SheetSheet sheet = workbook.getSheetAt(0);int rows = sheet.getPhysicalNumberOfRows();for (int i = 0; i < rows; i++) {Row row = sheet.getRow(i);int cells = row.getPhysicalNumberOfCells();for (int j = 0; j < cells; j++) {Cell cell = row.getCell(j);int type = cell.getCellType();String value = "";switch (type){case HSSFCell.CELL_TYPE_STRING:value = cell.getStringCellValue();break;case HSSFCell.CELL_TYPE_NUMERIC:cell.setCellType(HSSFCell.CELL_TYPE_STRING);value = cell.getStringCellValue();break;}System.out.print(value+" ");}System.out.println();}inputStream.close();}
}

二、EasyExcel

EasyExcel 會將 Excel 數據和 Java 對象之間綁定起來

1.引入依賴

<!-- EasyExcel -->
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.6</version>
</dependency>

2.創建跟 Excel 數據格式對應的 Java 類

package com.example;import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;import java.util.Date;@Data
public class ExcelVO {@ExcelProperty("ID")private Integer id;@ExcelProperty("公司名稱")private String name;@ExcelProperty("停車場")private String park;@ExcelProperty("車牌號")private String number;@ExcelProperty("支付類別")private String type;@ExcelProperty("支付金額(元)")private Integer money;@ExcelProperty("支付時間")private Date time;
}

3.讀取數據

package com.example;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.apache.poi.hssf.usermodel.HSSFCell;
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 java.io.*;public class Test {public static void main(String[] args) throws Exception {InputStream inputStream = new FileInputStream("D:\\java\\demo.xls");EasyExcel.read(inputStream).head(ExcelVO.class).sheet().registerReadListener(new AnalysisEventListener<ExcelVO>() {@Overridepublic void invoke(ExcelVO o, AnalysisContext analysisContext) {System.out.println(o);}@Overridepublic void doAfterAllAnalysed(AnalysisContext analysisContext) {System.out.println("文件解析完成");}}).doRead();}
}

4.寫數據

package com.southwind.handler;import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class CustomCellWriteHandler extends AbstractColumnWidthStyleStrategy {private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();@Overrideprotected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);if (needSetWidth) {Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());if (maxColumnWidthMap == null) {maxColumnWidthMap = new HashMap<>();CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);}Integer columnWidth = this.dataLength(cellDataList, cell, isHead);if (columnWidth >= 0) {if (columnWidth > 255) {columnWidth = 255;}Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());if (maxColumnWidth == null || columnWidth > maxColumnWidth) {maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);}}}}private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {if (isHead) {return cell.getStringCellValue().getBytes().length;} else {CellData cellData = cellDataList.get(0);CellDataTypeEnum type = cellData.getType();if (type == null) {return -1;} else {switch (type) {case STRING:return cellData.getStringValue().getBytes().length;case BOOLEAN:return cellData.getBooleanValue().toString().getBytes().length;case NUMBER:return cellData.getNumberValue().toString().getBytes().length;default:return -1;}}}}
}

package com.example;import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.southwind.handler.CustomCellWriteHandler;
import org.apache.poi.hssf.usermodel.HSSFCell;
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 java.io.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;public class Test {public static void main(String[] args) throws Exception {List<ExcelVO> list = new ArrayList<>();for (int i = 0; i < 5; i++) {ExcelVO excelVO = new ExcelVO();excelVO.setId(i+1);excelVO.setType("臨時車");excelVO.setTime(new Date());excelVO.setPark("軟件園停車場");excelVO.setMoney(100);excelVO.setNumber("電A123456");excelVO.setName("Java科技有限公司");list.add(excelVO);}OutputStream outputStream = new FileOutputStream("D:\\java\\demo2.xls");EasyExcel.write(outputStream,ExcelVO.class).registerWriteHandler(new CustomCellWriteHandler()).sheet("停車繳費記錄").doWrite(list);}
}