Java学习笔记-EXCEL文件的读写操作

一、基于POI操作EXCEL文件

1.

2.

3.org.apache.poi

4.poi

5.4.1.2

6.

7.

8.org.apache.poi

9.poi-ooxml

10.4.1.2

11.

12.

13.org.apache.poi

14.poi-ooxml-schemas

15.4.1.2

16.

17.

18.xerces

19.xercesImpl

20.2.12.0

21.

EXCEL文件读操作示例如下：

1.import https://www.360docs.net/doc/f711475319.html,ng.StringUtils;

2.import https://www.360docs.net/doc/f711475319.html,ng.time.DateFormatUtils;

3.import org.apache.poi.openxml4j.exceptions.OpenXML4JException;

4.import org.apache.poi.openxml4j.opc.OPCPackage;

5.import https://www.360docs.net/doc/f711475319.html,ermodel.*;

6.import org.apache.poi.xssf.eventusermodel.XSSFReader;

7.import org.apache.poi.xssf.model.SharedStringsTable;

8.import org.apache.poi.xssf.model.StylesTable;

9.import https://www.360docs.net/doc/f711475319.html,ermodel.XSSFCellStyle;

10.import https://www.360docs.net/doc/f711475319.html,ermodel.XSSFRichTextString;

11.import org.jdom2.Attribute;

12.import org.jdom2.Document;

13.import org.jdom2.Element;

14.import org.jdom2.JDOMException;

15.import org.jdom2.input.SAXBuilder;

16.import org.platform.utils.date.DateFormatter;

17.import org.slf4j.Logger;

18.import org.slf4j.LoggerFactory;

19.import org.xml.sax.*;

20.import org.xml.sax.helpers.DefaultHandler;

21.import org.xml.sax.helpers.XMLReaderFactory;

22.import java.io.*;

23.import java.text.DecimalFormat;

24.import java.text.SimpleDateFormat;

25.import java.util.ArrayList;

26.import java.util.Date;

27.import java.util.Iterator;

28.import java.util.List;

29.import java.util.function.Consumer;

30.

31.public class PoiExcelFileReader {

32.private Logger LOG =

LoggerFactory.getLogger(PoiExcelFileReader.class);

33.enum XssfDataType {

34.BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX,

NUMBER, DATE, NULL

35.}

36.public static final SimpleDateFormat SDF = DateFormatter.TIME.get();

37.public static final DecimalFormat DF = new DecimalFormat("0");

38.public static final int ERROR = 0;

39.public static final int BOOLEAN = 1;

40.public static final int NUMBER = 2;

41.public static final int STRING = 3;

42.public static final int DATE = 4;

43.public static final String DATE_FORMAT_STR1 = "yyyy-MM-dd

HH:mm:ss";

44.public static final String DATE_FORMAT_STR2 = "yyyy/MM/dd

HH:mm:ss";

45.private boolean isExcel2007 = true;

46.private InputStream inputStream = null;

47.private InputStream sheetInputStream = null;

48.private InputSource sheetInputSource = null;

49.private XMLReader xmlReader = null;

50.private List rowDataList = new

ArrayList();

51.// 批量处理

52.private Consumer> consumer = null;

53.// 批量处理的阀值

54.private Integer threshold = null;

55.// 跳过表头的行数

56.private Integer skipHeadLineCount = 0;

57.

58.public PoiExcelFileReader(InputStream in, String suffix,

Consumer> consumer, int threshold) {

59.this(in, suffix, consumer, threshold, 0);

60.}

61.

62.public PoiExcelFileReader(InputStream in, String suffix,

Consumer> consumer, int threshold,

63.int skipHeadLineCount) {

64.this.inputStream = in;

65.this.isExcel2007 = "xls".equals(suffix.toLowerCase()) ? false :

true;

66.this.consumer = consumer;

67.this.threshold = threshold;

68.this.skipHeadLineCount = skipHeadLineCount;

69.}

70.

71.public void parse() {

72.if (isExcel2007) {

73.try {

74.init(OPCPackage.open(inputStream));

75.} catch (Exception e) {

76.LOG.error(e.getMessage(), e);

77.}

78.parseExcel2007();

79.} else {

80.parseExcel2003(inputStream);

81.}

82.}

83.

84.public List getRowDataList() {

85.return this.rowDataList;

86.}

87.

88./** 初始化将Excel转换为XML */

89.private void init(OPCPackage pkg) throws IOException,

OpenXML4JException, SAXException{

90.XSSFReader xssfReader = new XSSFReader(pkg);

91.SharedStringsTable sharedStringsTable =

xssfReader.getSharedStringsTable();

92.StylesTable stylesTable = xssfReader.getStylesTable();

93.sheetInputStream =

xssfReader.getSheet(getSheetId(xssfReader.getWorkbookData()));

94.sheetInputSource = new InputSource(sheetInputStream);

95.xmlReader = getXmlReader(sharedStringsTable, stylesTable);

96.}

97.

98.private XMLReader getXmlReader(SharedStringsTable

sharedStringsTable, StylesTable stylesTable) throws SAXException {

99.XMLReader xmlReader =

XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");

100.ContentHandler contentHandler = new

CustomHandler(sharedStringsTable, stylesTable);

101.xmlReader.setContentHandler(contentHandler);

102.return xmlReader;

103.}

104.

105.private void parseExcel2007(){

106.try {

107.xmlReader.parse(sheetInputSource);

108.} catch (Exception e) {

109.LOG.error(e.getMessage(), e);

110.} finally{

111.if(sheetInputStream != null){

112.try {

113.sheetInputStream.close(); 114.} catch (IOException e) {

115.LOG.error(e.getMessage(), e); 116.}

117.}

118.}

119.}

120.

121.private String getSheetId(InputStream workbookDataInputStream) { 122.String sheetId = "rId1";

123.BufferedReader br = null;

124.try {

125.br = new BufferedReader(new

InputStreamReader(workbookDataInputStream));

126.String line = null;

127.loop:

128.while (null != (line = br.readLine())) {

129.if (line.startsWith("

130.InputStream in = null;

131.try {

132.in = new

ByteArrayInputStream(line.getBytes("UTF-8"));

133.Document document = new SAXBuilder().build(in);

134.Element rootElement = document.getRootElement();

135.Iterator iterator = rootElement.getChildren().iterator();

136.while

(iterator.hasNext()) {

137.Element element = iterator.next();

138.if

(!"sheets".equals(element.getName())) continue;

139.

List children = element.getChildren();

140.for (int i = 0, iLen = children.size(); i < iLen; i++) {

141.

Element subElement = children.get(i);

142.

for (Attribute attribute : subElement.getAttributes()) {

143.if ("id".equa 144.s 145.b 146.}

147.

}

148.}

149.}

150.} catch (JDOMException e) {

151.

LOG.error(e.getMessage(), e);

152.} finally {

153.try {

154.if (null != in) in.close();

155.} catch (Exception e) {

156.

LOG.error(e.getMessage(), e);

157.}

158.}

159.}

160.}

161.} catch (IOException e) {

162.LOG.error(e.getMessage(), e);

163.} finally {

164.try {

165.if (null != workbookDataInputStream) workbookDataInputStream.close();

166.if (null != br) br.close();

167.} catch (Exception e) {

168.LOG.error(e.getMessage(), e); 169.}

170.}

171.return sheetId;

172.}

173.

174.private class CustomHandler extends DefaultHandler {

175.// 映射字符串

176.private SharedStringsTable sharedStringsTable = null; 177.// 单元格样式

178.private StylesTable stylesTable = null;

179.// 读取值

180.private String readValue = null;

181.// 单元格类型

182.private XssfDataType dataType = null;

183.// 一行中数据文本

184.private String[] rowStrings = null;

185.// 一行中数据类型

186.private int[] rowTypes = null;

187.// 当前行索引

188.private int rowIndex = 0;

189.// 当前列索引

190.private int columnIndex;

191.private short formatIndex = -1;

192.

193.private CustomHandler(SharedStringsTable sharedStringsTable,StylesTable stylesTable) {

194.this.sharedStringsTable = sharedStringsTable;

195.this.stylesTable = stylesTable;

196.}

197.

198.@Override

199.public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {

200.// 单元格

201.if (qName.equals("c")) {

202.this.columnIndex =

getColumnIndex(attributes);

203.String cellType = attributes.getValue("t"); 204.String cellStyle = attributes.getValue("s"); 205.this.dataType = XssfDataType.NUMBER; 206.if ("b".equals(cellType)) {

207.this.dataType = XssfDataType.BOOL;

208.} else if ("e".equals(cellType)) {

209.this.dataType = XssfDataType.ERROR;

210.} else if ("inlineStr".equals(cellType)) {

211.this.dataType = XssfDataType.INLINESTR; 212.} else if ("s".equals(cellType)) {

213.this.dataType = XssfDataType.SSTINDEX;

214.} else if ("str".equals(cellType)) {

215.this.dataType = XssfDataType.FORMULA; 216.} else if (cellStyle != null) {

217.int styleIndex = Integer.parseInt(cellStyle);

218.XSSFCellStyle style =

stylesTable.getStyleAt(styleIndex);

219.this.formatIndex = style.getDataFormat(); 220.}

221.} else if (qName.equals("row")) {

222.rowIndex++;

223.// 获取该行的单元格数初始化数组224.int columnNumber =

getColumnNumber(attributes);

225.this.rowStrings = new

String[columnNumber];

226.this.rowTypes = new int[columnNumber]; 227.}

228.readValue = "";

229.}

230.

231.@Override

232.public void endElement(String uri, String localName, String qName) throws SAXException {

233.// 单元格的值

234.if (qName.equals("v")) {

235.switch (this.dataType) {

236.case BOOL: {

237.

rowStrings[columnIndex] = readValue.charAt(0) == '0' ? "FALSE" : "TRUE"; 238.

rowTypes[columnIndex] = BOOLEAN;

239.break;

240.}

241.case ERROR: {

242.

rowStrings[columnIndex] = "ERROR:" + readValue.toString();

243.

rowTypes[columnIndex] = ERROR;

244.break;

245.}

246.case INLINESTR: {

247.

rowStrings[columnIndex] = new XSSFRichTextString(readValue).getString(); 248.

rowTypes[columnIndex] = STRING;

249.break;

250.}

251.case SSTINDEX: {

252.

rowStrings[columnIndex] =

sharedStringsTable.getItemAt(Integer.parseInt(readValue)).getString();

253.rowTypes[columnIndex] = STRING; 254.break;

255.}

256.case FORMULA: {

257.

rowStrings[columnIndex] = readValue;

258.

rowTypes[columnIndex] = STRING;

259.break;

260.}

261.case NUMBER: {

262.// 判断是否是日期格式

263.if (formatIndex != -1 && DateUtil.isADateFormat(formatIndex, readValue)) {

264.Date date =

DateUtil.getJavaDate(Double.parseDouble(readValue));

265.if (null != date) {

266.

rowStrings[columnIndex] = DateFormatUtils.format(date, DATE_FORMAT_STR1); 267.} else { 268.

rowStrings[columnIndex] = readValue;

269.}

270.

rowTypes[columnIndex] = DATE;

271.

formatIndex = -1;

272.} else {

273.rowStrings[columnIndex] = readValue;

274.rowTypes[columnIndex] = NUMBER;

275.}

276.break;

277.}

278.default : break;

279.}

280.} else if (qName.equals("row")) {

281.// 当解析的一行的末尾时输出数组中的数据

282.if (rowIndex > skipHeadLineCount) { 283.// 过滤空行

284.boolean isValid = false;

285.for (int i = 0, len = rowStrings.length; i < len; i++) {

286.if (StringUtils.isNotBlank(rowStrings[i])) {

287.isValid = true;

288.break; 289.}

290.}

291.if (isValid) {

292.rowDataList.add(new PoiExcelRow(rowIndex, rowTypes, rowStrings));

293.if (null != threshold && rowDataList.size() > threshold) {

294.

consumer.accept(rowDataList);

295.

rowDataList.clear();

296.}

297.}

298.}

299.}

300.}

301.

302./**

303.* 如果单元格类型是字符串、INLINESTR、数字、日期，readValue则是索引值

304.* 如果单元格类型是布尔值、错误、公式，readValue则是内容值

305.*/

306.@Override

307.public void characters(char[] ch, int start, int length) throws SAXException {

308.readValue += new String(ch, start, length); 309.}

310.

311.@Override

312.public void endDocument() throws SAXException {

313.super.endDocument();

314.if (rowDataList.size() > 0) {

315.consumer.accept(rowDataList);

316.rowDataList.clear();

317.}

318.}

319.

320.private int getColumnIndex(Attributes attributes) {

321.String attributeValue = attributes.getValue("r"); 322.int columnIndex = -1;

323.for (int i = 0, len = attributeValue.length(); i < len; ++i) { 324.if (Character.isDigit(attributeValue.charAt(i))) break;

325.columnIndex = (columnIndex + 1) * 26 + attributeValue.charAt(i) - 'A';

326.}

327.return columnIndex;

328.}

329.

330.private int getColumnNumber(Attributes attrubuts){

331.String spans = attrubuts.getValue("spans");

332.return StringUtils.isBlank(spans) ? -1 : Integer.parseInt(spans.substring(spans.indexOf(":") + 1));

333.}

334.

335.}

336.

337.private void parseExcel2003(InputStream in) {

338.Workbook workbook = null;

339.try {

340.workbook = WorkbookFactory.create(in);

341.Sheet sheet = workbook.getSheetAt(0);

342.int lastCellNum =

sheet.getRow(0).getPhysicalNumberOfCells();

343.for (int i = skipHeadLineCount, iLen = sheet.getLastRowNum();

i <= iLen; i++) {

344.Row row = sheet.getRow(i);

345.String[] rowStrings = new String[lastCellNum]; 346.for (int j = 0, jLen = lastCellNum; j < jLen; j++) { 347.Cell cell = row.getCell(j);

348.if (null == cell) continue;

349.Object cellValue = null;

350.switch (cell.getCellType()) {

351.case BOOLEAN: cellValue = cell.getBooleanCellValue(); break;

352.case ERROR: cellValue = cell.getErrorCellValue(); break;

353.case FORMULA: cellValue = cell.getCellFormula(); break;

354.case NUMERIC: cellValue = DF.format(cell.getNumericCellValue()); break;

355.default: cellValue = cell.getStringCellValue(); break;

356.}

357.rowStrings[j] = String.valueOf(cellValue);

358.}

359.rowDataList.add(new PoiExcelRow(i, null, rowStrings));

360.if (null != threshold && rowDataList.size() > threshold) {

361.consumer.accept(rowDataList); 362.rowDataList.clear();

363.}

364.}

365.if (rowDataList.size() > 0) {

366.consumer.accept(rowDataList);

367.rowDataList.clear();

368.}

369.} catch (Exception e) {

370.LOG.error(e.getMessage(), e);

371.} finally {

372.try {

373.if (null != in) in.close();

374.if (null != workbook) workbook.close(); 375.} catch (IOException e) {

376.LOG.error(e.getMessage(), e);

377.}

378.}

379.}

380.

381.}

EXCEL文件写操作示例如下：

1.import org.apache.poi.xssf.streaming.SXSSFCell;

2.import org.apache.poi.xssf.streaming.SXSSFRow;

3.import org.apache.poi.xssf.streaming.SXSSFSheet;

4.import org.apache.poi.xssf.streaming.SXSSFWorkbook;

5.import https://www.360docs.net/doc/f711475319.html,ermodel.XSSFCell;

6.import https://www.360docs.net/doc/f711475319.html,ermodel.XSSFRow;

7.import https://www.360docs.net/doc/f711475319.html,ermodel.XSSFSheet;

8.import https://www.360docs.net/doc/f711475319.html,ermodel.XSSFWorkbook;

9.import org.slf4j.Logger;

10.import org.slf4j.LoggerFactory;

11.

12.import java.io.IOException;

13.import java.io.OutputStream;

14.import java.math.BigDecimal;

15.import java.util.List;

16.

17.public class PoiExcelFileWriter {

18.

19.private static Logger LOG =

LoggerFactory.getLogger(PoiExcelFileWriter.class);

20.

21.public static void writeXSSFWorkbook(List headerList,

List> resultList, OutputStream outputStream) {

22.XSSFWorkbook xssfWorkbook = new XSSFWorkbook();

23.XSSFSheet xssfSheet = xssfWorkbook.createSheet("sheet1");

24.XSSFRow xssfRow = xssfSheet.createRow(0);

25.for (int i = 0, len = headerList.size(); i < len; i++) {

26.XSSFCell xssfCell = xssfRow.createCell(i);

27.xssfCell.setCellValue(headerList.get(i));

28.}

29.for (int i = 0, iLen = resultList.size(); i < iLen; i++) {

30.xssfRow = xssfSheet.createRow(i + 1);

31.List