很久以前，微軟的電子表格軟體Excel操作簡單、存儲數據直觀方便，還支持列印報表，極大的提升了工作的效率，深得白領青睞，不久之後，便成了辦公室裡的必備工具。
隨著更多的新語言的崛起，例如我們所熟悉的Java，後來便有一些團隊開始開發一套能與Excel軟體無縫切換的操作工具！
當然，在Java生態體系裡面，能與Excel無縫銜接的第三方工具還有很多，在開始也給大家列出來三個，因為apache poi在業界使用的最廣泛，因此其他的工具不做過多介紹！
1.1 首先添加依賴
<dependencies> <!--xls(03)--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <!--xlsx(07)--> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <!--時間格式化工具--> <dependency> <groupId>joda-time</groupId> <artifactId>joda-time</artifactId> <version>2.10.6</version> </dependency></dependencies>1.2 導出excel
導出操作，即使用Java寫出數據到Excel中，常見場景是將頁面上的數據導出，這些數據可能是財務數據，也可能是商品數據，生成Excel後返回給用戶下載文件。
poi工具庫中，導出api可以分三種方式:
HSSF方式： 這種方式導出的文件格式為office 2003專用格式，即.xls，優點是導出數據速度快，但是最多65536行數據
XSSF方式： 這種方式導出的文件格式為office 2007專用格式，即.xlsx，優點是導出的數據不受行數限制，缺點導出速度慢
SXSSF方式： SXSSF是XSSF API的兼容流式擴展，主要解決當使用XSSF方式導出大數據量時，內存溢出的問題，支持導出大批量的excel數據
1.2.1 HSSF方式導出(.xls方式)
HSSF方式，最多只支持65536條數據導出，超過這個條數會報錯！
package cn.tedu.excel.test;
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.FileOutputStream;
public class ExcelWrite2003Test { private static String PATH = "/Users/lixin/Desktop/";
public static void main(String[] args) throws Exception { long begin = System.currentTimeMillis();
Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNumber = 0; rowNumber < 65536; rowNumber++) { Row row = sheet.createRow(rowNumber); for (int cellNumber = 0; cellNumber < 10; cellNumber++) { Cell cell = row.createCell(cellNumber); cell.setCellValue(cellNumber); } } System.out.println("結束！"); FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用戶信息表-XLS.xls"); workbook.write(fileOutputStream); fileOutputStream.close(); long end = System.currentTimeMillis(); System.out.println("時間為："+(double) (end - begin) / 1000); }}1.2.2 XSSF方式導出(.xlsx)
XSSF方式支持大批量數據導出，所有的數據先寫入內存再導出，容易出現內存溢出！
package cn.tedu.excel.test;
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 java.io.FileOutputStream;
public class ExcelWrite2007Test { public static String PATH = "/Users/lixin/Desktop/";
public static void main(String[] args) throws Exception { long begin = System.currentTimeMillis();
Workbook workbook = new XSSFWorkbook(); Sheet sheet = workbook.createSheet(); for (int rowNumber = 0; rowNumber < 65537; rowNumber++) { Row row = sheet.createRow(rowNumber); for (int cellNumber = 0; cellNumber < 10; cellNumber++) { Cell cell = row.createCell(cellNumber); cell.setCellValue(cellNumber); } } System.out.println("結束");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用戶信息表-XLSX.xlsx"); workbook.write(fileOutputStream); fileOutputStream.close(); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000); }}1.2.3 SXSSF方式導出
SXSSF方式是XSSF方式的一種延伸，主要特性是低內存，導出的時候，先將數據寫入磁碟再導出，避免報內存不足，導致程序運行異常，缺點是運行很慢！
package cn.tedu.excel.test;
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.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
public class ExcelWriteSXSSFTest { public static String PATH = "/Users/lixin/Desktop/";
public static void main(String[] args) throws Exception { long begin = System.currentTimeMillis();
Workbook workbook = new SXSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int rowNumber = 0; rowNumber < 100000; rowNumber++) { Row row = sheet.createRow(rowNumber); for (int cellNumber = 0; cellNumber < 10; cellNumber++) { Cell cell = row.createCell(cellNumber); cell.setCellValue(cellNumber); } } System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用戶信息表-SXSSF.xlsx"); workbook.write(fileOutputStream); fileOutputStream.close(); long end = System.currentTimeMillis(); System.out.println((double) (end - begin) / 1000); }}1.3 導入excel
導入操作，即將excel中的數據採用java工具庫將其解析出來，進而將excel 數據寫入資料庫！
同樣，在poi工具庫中，導入api也分三種方式，與上面的導出一一對應！
1.3.1 HSSF方式導入
package cn.tedu.excel.test;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.*;import org.joda.time.DateTime;
import java.io.FileInputStream;import java.util.Date;
public class ExcelRead2003Test { public static String PATH = "/Users/lixin/Desktop/";
public static void main(String[] args) throws Exception { FileInputStream inputStream = new FileInputStream(PATH + "用戶信息表2003read.xls");
Workbook workbook = new HSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); Cell cell = row.getCell(0); getValue(cell); inputStream.close(); }
public static void getValue(Cell cell){ if (cell != null) { CellType cellType = cell.getCellType(); String cellValue = ""; switch (cellType) { case STRING: System.out.print("[String類型]"); cellValue = cell.getStringCellValue(); break; case BOOLEAN: System.out.print("[boolean類型]"); cellValue = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: System.out.print("[BLANK類型]"); break; case NUMERIC: System.out.print("[NUMERIC類型]"); if (HSSFDateUtil.isCellDateFormatted(cell)) { System.out.print("[日期]"); Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd"); } else { System.out.print("[轉換為字符串輸出]"); cell.setCellType(CellType.STRING); cellValue = cell.toString(); } break; case ERROR: System.out.print("[數據類型錯誤]"); break; } System.out.println(cellValue); } }}
————————————————版權聲明：本文為CSDN博主「經理，天台風好大」的原創文章，遵循CC 4.0 BY-SA版權協議，轉載請附上原文出處連結及本聲明。原文連結：https:package cn.tedu.excel.test;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;import org.apache.poi.ss.usermodel.*;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.joda.time.DateTime;
import java.io.FileInputStream;import java.util.Date;
public class ExcelRead2007Test { public static String PATH = "/Users/lixin/Desktop/";
public static void main(String[] args) throws Exception { FileInputStream inputStream = new FileInputStream(PATH + "用戶信息表2007read.xlsx");
Workbook workbook = new XSSFWorkbook(inputStream); Sheet sheet = workbook.getSheetAt(0); Row row = sheet.getRow(0); Cell cell = row.getCell(0); getValue(cell); inputStream.close(); } public static void getValue(Cell cell){ if (cell != null) { CellType cellType = cell.getCellType(); String cellValue = ""; switch (cellType) { case STRING: System.out.print("[String類型]"); cellValue = cell.getStringCellValue(); break; case BOOLEAN: System.out.print("[boolean類型]"); cellValue = String.valueOf(cell.getBooleanCellValue()); break; case BLANK: System.out.print("[BLANK類型]"); break; case NUMERIC: System.out.print("[NUMERIC類型]"); if (HSSFDateUtil.isCellDateFormatted(cell)) { System.out.print("[日期]"); Date date = cell.getDateCellValue(); cellValue = new DateTime(date).toString("yyyy-MM-dd"); } else { System.out.print("[轉換為字符串輸出]"); cell.setCellType(CellType.STRING); cellValue = cell.toString(); } break; case ERROR: System.out.print("[數據類型錯誤]"); break; } System.out.println(cellValue); } }}package cn.tedu.excel.test;
import org.apache.poi.ooxml.util.SAXHelper;import org.apache.poi.openxml4j.opc.OPCPackage;import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;import org.apache.poi.xssf.eventusermodel.XSSFReader;import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;import org.apache.poi.xssf.model.StylesTable;import org.apache.poi.xssf.usermodel.XSSFComment;import org.xml.sax.InputSource;import org.xml.sax.XMLReader;
import java.io.InputStream;import java.util.ArrayList;import java.util.Iterator;import java.util.List;import java.util.stream.Collectors;
public class ExcelReadSXSSFTest { public static String PATH = "/Users/lixin/Desktop/";
public static void main(String[] args) throws Exception {
OPCPackage opcPackage = OPCPackage.open(PATH + "用戶信息表2007read.xlsx"); XSSFReader xssfReader = new XSSFReader(opcPackage); StylesTable stylesTable = xssfReader.getStylesTable(); ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(opcPackage); XMLReader xmlReader = SAXHelper.newXMLReader(); xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, new SimpleSheetContentsHandler(), false)); Iterator<InputStream> sheetsData = xssfReader.getSheetsData(); while (sheetsData.hasNext()) { try (InputStream inputStream = sheetsData.next();) { xmlReader.parse(new InputSource(inputStream)); } } } public static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
protected List<String> row;
@Override public void startRow(int rowNum) { row = new ArrayList<>(); }
@Override public void endRow(int rowNum) { if (row.isEmpty()) { return; } System.out.println(row.stream().collect(Collectors.joining(" "))); }
@Override public void cell(String cellReference, String formattedValue, XSSFComment comment) { row.add(formattedValue); }
@Override public void headerFooter(String text, boolean isHeader, String tagName) { } }}