一、导出
1、数据源（树形结构数据）
DcdbInfoAO infoAO（根数据）
DcdbTaskMainAO（一级目录数据）
DcdbTaskSubAO（二级目录数据）
以上为三个实体类，结构为：
List<DcdbTaskMainAO> mainList = infoAO.getMainTaskList();
DcdbTaskMainAO mainAO = mainList.get(i);
List<DcdbTaskSubAO> subAOList = mainAO.getSubTaskList();
DcdbTaskSubAO subAO = subAOList.get(j);
2、代码示例
package com.yuanxin.app.appkmbgszh.util;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.RichTextString;
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.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.yuanxin.app.appkmbgszh.appobject.DcdbInfoAO;
import com.yuanxin.app.appkmbgszh.appobject.DcdbTaskMainAO;
import com.yuanxin.app.appkmbgszh.appobject.DcdbTaskSubAO;
/**
* 督办任务excel导出工具类;
* @author Zhangwei
*
*/
public class ExcelDcdbExportUtil {
private int cols = 12;//excel里表格列
private String sheetTitle = "2019年工作要点";
public void setCols(int cols) {
this.cols = cols;
}
public void setSheetTitle(String sheetTitle) {
this.sheetTitle = sheetTitle;
}
/**
* POI : 导出数据，存放于Excel中
*
* @param os
* 输出流 (action: OutputStream os = response.getOutputStream();)
* @param infoAO
* 要导出的数据记录集合(根数据)
* @param mainAO
* 重点任务数据(重大工作项:一级目录)
* @param mainAO
* 分解任务数据(任务分解项:二级目录)
*/
public void exportTaskSumPoi(OutputStream os, DcdbInfoAO infoAO) {
//取出数据源;
String headTitle = infoAO.getTitle();//标题;
List<DcdbTaskMainAO> mainList = infoAO.getMainTaskList();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日");
try {
// 创建Excel工作薄
Workbook book = null;
try {
book = new XSSFWorkbook();//excell2007
} catch (Exception ex) {
book = new HSSFWorkbook();//excell2003
}
//表内容默认从工作表第三行开始;
int start = 2;
// 在Excel工作薄中建一张工作表;
sheetTitle = infoAO.getTitle();
Sheet sheet = book.createSheet(sheetTitle);
//设置标题和表头;
createTitle(book, sheet, headTitle);
createHead(book, sheet);
//根据数据源设置表结构;
for (int i = 0; i < mainList.size(); i++) {
DcdbTaskMainAO mainAO = mainList.get(i);
List<DcdbTaskSubAO> subAOList = mainAO.getSubTaskList();
for (int j = 0; j < subAOList.size(); j++) {
Row row = sheet.createRow(start + j);
DcdbTaskSubAO subAO = subAOList.get(j);
Cell cell0 = row.createCell(0);
Cell cell1 = row.createCell(1);
Cell cell2 = row.createCell(2);
Cell cell3 = row.createCell(3);
Cell cell4 = row.createCell(4);
Cell cell5 = row.createCell(5);
Cell cell6 = row.createCell(6);
Cell cell7 = row.createCell(7);
Cell cell8 = row.createCell(8);
Cell cell9 = row.createCell(9);
Cell cell10 = row.createCell(10);
Cell cell11 = row.createCell(11);
//前5列为重点工作任务(一级目录);
cell0.setCellValue(mainAO.getBusinessArea() != null ? mainAO.getBusinessArea() : " ");
cell1.setCellValue(mainAO.getSeq() != null ? mainAO.getSeq() : " ");
cell2.setCellValue(mainAO.getMajorTask() != null ? mainAO.getMajorTask() : " ");
cell3.setCellValue(mainAO.getTaskTarget() != null ? mainAO.getTaskTarget() : " ");
cell4.setCellValue(mainAO.getLeaderUnitName() != null ? mainAO.getLeaderUnitName() : " ");
//后7列为任务及目标分解,不需要跨行;
if (subAO.getTaskDetail() != null) {
cell5.setCellValue(noteFontStyle(book, subAO.getTaskDetail()));//行内注解处理;
}
cell6.setCellValue(subAO.getResponsibleUnitNames() != null ? subAO.getResponsibleUnitNames() : " ");
if (subAO.getShouldFinishedDate() != null) {
cell7.setCellValue(sdf.format(subAO.getShouldFinishedDate()));//日期处理;
}
if (subAO.getActuallyFinishedDate() != null) {
cell8.setCellValue(sdf.format(subAO.getActuallyFinishedDate()));
}
if (subAO.getFinished() != null) {
cell9.setCellValue("0".equals(subAO.getFinished()) ? "是" : "否");//是否办结;
}
cell10.setCellValue(subAO.getDoneDetail() != null ? subAO.getDoneDetail() : " ");
cell11.setCellValue(subAO.getNote() != null ? subAO.getNote() : " ");
//设置单元格样式;
CellStyle contentStyle = createContentStyle(book);
CellStyle otherStyle = createOtherStyle(book);
//前8列背景、字体、边框、对齐方式;
for (int k = 0; k < 8; k++) {
row.getCell(k).setCellStyle(contentStyle);
}
//后4列背景、字体、边框、对齐方式、日期格式;
for (int l = 8; l < cols; l++) {
if (l == 8) {
row.getCell(8).setCellStyle(setDateStyle(book));//设置日期样式
//System.out.println(DateUtil.isCellDateFormatted(row.getCell(8)));//输出：true
}
row.getCell(l).setCellStyle(otherStyle);
}
//单元格列宽度;
for (int h = 0; h < cols; h++) {
if (h == 2 || h == 3 || h == 5 || h == 10 || h == 11) {
sheet.setColumnWidth(h, 40 * 256);//设置第一类列的宽度是44个字符宽度
} else if (h == 10) {
sheet.setColumnWidth(h, 60 * 256);
} else {
sheet.setColumnWidth(h, 16 * 256);//设置第一类列的宽度是16个字符宽度,水平居中;
CellStyle cellStyle = book.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
}
}
}
//前5列根据每一项重点工作的任务及目标分解数量来决定跨行数;
if (subAOList.size() > 0) {
sheet.addMergedRegion(new CellRangeAddress(start, start + subAOList.size() - 1, 0, 0));
sheet.addMergedRegion(new CellRangeAddress(start, start + subAOList.size() - 1, 1, 1));
sheet.addMergedRegion(new CellRangeAddress(start, start + subAOList.size() - 1, 2, 2));
sheet.addMergedRegion(new CellRangeAddress(start, start + subAOList.size() - 1, 3, 3));
sheet.addMergedRegion(new CellRangeAddress(start, start + subAOList.size() - 1, 4, 4));
start += subAOList.size();
}
}
// 写入数据 把相应的Excel 工作簿存盘
book.write(os);
book.close();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 给excel设置标题
*
* @param sheet
*/
public void createTitle(Workbook book, Sheet sheet, String headTitle) {
CellStyle style = createTitleStyle(book);
Row row = sheet.createRow(0);// 创建第一行,设置表的标题;
row.setHeightInPoints(36);//设置行的高度是34个点
Cell cell = row.createCell(0);
cell.setCellValue(headTitle);
cell.setCellStyle(style);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, cols-1));//第一行跨表所有列;
}
/**
* 设置导出表的表头
*
* @param book
* @param sheet
*/
private void createHead(Workbook book, Sheet sheet) {
// 设置单元格格式(文本)
// 第二行为表头行
String title = "";
CellStyle style = createHeadStyle(book);
Row row = sheet.createRow(1);// 创建第一行
row.setHeightInPoints(22);//设置行的高度是20个点
for (int j = 0; j < cols; j++) {
Cell cell = row.createCell(j);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
if (j == 0) {
title = "业务领域";
}
if (j == 1) {
title = "编号";
}
if (j == 2) {
title = "重点工作";
}
if (j == 3) {
title = "预期目标或成效";
}
if (j == 4) {
title = "牵头部门";
}
if (j == 5) {
title = "任务及目标分解";
}
if (j == 6) {
title = "责任部门";
}
if (j == 7) {
title = "办结期限";
}
if (j == 8) {
title = "实际完成时间";
sheet.setColumnWidth(j, title.getBytes().length * 2 * 256);
}
if (j == 9) {
title = "是否办结";
}
if (j == 10) {
title = "完成情况";
}
if (j == 11) {
title = "备注";
}
cell.setCellValue(title);
cell.setCellStyle(style);
}
}
/**
* 创建标题样式
* @param book
* @return
*/
public CellStyle createTitleStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
Font font = book.createFont();
font.setFontHeightInPoints((short) 20); // 字体大小
font.setFontName("宋体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
cellStyle.setFont(font);
return cellStyle;
}
/**
* 创建表头样式
* @param book
* @return
*/
public CellStyle createHeadStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填浅蓝色
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
Font font = book.createFont();
font.setFontHeightInPoints((short) 11); // 字体大小
font.setFontName("黑体");
font.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
cellStyle.setFont(font);
return cellStyle;
}
/**
* 创建内容部分前8列单元格样式
* @param book
* @return
*/
public CellStyle createContentStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // 填充单元格
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index); // 填浅蓝色
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
cellStyle.setWrapText(true);//自动换行
Font font = book.createFont();
font.setFontHeightInPoints((short) 11); // 字体大小
font.setFontName("宋体");
cellStyle.setFont(font);
return cellStyle;
}
/**
* 创建内容其它部分单元格样式
* @param book
* @return
*/
public CellStyle createOtherStyle(Workbook book) {
CellStyle cellStyle = book.createCellStyle();
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
cellStyle.setWrapText(true);//自动换行
Font font = book.createFont();
font.setFontHeightInPoints((short) 11); // 字体大小
font.setFontName("宋体");
cellStyle.setFont(font);
return cellStyle;
}
/**
* 设置字符串内注解样式;
* @param book
* @param str 传入的待处理字符串;
* @return
*/
public RichTextString noteFontStyle(Workbook book, String str) {
//定义字体
Font hFont = book.createFont();
hFont.setFontHeightInPoints((short) 10);//字体大小
hFont.setFontName("楷体");
RichTextString richString = null;
try {//2007版excel
richString = new XSSFRichTextString(str);//2007版excel
} catch (Exception e) {
richString = new HSSFRichTextString(str);//2003版excel
}
if (str.indexOf("（") == -1) {
return richString;
}
//字体样式设置到字符串中;
richString.applyFont(str.indexOf("（"), str.indexOf("）"), hFont);
return richString;
}
/**
* 设置日期格式;
* @param book
* @param str
* @return
*/
public CellStyle setDateStyle(Workbook book) {
CellStyle style = book.createCellStyle();
try {//2007版excel
DataFormat format = book.createDataFormat();
style.setDataFormat(format.getFormat("yyyy年MM月dd日"));
} catch (Exception e) {//2003版excel
style.setDataFormat(HSSFDataFormat.getBuiltinFormat("yyyy年MM月dd日"));
}
return style;
}
}
3.导出结果
二、导入（数据库）
1、数据源
InputStream inIo：输入流，excel文件的上传输入流;
String sheetName:excel工作表sheet名;
int minColumns:待导入工作表列数;
2、数据结果（树形结构数据）
待导入Excel工作表前5列为合并单元格，对应java一级目录实体类；后7列为非合并单元格，对应java二级目录实体类；
DcdbInfoAO infoAO（根数据）
TaskMainAO（一级目录数据）
TaskSubAO（二级目录数据）
3、代码示例
package com.yuanxin.app.appkmbgszh.util;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
/**
* POI以SAX解析excel2007文件工具类
* 使用CVS模式解决XLSX文件，可以有效解决用户模式内存溢出的问题
* 该模式是POI官方推荐的读取大数据的模式，在用户模式下，数据量较大、Sheet较多、或者是有很多无用的空行的情况
* ，容易出现内存溢出,用户模式读取Excel的典型代码如下： FileInputStream file=new
* FileInputStream("c:\\test.xlsx"); Workbook wb=new XSSFWorkbook(file);
*
* @author zhangwei
*/
public class ExcelDcdbImportUtil {
//excll中存在的样式;
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER;
}
/**
*/
class MyXSSFSheetHandler extends DefaultHandler {
/**
* 表样式
*/
private StylesTable stylesTable;
/**
* 表中独特的字符串
*/
private ReadOnlySharedStringsTable sharedStringsTable;
/**
* 数据输出
*/
private final PrintStream output;
/**
* 最大列数
*/
private final int minColumnCount;
// 当看到v start元素时设置
private boolean vIsOpen;
// 当单元格开始元素出现时设置；
//当看到cell close元素时使用。
private xssfDataType nextDataType;
// 用于设置数值单元格值的格式
private short formatIndex;
private String formatString;
private final DataFormatter formatter;
private int thisColumn = -1;
// 打印到输出流的最后一列
private int lastColumnNumber = -1;
// 收集所看到的角色
private StringBuffer value;
private String[] record;
private List<String[]> rows = new ArrayList<String[]>();
private boolean isCellNull = false;
/**
* 接受分析时所需的对象。
* @param styles
* @param strings 共享字符串
* @param cols 要显示的最小列数
* @param target
*/
public MyXSSFSheetHandler(StylesTable styles, ReadOnlySharedStringsTable strings, int cols, PrintStream target) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.minColumnCount = cols;
this.output = target;
this.value = new StringBuffer();
this.nextDataType = xssfDataType.NUMBER;
this.formatter = new DataFormatter();
record = new String[this.minColumnCount];
rows.clear();// 每次读取都清空行集合
}
/*
* 第一个执行方法,用于设定单元格的数字类型（如日期、数字、字符串等等）;
*/
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
if ("inlineStr".equals(name) || "v".equals(name)) {
vIsOpen = true;
// Clear contents cache
value.setLength(0);
}
// c => cell
else if ("c".equals(name)) {
// Get the cell reference
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
thisColumn = nameToColumn(r.substring(0, firstDigit));
// Set up defaults.
this.nextDataType = xssfDataType.NUMBER;
this.formatIndex = -1;
this.formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
if ("b".equals(cellType))
nextDataType = xssfDataType.BOOL;
else if ("e".equals(cellType))
nextDataType = xssfDataType.ERROR;
else if ("inlineStr".equals(cellType))
nextDataType = xssfDataType.INLINESTR;
else if ("s".equals(cellType))
nextDataType = xssfDataType.SSTINDEX;
else if ("str".equals(cellType))
nextDataType = xssfDataType.FORMULA;
else if (cellStyleStr != null) {
// It's a number, but almost certainly one
// with a special style or format
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
this.formatIndex = style.getDataFormat();
this.formatString = style.getDataFormatString();
if (this.formatString == null)
this.formatString = BuiltinFormats.getBuiltinFormat(this.formatIndex);
}
}
}
/*
* 最后一个执行方法;
*/
public void endElement(String uri, String localName, String name) throws SAXException {
String thisStr = null;
// v => contents of a cell
if ("v".equals(name)) {
// 根据需要处理值内容。
// 这时characters()方法可能会被调用多次
switch (nextDataType) {
case BOOL:
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR:
thisStr = "\"ERROR:" + value.toString() + '"';
break;
case FORMULA:
// A formula could result in a string value,
// so always add double-quote characters.
thisStr = '"' + value.toString() + '"';
break;
case INLINESTR:
// TODO: have seen an example of this, so it's untested.
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = '"' + rtsi.toString() + '"';
break;
//字符串
case SSTINDEX:
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
//根据idx索引值获取内容值
XSSFRichTextString rtss = new XSSFRichTextString(sharedStringsTable.getEntryAt(idx));
thisStr = rtss.toString();
} catch (NumberFormatException ex) {
output.println("Failed to parse SST index '" + sstIndex + "': " + ex.toString());
}
break;
case NUMBER:
String n = value.toString();
// 判断是否是日期格式
if (HSSFDateUtil.isADateFormat(this.formatIndex, n)) {
Double d = Double.parseDouble(n);
Date date = HSSFDateUtil.getJavaDate(d);
thisStr = formateDateToString(date);
} else if (this.formatString != null)
thisStr = formatter.formatRawCellContents(Double.parseDouble(n), this.formatIndex,
this.formatString);
else
thisStr = n;
break;
default:
thisStr = "(TODO: Unexpected type: " + nextDataType + ")";
break;
}
// Output after we've seen the string contents
// Emit commas for any fields that were missing on this row
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
//判断单元格的值是否为空
if (thisStr == null || "".equals(isCellNull)) {
isCellNull = true;// 设置单元格是否为空值
}
record[thisColumn] = thisStr;
// Update column
if (thisColumn > -1)
lastColumnNumber = thisColumn;
} else if ("row".equals(name)) {
// Print out any missing commas if needed
if (minColumns > 0) {
// Columns are 0 based
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
boolean recordflag = record[0] != null;
for (int i = 1; i < minColumns; i++) {
recordflag = recordflag || (record[i] != null);
}
if (isCellNull == false && recordflag)// 判断是否空行
{
rows.add(record.clone());
isCellNull = false;
for (int i = 0; i < record.length; i++) {
record[i] = null;
}
}
}
lastColumnNumber = -1;
}
}
public List<String[]> getRows() {
return rows;
}
public void setRows(List<String[]> rows) {
this.rows = rows;
}
/**
* 仅在打开适当的元素时捕获字符。最初只是“v”；扩展为inlinestr
*/
public void characters(char[] ch, int start, int length) throws SAXException {
if (vIsOpen)
value.append(ch, start, length);
}
/**
* 将Excel列名（如“C”）转换为从零开始的索引
*
* @param name
* @return 与指定名称对应的索引
*/
private int nameToColumn(String name) {
int column = -1;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
private String formateDateToString(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd日");//格式化日期
return sdf.format(date);
}
}
private OPCPackage xlsxPackage;
private int minColumns;
private PrintStream output;
private String sheetName;
/**
* 创建新的XLSX->csv转换器
*
* @param pkg
* 要处理的XLSX包
* @param output
* 要将csv输出到的打印流
* @param minColumns
* 要输出的最小列数，或-1表示无最小值
*/
public ExcelDcdbImportUtil(OPCPackage pkg, PrintStream output, String sheetName, int minColumns) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this.sheetName = sheetName;
}
/**
*使用指定的样式和共享字符串表解析和显示一个工作表的内容。
*
* @param styles
* @param strings
* @param sheetInputStream
*/
public List<String[]> processSheet(StylesTable styles, ReadOnlySharedStringsTable strings,
InputStream sheetInputStream) throws IOException, ParserConfigurationException, SAXException {
InputSource sheetSource = new InputSource(sheetInputStream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.getXMLReader();
MyXSSFSheetHandler handler = new MyXSSFSheetHandler(styles, strings, this.minColumns, this.output);
sheetParser.setContentHandler(handler);
//解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
sheetParser.parse(sheetSource);
return handler.getRows();
}
/**
* 初始化这个处理程序 将
*
* @throws IOException
* @throws OpenXML4JException
* @throws ParserConfigurationException
* @throws SAXException
*/
public List<String[]> process() throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
List<String[]> list = null;
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
int index = 0;
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetNameTemp = iter.getSheetName();
if (this.sheetName.equals(sheetNameTemp)) {
list = processSheet(styles, strings, stream);
stream.close();
++index;
}
}
return list;
}
/**
* 读取Excel
*
* @param path
* 文件路径
* @param sheetName
* sheet名称
* @param minColumns
* 列总数
* @return
* @throws SAXException
* @throws ParserConfigurationException
* @throws OpenXML4JException
* @throws IOException
*/
public static List<String[]> readerExcel(InputStream inIo, String sheetName, int minColumns) throws IOException,
OpenXML4JException, ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(inIo);
ExcelDcdbImportUtil xlsx2csv = new ExcelDcdbImportUtil(p, System.out, sheetName, minColumns);
List<String[]> list = xlsx2csv.process();
p.close();
return list;
}
}
三、最后
示例中导入导出均需要引入必要的依赖jar包，需要自己构建树形结构实体类，此外示例未给出测试类和测试方法、以及前端相关代码。
<dependency>
<groupId>poi-3.11-20141221</groupId>
<artifactId>poi-3.11-20141221</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>poi-ooxml-3.11-20141221</groupId>
<artifactId>poi-ooxml-3.11-20141221</artifactId>
<version>1.0.0</version>
</dependency>
<dependency>
<groupId>poi-ooxml-schemas-3.11-20141221</groupId>
<artifactId>poi-ooxml-schemas-3.11-20141221</artifactId>
<version>1.0.0</version>
</dependency>
![js实现前端导入excel后用纯js完成转换数组（公司框架，自行变通）[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)