1、POI常用Excel工具类
XLSXCovertCSVReader
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintStream;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import org.apache.commons.lang3.StringUtils;
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.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
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;
/**
* 使用CVS模式解决XLSX文件，可以有效解决用户模式内存溢出的问题
* 该模式是POI官方推荐的读取大数据的模式，在用户模式下，数据量较大、Sheet较多、或者是有很多无用的空行的情况
* ，容易出现内存溢出,用户模式读取Excel的典型代码如下： FileInputStream file=new
* FileInputStream("c:\\test.xlsx"); Workbook wb=new XSSFWorkbook(file);
*
*
*/
public class XLSXCovertCSVReader {
/**
* The type of the data value is indicated by an attribute on the cell. The
* value is usually in a "v" element within the cell.
*/
enum xssfDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER,
}
/**
* 使用xssf_sax_API处理Excel,请参考： http://poi.apache.org/spreadsheet/how-to.html#xssf_sax_api
* <p/>
* Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at
* http://www.ecma-international.org/publications/standards/Ecma-376.htm
* <p/>
* A web-friendly version is http://openiso.org/Ecma/376/Part4
*/
class MyXSSFSheetHandler extends DefaultHandler {
/**
* Table with styles
*/
private StylesTable stylesTable;
/**
* Table with unique strings
*/
private ReadOnlySharedStringsTable sharedStringsTable;
/**
* Destination for data
*/
private final PrintStream output;
/**
* Number of columns to read starting with leftmost
*/
private final int minColumnCount;
// Set when V start element is seen
private boolean vIsOpen;
// Set when cell start element is seen;
// used when cell close element is seen.
private xssfDataType nextDataType;
// Used to format numeric cell values.
private short formatIndex;
private String formatString;
private final DataFormatter formatter;
private int thisColumn = -1;
private int thisRowNum = -1;
// The last column printed to the output stream
private int lastColumnNumber = -1;
private boolean isSameColCountFlag = true;
// Gathers characters as they are seen.
private StringBuffer value;
private String[] record;
private List<String> recordList;
private List<String[]> rows = new Vector<String[]>();
private boolean isCellNull = false;
/**
* Accepts objects needed while parsing.
*
* @param styles
* Table of styles
* @param strings
* Table of shared strings
* @param cols
* Minimum number of columns to show
* @param target
* Sink for output
*/
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.SSTINDEX;
this.formatter = new DataFormatter();
record = new String[this.minColumnCount];
rows.clear();// 每次读取都清空行集合
thisRowNum = -1;
recordList = new Vector<>();
isSameColCountFlag = true;
}
public MyXSSFSheetHandler(StylesTable styles,
ReadOnlySharedStringsTable strings, int cols, PrintStream target,boolean isSameColCountFlag) {
this.stylesTable = styles;
this.sharedStringsTable = strings;
this.minColumnCount = cols;
this.output = target;
this.value = new StringBuffer();
this.nextDataType = xssfDataType.SSTINDEX;
this.formatter = new DataFormatter();
rows.clear();// 每次读取都清空行集合
thisRowNum = -1;
recordList = new Vector<>();
this.isSameColCountFlag = isSameColCountFlag;
}
/*
* (non-Javadoc)
*
* @see
* org.xml.sax.helpers.DefaultHandler#startElement(java.lang.String,
* java.lang.String, java.lang.String, org.xml.sax.Attributes)
*/
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
try {
if ("inlineStr".equals(name) || "v".equals(name)|| "is".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));
if(!isSameColCountFlag){
thisRowNum = numToColumn(r);
}
// 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);
}
}
} catch (Exception e) {
LoggerUtil.error(this.getClass(), "error",e);
}
}
/*
* (non-Javadoc)
*
* @see org.xml.sax.helpers.DefaultHandler#endElement(java.lang.String,
* java.lang.String, java.lang.String)
*/
public void endElement(String uri, String localName, String name)
throws SAXException {
String thisStr = null;
try {
// v => contents of a cell
if ("v".equals(name)||"is".equals(name)) {
// Process the value contents as required.
// Do now, as characters() may be called more than once
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);
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;// 设置单元格是否为空值
}
if(!isSameColCountFlag){
if(thisRowNum == 1){
recordList.add(thisStr);
}else{
// 当前行若大于 指定行长度时则提示
if(thisColumn >=minColumns) {
//System.out.println("当前列长度("+thisColumn+") 大于指定列长度("+minColumns+")");
return;
}
record[thisColumn] = thisStr;
// Update column
if (thisColumn > -1)
lastColumnNumber = thisColumn;
}
}else{
// 当前行若大于 指定行长度时则提示
if(thisColumn >=minColumns) {
//System.out.println("当前列长度("+thisColumn+") 大于指定列长度("+minColumns+")");
return;
}
record[thisColumn] = thisStr;
// Update column
if (thisColumn > -1)
lastColumnNumber = thisColumn;
}
} else if ("row".equals(name)) {
if(!isSameColCountFlag){
if(thisRowNum == 1){
minColumns = recordList.size();
String [] tempArray = new String[minColumns];
record =recordList.toArray(tempArray);
}
// Print out any missing commas if needed
if (minColumns > 0) {
// Columns are 0 based
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
if (isCellNull == false && record[primaryIndex] != null)// 判断是否空行
{
rows.add(record.clone());
isCellNull = false;
for (int i = 0; i < minColumns; i++) {
record[i] = null;
}
}
}
lastColumnNumber = -1;
}else{
// Print out any missing commas if needed
if (minColumns > 0) {
// Columns are 0 based
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
if (isCellNull == false && record[primaryIndex] != null)// 判断是否空行
{
rows.add(record.clone());
isCellNull = false;
for (int i = 0; i < record.length; i++) {
record[i] = null;
}
}
}
lastColumnNumber = -1;
}
}
} catch (Exception e) {
LoggerUtil.error(this.getClass(), "error",e);
LoggerUtil.info(this.getClass(), "name:"+name + " nextDataType="+nextDataType + " value"+thisStr);
}
}
public List<String[]> getRows() {
return rows;
}
public void setRows(List<String[]> rows) {
this.rows = rows;
}
/**
* Captures characters only if a suitable element is open. Originally
* was just "v"; extended for inlineStr also.
*/
public void characters(char[] ch, int start, int length)
throws SAXException {
if (vIsOpen)
value.append(ch, start, length);
}
/**
* Converts an Excel column name like "C" to a zero-based index.
*
* @param name
* @return Index corresponding to the specified name
*/
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 int numToColumn(String name){
if(name == null){
return -1;
}
String regEx="[^0-9]";
Pattern p = Pattern.compile(regEx);
Matcher m = p.matcher(name);
String str = m.replaceAll("").trim();
if(StringUtils.isEmpty(str)){
return -1;
}
return Integer.valueOf(str);
}
private String formateDateToString(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//格式化日期
return sdf.format(date);
}
}
// /
private OPCPackage xlsxPackage;
private int minColumns;
private PrintStream output;
private String sheetName;
private int sheetIndex;
private int primaryIndex;
/**
* Creates a new XLSX -> CSV converter
*
* @param pkg
* The XLSX package to process
* @param output
* The PrintStream to output the CSV to
* @param minColumns
* The minimum number of columns to output, or -1 for no minimum
*/
public XLSXCovertCSVReader(OPCPackage pkg, PrintStream output,
String sheetName, int minColumns) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this.sheetName = sheetName;
}
public XLSXCovertCSVReader(OPCPackage pkg, PrintStream output,
String sheetName, int minColumns, int primaryIndex) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this.sheetName = sheetName;
this.primaryIndex = primaryIndex;
}
public XLSXCovertCSVReader(OPCPackage pkg, PrintStream output,
int sheetIndex, int minColumns) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this.sheetIndex = sheetIndex;
}
public XLSXCovertCSVReader(OPCPackage pkg, PrintStream output,
int sheetIndex, int minColumns, int primaryIndex) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
this.sheetIndex = sheetIndex;
this.primaryIndex = primaryIndex;
}
/**
* Parses and shows the content of one sheet using the specified styles and
* shared-strings tables.
*
* @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);
sheetParser.parse(sheetSource);
return handler.getRows();
}
/**
* Parses and shows the content of one sheet using the specified styles and
* shared-strings tables.
*
* @param styles
* @param strings
* @param sheetInputStream
*/
public List<String[]> processSheet(StylesTable styles,
ReadOnlySharedStringsTable strings, InputStream sheetInputStream,boolean isSameFlag)
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,isSameFlag);
sheetParser.setContentHandler(handler);
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;
}
public List<String[]> processIndex() 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()) {
if(index == sheetIndex) {
InputStream stream = iter.next();
list = processSheet(styles, strings, stream);
stream.close();
}
if(index == 0 && (list ==null || list.size()==0)) {
InputStream stream = iter.next();
list = processSheet(styles, strings, stream);
stream.close();
}else {
break;
}
++index;
}
return list;
}
public List<List<String[]>> processAllSheet() throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
List<List<String[]>> result = new Vector<>();
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
List<String[]> list = processSheet(styles, strings, stream);
if(list != null && list.size() >0){
result.add(list);
}
stream.close();
}
return result;
}
public List<List<String[]>> processAllSheet(boolean flag) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
List<List<String[]>> result = new Vector<>();
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
List<String[]> list = processSheet(styles, strings, stream,flag);
if(list != null && list.size() >0){
result.add(list);
}
stream.close();
}
return result;
}
public Map<String,List<String[]>> processAllSheetByMap() throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
Map<String,List<String[]>> result = new HashMap<>();
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetName = iter.getSheetName();
List<String[]> list = processSheet(styles, strings, stream);
if(list != null && list.size() >0){
result.put(sheetName,list);
}
stream.close();
}
return result;
}
public Map<String,List<String[]>> processAllSheetByMap(boolean flag) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
Map<String,List<String[]>> result = new HashMap<>();
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetName = iter.getSheetName();
List<String[]> list = processSheet(styles, strings, stream,flag);
if(list != null && list.size() >0){
result.put(sheetName,list);
}
stream.close();
}
return result;
}
/**
* 读取Excel
*
* @param path
* 文件路径
* @param sheetName
* sheet名称
* @param minColumns
* 列总数
* @return
* @throws SAXException
* @throws ParserConfigurationException
* @throws OpenXML4JException
* @throws IOException
*/
public static List<String[]> readerExcel(String path, String sheetName,
int minColumns) throws Exception {
return readerExcel(path, sheetName, minColumns,0);
}
public static List<String[]> readerExcel(String path, String sheetName,
int minColumns,int avalibleColumns) throws Exception {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
sheetName, minColumns, avalibleColumns);
List<String[]> list = xlsx2csv.process();
p.close();
return list;
}
public static List<String[]> readerExcel(String path, int sheetName,
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerExcel(path, sheetName, minColumns,0);
}
public static List<String[]> readerExcel(String path, int sheetName,
int minColumns,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
sheetName, minColumns,avalibleColumns);
List<String[]> list = xlsx2csv.processIndex();
p.close();
return list;
}
public static List<List<String[]>> readerAllSameSheetExcel(String path,
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerAllSameSheetExcel(path, minColumns,0);
}
public static List<List<String[]>> readerAllSameSheetExcel(String path, int minColumns,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, minColumns,avalibleColumns);
List<List<String[]>> list = xlsx2csv.processAllSheet();
p.close();
return list;
}
public static List<List<String[]>> readerAllSheetExcel(String path,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
List<List<String[]>> list = xlsx2csv.processAllSheet(false);
p.close();
return list;
}
public static Map<String,List<String[]>> readerAllSheetExcelByMap(String path,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
Map<String,List<String[]>> list = xlsx2csv.processAllSheetByMap(false);
p.close();
return list;
}
public static List<List<String[]>> readerAllSheetExcel(String path) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerAllSheetExcel(path,0);
}
public static Map<String,List<String[]>> readerAllSheetExcelByMap(String path) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerAllSheetExcelByMap(path,0);
}
public static List<List<String[]>> readerAllSheetExcel(InputStream path) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerAllSheetExcel(path,0);
}
public static Map<String,List<String[]>> readerAllSheetExcelByMap(InputStream path) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerAllSheetExcelByMap(path,0);
}
public static List<List<String[]>> readerAllSheetExcel(InputStream path,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
List<List<String[]>> list = xlsx2csv.processAllSheet(false);
p.close();
return list;
}
public static Map<String,List<String[]>> readerAllSheetExcelByMap(InputStream path,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
Map<String,List<String[]>> list = xlsx2csv.processAllSheetByMap(false);
p.close();
return list;
}
public static List<String[]> readerExcel(InputStream path, int sheetName,
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerExcel(path, sheetName, minColumns,0);
}
public static List<String[]> readerExcel(InputStream path, int sheetName,
int minColumns,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
sheetName, minColumns, avalibleColumns);
List<String[]> list = xlsx2csv.processIndex();
p.close();
return list;
}
public static List<String[]> readerExcel(InputStream path, String sheetName,
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerExcel(path, sheetName, minColumns,0);
}
public static List<String[]> readerExcel(InputStream path, String sheetName,
int minColumns,int avalibleColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
sheetName, minColumns,avalibleColumns);
List<String[]> list = xlsx2csv.process();
p.close();
return list;
}
/**
* 复制样式
* @param fromStyle
* @param toStyle
*/
public static void copyCellStyle(CellStyle fromStyle,CellStyle toStyle){
try {
toStyle.cloneStyleFrom(fromStyle);
}catch (Exception e){
}
}
/**
* 复制合并单元格
* @param fromSheet
* @param toSheet
*/
public static void mergeSheetAllRegion(Sheet fromSheet, Sheet toSheet){
int num = fromSheet.getNumMergedRegions();
CellRangeAddress cellR = null;
for (int i = 0; i < num; i++){
cellR = fromSheet.getMergedRegion(i);
toSheet.addMergedRegion(cellR);
}
}
/**
* 复制单元格
* @param wb
* @param fromCell
* @param toCell
*/
public static void copyCell(Workbook wb,Cell fromCell,Cell toCell){
CellStyle newstyle = wb.createCellStyle();
copyCellStyle(fromCell.getCellStyle(),newstyle);
toCell.setCellStyle(newstyle);
if(fromCell.getCellComment() != null){
toCell.setCellComment(fromCell.getCellComment());
}
CellType fromCellType = fromCell.getCellTypeEnum();
toCell.setCellType(fromCellType);
if(fromCellType == CellType.NUMERIC){
if(DateUtil.isCellDateFormatted(fromCell)){
toCell.setCellValue(fromCell.getDateCellValue());
}else{
toCell.setCellValue(fromCell.getNumericCellValue());
}
}else if(fromCellType == CellType.STRING){
toCell.setCellValue(fromCell.getRichStringCellValue());
}else if(fromCellType == CellType.BLANK){
}else if(fromCellType == CellType.BOOLEAN){
toCell.setCellValue(fromCell.getBooleanCellValue());
}else if(fromCellType == CellType.ERROR){
toCell.setCellValue(fromCell.getErrorCellValue());
}else if(fromCellType == CellType.FORMULA){
toCell.setCellValue(fromCell.getCellFormula());
}else{
}
}
/**
* 复制行
* @param wb
* @param fromRow
* @param toRow
*/
public static void copyRow(Workbook wb,Row fromRow,Row toRow){
toRow.setHeight(fromRow.getHeight());
for(Iterator cellIt = fromRow.cellIterator();cellIt.hasNext();){
Cell tmpCell = (Cell) cellIt.next();
Cell newCell = toRow.createCell(tmpCell.getColumnIndex());
copyCell(wb,tmpCell,newCell);
}
}
/**
* 复制 sheet
* @param wb
* @param fromSheet
* @param toSheet
*/
public static void copySheet(Workbook wb, Sheet fromSheet, Sheet toSheet){
mergeSheetAllRegion(fromSheet,toSheet);
int length = fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();
for (int i = 0; i <= length; i++){
toSheet.setColumnWidth(i,fromSheet.getColumnWidth(i));
}
for(Iterator rowIt = fromSheet.rowIterator();rowIt.hasNext();){
Row fromRow = (Row) rowIt.next();
Row newRow = toSheet.createRow(fromRow.getRowNum());
copyRow(wb,fromRow,newRow);
}
}
}ExcelUtil
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import com.chinasoft.biz.attendanceTools.XLSB2Lists;
import com.chinasoft.biz.attendanceTools.controller.OmpFindDataController;
import com.chinasoft.util.ArraysTools;
import com.chinasoft.util.LoggerUtil;
import com.chinasoft.util.StringTools;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.binary.XSSFBSharedStringsTable;
import org.apache.poi.xssf.binary.XSSFBSheetHandler;
import org.apache.poi.xssf.binary.XSSFBStylesTable;
import org.apache.poi.xssf.eventusermodel.XSSFBReader;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
import org.xml.sax.SAXException;
import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
public class ExcelUtil {
private Gson gson = new GsonBuilder().setDateFormat("yyyy-MM-dd HH:mm:ss").create();
/**
* Excel 2003
*/
public final static String XLS = "xls";
/**
* Excel 2007
*/
public final static String XLSX = "xlsx";
/**
*
* @Title: getExcelHeadTitle
* @Description: 读取excel 标题列
* @param filePath excel文件所在路径
* @return List<String> 返回标题内容数组
*/
public List<String> getExcelHeadTitle(String filePath){
File file = new File(filePath);
return getExcelHeadTitle(file);
}
public static Workbook getWorkBook(MultipartFile file) throws IOException {
Workbook workBook = null;
if(file == null) return null;
String endName = getEndname(file.getOriginalFilename());
if(endName == null) return null;
if (endName.toLowerCase().equals(XLS)) {
workBook = new HSSFWorkbook(file.getInputStream());
} else if (endName.toLowerCase().equals(XLSX)) {
workBook = new XSSFWorkbook(file.getInputStream());
}
return workBook;
}
public static Workbook getWorkBook(File file) throws IOException {
Workbook workBook = null;
if(file == null) return null;
String endName = getEndname(file.getName());
if(endName == null) return null;
InputStream in = new FileInputStream(file);
if (endName.toLowerCase().equals(XLS)) {
workBook = new HSSFWorkbook(in);
} else if (endName.toLowerCase().equals(XLSX)) {
workBook = new XSSFWorkbook(in);
}
return workBook;
}
private static String getEndname(String s) {
if(StringTools.isEmpty(s)) {
return null;
}
if(s.indexOf(".") > -1) {
return s.substring(s.lastIndexOf(".")+1, s.length());
}
return null;
}
/**
*
* @Title: getExcelHeadTitle
* @Description: 读取excel 标题列
* @param f excel文件所在路径
* @return List<String> 返回标题内容数组
*/
public List<String> getExcelHeadTitle(File f){
String name = getMemType(f.getName());
FileInputStream fi = null;
try {
fi = new FileInputStream(f);
} catch (FileNotFoundException e2) {
LoggerUtil.error(this.getClass(), "文件不存在", e2);
e2.printStackTrace();
}
Workbook workbook = null;
try {
workbook = getWorkbookBySheetName(fi , name, null);
} catch (IOException e1) {
LoggerUtil.error(this.getClass(), "workbook 对象获取失败", e1);
e1.printStackTrace();
}
Sheet sheet = getSheet(workbook, null);
FormulaEvaluator evaluator = getEvaluator(workbook);
return getExcelHeadMenu(sheet, evaluator);
}
public static JsonObject createKeysHeadRelations(List<String> headList, List<String> keyList){
int length = headList.size();
JsonObject result = new JsonObject();
for (int i = 0; i < length; i++) {
result.addProperty(headList.get(i), keyList.get(i));
}
return result;
}
public static JsonObject createKeysHeadRelations(List<String> headList, String[] keyArray){
List<String> keyList = Arrays.asList(keyArray);
return createKeysHeadRelations(headList, keyList);
}
public static JsonObject createKeysHeadRelations(String[] headArray, String[] keyArray){
List<String> keyList = Arrays.asList(keyArray);
List<String> headList = Arrays.asList(headArray);
return createKeysHeadRelations(headList, keyList);
}
/**
*
* @Title: getExcelContent
* @Description: 根据列头值获取excel中文件内容 默认读取第一个标签页
* @param filePath 文件实际路径
* @param s excel标题头对应的变量名称
* @return JsonArray 返回类型 以key-value 数组形式返回， key为数据库中对应变量名称，value为String类型内容
*/
public JsonArray getExcelContent(String filePath, String [] s){
return getExcelContent(filePath,null , s);
}
/**
*
* @Title: getExcelContent
* @Description: 根据列头值获取excel中文件内容 默认读取第一个标签页
* @param filePath 文件实际路径
* @param s excel标题头对应的变量名称
* @param formate 格式化列内容，例如：{"fieldName1":{"英特尔":"1","amd":"2"},"fieldName2":{"黑"："1","白"："2","灰"："3"}}
* @return JsonArray 返回类型 以key-value 数组形式返回， key为数据库中对应变量名称，value为String类型内容
*/
public JsonArray getExcelContent(String filePath, String [] s, JsonObject formate){
return getExcelContent(filePath,null , s, formate);
}
/**
*
* @Title: getExcelContent
* @Description: 根据列头值获取excel中文件内容
* @param filePath 文件实际路径
* @param sheetName excel标签页名称
* @param s excel标题头对应的变量名称
* @return JsonArray 返回类型 以key-value 数组形式返回， key为数据库中对应变量名称，value为String类型内容
*/
public JsonArray getExcelContent(String filePath, String sheetName, String [] s, JsonObject formate){
File file = new File(filePath);
FileInputStream fis = null;
Workbook workbook = null;
JsonArray resultArray = null;
if(file.exists()){
try {
fis = new FileInputStream(file);
} catch (FileNotFoundException e1) {
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中", e1);
e1.printStackTrace();
return null;
}finally {
if(fis != null) {
try {
fis.close();
} catch (IOException e) {
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中", e);
}
}
}
Sheet sheet = getSheet(workbook, null);
FormulaEvaluator evaluator = getEvaluator(workbook);
List<String> headlist = getExcelHeadMenu(sheet, evaluator);
JsonObject keys = createKeysHeadRelations(headlist, s);
if(formate == null){
resultArray = exportListFromExcelArray(workbook, sheetName, keys);
}else{
resultArray = exportListFromExcelArray(workbook, sheetName, keys, formate);
}
}else{
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中");
return null;
}
return resultArray;
}
/**
*
* @Title: getExcelContent
* @Description: 根据列头值获取excel中文件内容
* @param filePath 文件实际路径
* @param filePath excel标签页名称
* @param s excel标题头对应的变量名称
* @return JsonArray 返回类型 以key-value 数组形式返回， key为数据库中对应变量名称，value为String类型内容
*/
public JsonArray getExcelContent(String filePath, String [] s, JsonObject formate, int[] timeIndex, String pattern){
File file = new File(filePath);
FileInputStream fis = null;
Workbook workbook = null;
JsonArray resultArray = null;
if(file.exists()){
try {
fis = new FileInputStream(file);
} catch (FileNotFoundException e1) {
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中",e1);
return null;
}finally {
if(fis != null) {
try {
fis.close();
} catch (IOException e) {
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中",e);
}
}
}
Sheet sheet = getSheet(workbook, null);
FormulaEvaluator evaluator = getEvaluator(workbook);
List<String> headlist = getExcelHeadMenu(sheet, evaluator);
JsonObject keys = createKeysHeadRelations(headlist, s);
if(formate == null){
resultArray = exportListFromExcelArray(workbook, null, keys, timeIndex,pattern);
}else{
resultArray = exportListFromExcelArray(workbook, null, keys, formate, timeIndex,pattern);
}
}else{
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中");
return null;
}
return resultArray;
}
/**
*
* @Title: getExcelContent
* @Description: 根据列头值获取excel中文件内容
* @param filePath 文件实际路径
* @param timeIndex excel标签页名称
* @param s excel标题头对应的变量名称
* @return JsonArray 返回类型 以key-value 数组形式返回， key为数据库中对应变量名称，value为String类型内容
*/
public JsonArray getExcelContent(String filePath, String [] s, JsonObject formate, int[] timeIndex){
File file = new File(filePath);
FileInputStream fis = null;
Workbook workbook = null;
JsonArray resultArray = null;
if(file.exists()){
try {
fis = new FileInputStream(file);
} catch (FileNotFoundException e1) {
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中",e1);
return null;
}finally {
if(fis != null) {
try {
fis.close();
} catch (IOException e) {
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中",e);
}
}
}
Sheet sheet = getSheet(workbook, null);
FormulaEvaluator evaluator = getEvaluator(workbook);
List<String> headlist = getExcelHeadMenu(sheet, evaluator);
JsonObject keys = createKeysHeadRelations(headlist, s);
if(formate == null){
resultArray = exportListFromExcelArray(workbook, null, keys, timeIndex,null);
}else{
resultArray = exportListFromExcelArray(workbook, null, keys, formate, timeIndex,null);
}
}else{
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中");
return null;
}
return resultArray;
}
/**
*
* @Title: getExcelContent
* @Description: 根据列头值获取excel中文件内容
* @param filePath 文件实际路径
* @param sheetName excel标签页名称
* @param s excel标题头对应的变量名称
* @return JsonArray 返回类型 以key-value 数组形式返回， key为数据库中对应变量名称，value为String类型内容
*/
public JsonArray getExcelContent(String filePath, String sheetName, String [] s){
return getExcelContent(filePath, sheetName, s, null);
}
/**
*
* @Title: getExcelContent
* @Description: 根据列头值获取excel中文件内容
* @param filePath 文件实际路径
* @param sheetName excel标签页名称
* @param keys key-value形式。key为excel列头名称,value为该列对应数据中字段名称。
* @return JsonArray 返回类型 以key-value 数组形式返回， key为数据库中对应变量名称，value为String类型内容
*/
private JsonArray getExcelContent(String filePath, String sheetName, JsonObject keys){
File file = new File(filePath);
FileInputStream fis = null;
Workbook workbook = null;
JsonArray resultArray = null;
if(file.exists()){
String extensionName = getMemType(file.getName());
try {
fis = new FileInputStream(file);
} catch (FileNotFoundException e1) {
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中");
e1.printStackTrace();
return null;
}
try {
workbook = getWorkbookBySheetName(fis, extensionName, sheetName);
} catch (IOException e) {
LoggerUtil.error(this.getClass(), "excel 文件读取异常。捕获位置：getExcelContent 方法中");
e.printStackTrace();
return null;
}
resultArray = exportListFromExcelArray(workbook, sheetName, keys);
}else{
LoggerUtil.error(this.getClass(), "excel 文件不存在。捕获位置：getExcelContent 方法中");
return null;
}
return resultArray;
}
private Workbook getWorkbookBySheetName(InputStream is,
String extensionName, String sheetName) throws IOException {
Workbook workbook = null;
if (extensionName.toLowerCase().equals(XLS)) {
workbook = new HSSFWorkbook(is);
} else if (extensionName.toLowerCase().equals(XLSX)) {
workbook = new XSSFWorkbook(is);
}
return workbook;
}
/**
*
* @Title: getMemType
* @Description: 获取文件扩展类型
* @param fileName 文件名称全称
* @return String 扩展名称
*/
private String getMemType(String fileName){
return fileName.substring(fileName.lastIndexOf(".")+1,fileName.length());
}
private Sheet getSheet(Workbook workbook, String sheetName){
Sheet sheet = null;
if(sheetName == null || "".equals(sheetName)){
sheet = workbook.getSheetAt(0);
}else{
sheet = workbook.getSheet(sheetName);
}
return sheet;
}
private FormulaEvaluator getEvaluator(Workbook workbook){
return workbook.getCreationHelper().createFormulaEvaluator();
}
private JsonArray exportListFromExcelArray(Workbook workbook, String sheetName, JsonObject keys) {
// 解析公式结果
FormulaEvaluator evaluator = getEvaluator(workbook);
return exportListFromExcelArray(workbook, sheetName, keys, evaluator, null);
}
private JsonArray exportListFromExcelArray(Workbook workbook, String sheetName, JsonObject keys, JsonObject formate) {
// 解析公式结果
FormulaEvaluator evaluator = getEvaluator(workbook);
return exportListFromExcelArray(workbook, sheetName, keys, evaluator, formate);
}
private JsonArray exportListFromExcelArray(Workbook workbook, String sheetName, JsonObject keys, int [] timeIndex,String pattern) {
// 解析公式结果
FormulaEvaluator evaluator = getEvaluator(workbook);
return exportListFromExcelArray(workbook, sheetName, keys, evaluator, null,timeIndex, pattern);
}
private JsonArray exportListFromExcelArray(Workbook workbook, String sheetName, JsonObject keys, JsonObject formate, int [] timeIndex, String pattern) {
// 解析公式结果
FormulaEvaluator evaluator = getEvaluator(workbook);
return exportListFromExcelArray(workbook, sheetName, keys, evaluator, formate,timeIndex, pattern);
}
private JsonArray exportListFromExcelArray(Workbook workbook, String sheetName, JsonObject keys, FormulaEvaluator evaluator, JsonObject formate) {
Sheet sheet = getSheet(workbook, sheetName);
List<String> headList = getHeadMenu(sheet, keys, evaluator);
int minRowIx = sheet.getFirstRowNum();
int maxRowIx = sheet.getLastRowNum();
maxRowIx = sheet.getPhysicalNumberOfRows();
Row row = null;
int minColIx;
int maxColIx;
Cell cell = null;
CellValue cellValue = null;
String value = null;
String head = null;
JsonObject formatter = null;
JsonArray rowsData = new JsonArray();
for (int rowIx = minRowIx+1; rowIx < maxRowIx; rowIx++) {
row = sheet.getRow(rowIx);
minColIx = row.getFirstCellNum();
maxColIx = row.getLastCellNum();
JsonObject rowData = new JsonObject();
int headIndex = 0;
int isEmptyRow = 0;
int isEmptyCell = 1;
if(isEmptyRows(row, maxColIx)){
continue;
}
for (int colIx = minColIx; colIx < maxColIx; colIx++) {
cell = row.getCell(colIx);
if(cell == null){
value = "";
isEmptyCell = 0;
}else{
if(cell.getCellTypeEnum() == CellType.BLANK){
cell.setCellValue("");
isEmptyCell = 0;
}
cell.setCellType(CellType.STRING);
cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
continue;
}
value = cellValue.getStringValue();
}
head = headList.get(headIndex);
if(formate != null){
if(formate.has(head)){
formatter = formate.get(head).getAsJsonObject();
if(formatter.has(value)){
value = formatter.get(value).getAsString();
}
}
}
rowData.addProperty(head, value);
isEmptyRow += isEmptyCell;
headIndex++;
}
if(rowData.size() > 0 && isEmptyRow !=0){
rowsData.add(rowData);
}
}
return rowsData;
}
private JsonArray exportListFromExcelArray(Workbook workbook, String sheetName, JsonObject keys, FormulaEvaluator evaluator, JsonObject formate, int[] timeIndex,String pattern) {
Sheet sheet = getSheet(workbook, sheetName);
List<String> headList = getHeadMenu(sheet, keys, evaluator);
String patte = null;
if(pattern == null){
patte = "yyyy-MM";
}else{
patte = pattern;
}
SimpleDateFormat dateFormate = new SimpleDateFormat(patte);
int minRowIx = sheet.getFirstRowNum();
int maxRowIx = sheet.getLastRowNum();
maxRowIx = sheet.getPhysicalNumberOfRows();
Row row = null;
int minColIx;
int maxColIx;
Cell cell = null;
CellValue cellValue = null;
String value = null;
String head = null;
JsonObject formatter = null;
JsonArray rowsData = new JsonArray();
for (int rowIx = minRowIx+1; rowIx < maxRowIx; rowIx++) {
row = sheet.getRow(rowIx);
if(row == null) continue;
minColIx = row.getFirstCellNum();
maxColIx = row.getLastCellNum();
JsonObject rowData = new JsonObject();
int headIndex = 0;
int isEmptyRow = 0;
int isEmptyCell = 1;
if(isEmptyRows(row, maxColIx)){
continue;
}
for (int colIx = minColIx; colIx < maxColIx; colIx++) {
cell = row.getCell(colIx);
if(cell == null){
value = "";
isEmptyCell = 0;
}else{
cell.setCellType(CellType.STRING);
if(cell.getCellTypeEnum() == CellType.BLANK){
cell.setCellValue("");
isEmptyCell = 0;
value = "";
}else if(ArraysTools.contains(timeIndex, colIx)){
if(cell.getCellTypeEnum() == CellType.NUMERIC) {
value = dateFormate.format(cell.getDateCellValue());
}else if(cell.getCellTypeEnum() == CellType.STRING) {
value = cell.getStringCellValue();
}
}else{
cell.setCellType(CellType.STRING);
cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
continue;
}
value = cellValue.getStringValue();
}
}
head = headList.get(headIndex);
if(formate != null){
if(formate.has(head)){
formatter = formate.get(head).getAsJsonObject();
if(formatter.has(value)){
value = formatter.get(value).getAsString();
}
}
}
rowData.addProperty(head, value);
isEmptyRow += isEmptyCell;
headIndex++;
}
if(rowData.size() > 0 && isEmptyRow !=0){
rowsData.add(rowData);
}
}
return rowsData;
}
private List<String> getHeadMenu(Sheet sheet, JsonObject keys, FormulaEvaluator evaluator){
Row row = sheet.getRow(0);
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
Cell cell = null;
CellValue cellValue = null;
String value = null;
List<String> headList = new ArrayList<String>();
for (int colIx = minColIx; colIx < maxColIx; colIx++){
cell = row.getCell(colIx);
cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
continue;
}
value = cellValue.getStringValue();
headList.add(keys.get(value).getAsString());
}
return headList;
}
/**
*
* @Title: getExcelHeadMenu
* @Description: 获取excel中标题列内容
* @param sheet 标签页名称。 为null默认取第一个标签页
* @param evaluator excel解析器
* @return List<String> 返回类型
*/
public List<String> getExcelHeadMenu(Sheet sheet,FormulaEvaluator evaluator){
Row row = sheet.getRow(0);
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
Cell cell = null;
CellValue cellValue = null;
String value = null;
List<String> headList = new ArrayList<String>();
for (int colIx = minColIx; colIx <= maxColIx; colIx++){
cell = row.getCell(colIx);
cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
continue;
}
value = cellValue.getStringValue();
headList.add(value);
}
return headList;
}
public static List<String> getExcelHeadMenu(Sheet sheet,FormulaEvaluator evaluator, int headRow){
Row row = sheet.getRow(headRow);
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
Cell cell = null;
CellValue cellValue = null;
String value = null;
List<String> headList = new ArrayList<String>();
for (int colIx = minColIx; colIx <= maxColIx; colIx++){
cell = row.getCell(colIx);
cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
continue;
}
value = cellValue.getStringValue();
headList.add(value);
}
return headList;
}
public JsonObject createHeadKeysMapping(String[] keys,String[] excelHead){
int len = keys.length;
JsonObject result = new JsonObject();
for (int i = 0; i < len; i++) {
result.addProperty(keys[i], excelHead[i]);
}
return result;
}
public Workbook getWrokBook(String fileType){
Workbook wb = null;
if (XLS.equals(fileType)) {
wb = new HSSFWorkbook();
} else if(XLSX.equals(fileType)) {
wb = new XSSFWorkbook();
} else {
LoggerUtil.error(this.getClass(), "文件格式不正确");
}
return wb;
}
/**
*
* @Title: write
* @Description: 根据数据内容生成excel文件
* @param wb excel工作薄
* @param keys 变量字符串数组
* @param sheetName 标签页名称
* @param excelHead 变量字符串对应的excel标题头数组
* @param list 数据
* @param headStyle 标题头样式 可为null
* @param cellStyle 文件内容 样式可为null
* @return Workbook 返回类型
*/
public Workbook write(Workbook wb , String[] keys,String sheetName, String[] excelHead,List<?> list, CellStyle headStyle,CellStyle cellStyle){
Sheet sheet =null;
if(sheetName == null || "".equals(sheetName)){
sheetName = "sheet1";
}
sheet = wb.createSheet(sheetName);
//列宽
for (int i=0;i<excelHead.length;i++) {
sheet.autoSizeColumn(i);
int length1 = sheet.getColumnWidth(i);
int length2 = excelHead[i].length();
int columnWidth = length1 > length2 ? length1 : length2;
sheet.setColumnWidth(i, columnWidth*3);
}
// 生成字段与excel表头对应关系
JsonObject keysMappings = createHeadKeysMapping(keys, excelHead);
// excel写入标题头
writeHead(sheet, keys, keysMappings, cellStyle);
// excel写入内容
writeContent(list, sheet, keys, headStyle);
return wb;
}
/**
*
* @Title: write
* @Description: 根据数据内容生成excel文件
* @param wb excel工作薄
* @param keys 变量字符串数组
* @param sheetName 标签页名称
* @param excelHead 变量字符串对应的excel标题头数组
* @param list 数据
* @param headStyle 标题头样式 可为null
* @param cellStyle 文件内容 样式可为null
* @return Workbook 返回类型
*/
public void writeSheet(Workbook wb , String[] keys,String sheetName, String[] excelHead,JsonArray list, CellStyle headStyle,CellStyle cellStyle){
Sheet sheet =null;
if(sheetName == null || "".equals(sheetName)){
sheetName = "sheet1";
}
sheet = wb.createSheet(sheetName);
//设置表格默认宽度
//列宽
for (int i=0;i<excelHead.length;i++) {
sheet.autoSizeColumn(i);
int length1 = sheet.getColumnWidth(i);
int length2 = excelHead[i].length();
int columnWidth = length1 > length2 ? length1 : length2;
sheet.setColumnWidth(i, columnWidth*3);
}
// 生成字段与excel表头对应关系
JsonObject keysMappings = createHeadKeysMapping(keys, excelHead);
// excel写入标题头
writeHead(sheet, keys, keysMappings, cellStyle);
// excel写入内容
writeContent(list, sheet, keys, headStyle);
}
public Workbook writeMultSheet(Workbook wb , List<String[]> keyList,List<String> sheetNameList, List<String[]> excelHeadList,List<List<?>> listAll, CellStyle headStyle,CellStyle cellStyle){
int size = sheetNameList.size();
for (int j = 0; j < size; j++) {
Sheet sheet =null;
String sheetName = sheetNameList.get(j);
if(StringUtils.isEmpty(sheetName)){
sheetName = "sheet1";
}
sheet = wb.createSheet(sheetName);
String [] excelHead = excelHeadList.get(j);
//列宽
for (int i=0;i<excelHead.length;i++) {
sheet.autoSizeColumn(i);
int length1 = sheet.getColumnWidth(i);
int length2 = excelHead[i].length();
int columnWidth = length1 > length2 ? length1 : length2;
sheet.setColumnWidth(i, columnWidth*3);
}
String [] keys = keyList.get(j);
// 生成字段与excel表头对应关系
JsonObject keysMappings = createHeadKeysMapping(keys, excelHead);
// excel写入标题头
writeHead(sheet, keys, keysMappings, cellStyle);
List<?> list = listAll.get(j);
// excel写入内容
writeContent(list, sheet, keys, headStyle);
}
return wb;
}
public Workbook writeMultSheet(Workbook wb , List<String[]> keyList,List<String> sheetNameList, List<String[]> excelHeadList,List<List<?>> listAll, CellStyle headStyle,CellStyle cellStyle,JsonObject formate ) throws Exception{
int size = sheetNameList.size();
for (int j = 0; j < size; j++) {
Sheet sheet =null;
String sheetName = sheetNameList.get(j);
if(StringUtils.isEmpty(sheetName)){
sheetName = "sheet1";
}
sheet = wb.createSheet(sheetName);
String [] excelHead = excelHeadList.get(j);
//列宽
for (int i=0;i<excelHead.length;i++) {
sheet.autoSizeColumn(i);
int length1 = sheet.getColumnWidth(i);
int length2 = excelHead[i].length();
int columnWidth = length1 > length2 ? length1 : length2;
sheet.setColumnWidth(i, columnWidth*3);
}
String [] keys = keyList.get(j);
// 生成字段与excel表头对应关系
JsonObject keysMappings = createHeadKeysMapping(keys, excelHead);
// excel写入标题头
writeHead(sheet, keys, keysMappings, cellStyle);
List<?> list = listAll.get(j);
// excel写入内容
writeContent(list, sheet, keys, headStyle, formate);
}
return wb;
}
public Workbook write(Workbook wb , String[] keys,String sheetName, String[] excelHead,JsonArray list, CellStyle headStyle,CellStyle cellStyle){
Sheet sheet =null;
if(sheetName == null || "".equals(sheetName)){
sheetName = "sheet1";
}
sheet = wb.createSheet(sheetName);
//设置表格默认宽度
//列宽
for (int i=0;i<excelHead.length;i++) {
sheet.autoSizeColumn(i);
int length1 = sheet.getColumnWidth(i);
int length2 = excelHead[i].length();
int columnWidth = length1 > length2 ? length1 : length2;
sheet.setColumnWidth(i, columnWidth*3);
}
// 生成字段与excel表头对应关系
JsonObject keysMappings = createHeadKeysMapping(keys, excelHead);
// excel写入标题头
writeHead(sheet, keys, keysMappings, cellStyle);
// excel写入内容
writeContent(list, sheet, keys, headStyle);
return wb;
}
/**
*
* @Title: write
* @Description: 根据数据内容生成excel文件
* @param wb excel工作薄
* @param keys 变量字符串数组
* @param sheetName 标签页名称
* @param excelHead 变量字符串对应的excel标题头数组
* @param list 数据
* @param headStyle 标题头样式 可为null
* @param cellStyle 文件内容 样式可为null
* @param formate 格式化列内容，例如：{"fieldName1":{"1":"intel","2":"amd"},"fieldName2":{"1":"黑","2":"白","3":"灰"}}
* @return Workbook 返回类型
* @throws Exception
*/
public Workbook write(Workbook wb , String[] keys,String sheetName, String[] excelHead,List<?> list, CellStyle headStyle,CellStyle cellStyle, JsonObject formate) throws Exception{
Sheet sheet =null;
if(sheetName == null || "".equals(sheetName)){
sheetName = "sheet1";
}
sheet = wb.createSheet(sheetName);
//列宽
for (int i=0;i<excelHead.length;i++) {
sheet.setColumnWidth(i, excelHead[i].length()*2000);
}
// 生成字段与excel表头对应关系
JsonObject keysMappings = createHeadKeysMapping(keys, excelHead);
// excel写入标题头
writeHead(sheet, keys, keysMappings, cellStyle);
// excel写入内容
writeContent(list, sheet, keys, headStyle, formate);
return wb;
}
private void writeHead(Sheet sheet, String[] keys, JsonObject keysMapping){
writeHead(sheet, keys, keysMapping, null);
}
private void writeHead(Sheet sheet, String[] keys, JsonObject keysMapping, CellStyle style){
Row row = sheet.createRow(0);
row.setHeightInPoints(40);//表头行高
int length = keys.length;
Cell cell = null;
for (int i = 0; i < length; i++) {
cell = row.createCell(i);
if(style != null){
cell.setCellStyle(style);
}
cell.setCellValue(keysMapping.get(keys[i]).getAsString());
}
}
private void writeContent(List<?> list, Sheet sheet, String[] keys,CellStyle style){
if(list == null) return;
JsonArray ja = gson.toJsonTree(list).getAsJsonArray();
int size = ja.size();
int keyLen = keys.length;
JsonObject jo = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < size; i++) {
row = sheet.createRow(i + 1);
jo = ja.get(i).getAsJsonObject();
for (int j = 0; j < keyLen; j++) {
cell = row.createCell(j);
if(style != null){
cell.setCellStyle(style);
}
cell.setCellType(CellType.STRING);
if(jo.has(keys[j])){
cell.setCellValue(jo.get(keys[j]).getAsString());
}else{
cell.setCellValue("");
}
}
}
}
private void writeContentWD(List<String []> list, Sheet sheet,CellStyle style,String[] excelHead)
{
{
if(list == null) return;
JsonArray ja = gson.toJsonTree(list).getAsJsonArray();
int size = ja.size();
int keyLen = excelHead.length;
JsonObject jo = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < size; i++) {
row = sheet.createRow(i + 1);
for (int j = 0; j < keyLen; j++) {
cell = row.createCell(j);
if(style != null){
cell.setCellStyle(style);
}
cell.setCellType(CellType.STRING);
cell.setCellValue(list.get(i)[j]);
}
}
}
}
private void writeContentCard(List<String []> list, Sheet sheet,CellStyle style,String[] excelHead){
if(list == null) return;
JsonArray ja = gson.toJsonTree(list).getAsJsonArray();
int size = ja.size();
int keyLen = excelHead.length+1;
JsonObject jo = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < size; i++) {
row = sheet.createRow(i + 6);
for (int j = 0; j < keyLen; j++) {
cell = row.createCell(j);
if(style != null){
cell.setCellStyle(style);
}
cell.setCellType(CellType.STRING);
cell.setCellValue(list.get(i)[j]);
}
}
}
private void writeContent(JsonArray ja, Sheet sheet, String[] keys,CellStyle style){
if(ja == null) return;
int size = ja.size();
int keyLen = keys.length;
JsonObject jo = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < size; i++) {
row = sheet.createRow(i + 1);
jo = ja.get(i).getAsJsonObject();
for (int j = 0; j < keyLen; j++) {
cell = row.createCell(j);
if(style != null){
cell.setCellStyle(style);
}
cell.setCellType(CellType.STRING);
if(jo.has(keys[j])){
if(jo.get(keys[j]).isJsonNull()) {
cell.setCellValue("");
}else {
cell.setCellValue(jo.get(keys[j]).getAsString());
}
}else{
cell.setCellValue("");
}
}
}
}
public static void writeDiskExcelContent(JsonArray ja, Sheet sheet, String[] keys){
writeDiskExcelContent(ja, sheet, keys, 1);
}
public static void writeDiskExcelContent(JsonArray ja, Sheet sheet, String[] keys,int rowStart){
writeDiskExcelContent(ja, sheet, keys, rowStart,null);
}
public static void writeDiskExcelContent(JsonArray ja, Sheet sheet, String[] keys,int rowStart,CellStyle cs){
if(ja == null) return;
int size = ja.size();
int keyLen = keys.length;
JsonObject jo = null;
Row row = null;
Cell cell = null;
for (int i = 0; i < size; i++) {
//row = sheet.getRow(i + 1);
//if(row == null) {
row = sheet.createRow(i+rowStart);
//}
jo = ja.get(i).getAsJsonObject();
for (int j = 0; j < keyLen; j++) {
cell = row.getCell(j);
if(cell == null) {
cell = row.createCell(j);
}
if(cs != null) {
cell.setCellStyle(cs);
}
if(keys[j].contains(".")) {
String[] tempKeys = keys[j].split("\\.");
String val = getObjectFieldValue(tempKeys,jo);
cell.setCellValue(val);
}else {
if(jo.has(keys[j])){
if(jo.get(keys[j]).isJsonNull()) {
cell.setCellValue("");
}else {
cell.setCellValue(jo.get(keys[j]).getAsString());
}
}else{
cell.setCellValue("");
}
}
}
}
}
private static String getObjectFieldValue(String[] fields,JsonObject jo) {
JsonObject temp = null;
for (int k = 0; k < fields.length; k++) {
if(temp == null) {
temp = jo;
}
JsonElement je = temp.get(fields[k]);
if(je.isJsonObject()) {
temp = je.getAsJsonObject();
continue;
}else if(je.isJsonNull()){
return "";
}else if(je.isJsonPrimitive()) {
return je.getAsString();
}
}
return "";
}
public static void writeDiskExcelContent(JsonArray ja, Sheet sheet, String[] keys,int rowStart,CellStyle cs, JsonObject formate) {
if(ja == null) return;
int size = ja.size();
int keyLen = keys.length;
JsonObject jo = null;
Row row = null;
Cell cell = null;
int isDate = 0;
String formatter = null;
Date formateDate = null;
String filedName = null;
String value = null;
JsonObject temp = null;
for (int i = 0; i < size; i++) {
row = sheet.createRow(i+rowStart);
jo = ja.get(i).getAsJsonObject();
for (int j = 0; j < keyLen; j++) {
cell = row.getCell(j);
if(cell == null) {
cell = row.createCell(j);
}
if(cs != null) {
cell.setCellStyle(cs);
}
if(keys[j].contains(".")) {
String[] tempKeys = keys[j].split("\\.");
String val = getObjectFieldValue(tempKeys,jo);
cell.setCellValue(val);
}else {
if(jo.has(keys[j])){
if(jo.get(keys[j]).isJsonNull()) {
cell.setCellValue("");
}else {
filedName=keys[j];
value = jo.get(filedName).getAsString();
if(formate.has(filedName)){
temp = formate.get(filedName).getAsJsonObject();
if(temp.has(value)){
value = temp.get(value).getAsString();
}else if("-1".equals(value)){
value="";
}
if(temp.has("isDate") && StringTools.isNotEmpty(value)) {
isDate = temp.get("isDate").getAsInt();
if(isDate == 1) {
if(temp.has("formatter")) {
formatter = temp.get("formatter").getAsString();
try {
formateDate = new SimpleDateFormat(formatter).parse(value);
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
value = new SimpleDateFormat(formatter).format(formateDate);
}
}
}
}
}
cell.setCellValue(value);
}else{
cell.setCellValue("");
}
}
}
}
}
private void writeContent(List<?> list, Sheet sheet, String[] keys,CellStyle style, JsonObject formate,int rowStart) throws Exception{
JsonArray ja = gson.toJsonTree(list).getAsJsonArray();
int size = ja.size();
int keyLen = keys.length;
JsonObject jo = null;
Row row = null;
Cell cell = null;
String filedName = null;
String value = null;
JsonObject temp = null;
int isDate = 0;
String formatter = null;
Date formateDate = null;
for (int i = 0; i < size; i++) {
// row = sheet.createRow(i + 1);
//row = sheet.getRow(i + 1);
//if(row == null) {
row = sheet.createRow(i+rowStart);
//}
jo = ja.get(i).getAsJsonObject();
for (int j = 0; j < keyLen; j++) {
cell = row.createCell(j);
if(style != null){
cell.setCellStyle(style);
}
cell.setCellType(CellType.STRING);
filedName = keys[j];
if(jo.has(filedName)){
value = jo.get(filedName).getAsString();
if(formate.has(filedName)){
temp = formate.get(filedName).getAsJsonObject();
if(temp.has(value)){
value = temp.get(value).getAsString();
}else if("-1".equals(value)){
value="";
}
if(temp.has("isDate") && StringTools.isNotEmpty(value)) {
isDate = temp.get("isDate").getAsInt();
if(isDate == 1) {
if(temp.has("formatter")) {
formatter = temp.get("formatter").getAsString();
formateDate = new SimpleDateFormat(formatter).parse(value);
value = new SimpleDateFormat(formatter).format(formateDate);
}
}
}
}
}else{
value = "";
}
cell.setCellValue(value);
}
}
}
private void writeContent(List<?> list, Sheet sheet, String[] keys,CellStyle style, JsonObject formate) throws Exception{
JsonArray ja = gson.toJsonTree(list).getAsJsonArray();
int size = ja.size();
int keyLen = keys.length;
JsonObject jo = null;
Row row = null;
Cell cell = null;
String filedName = null;
String value = null;
JsonObject temp = null;
int isDate = 0;
String formatter = null;
Date formateDate = null;
for (int i = 0; i < size; i++) {
row = sheet.createRow(i + 1);
jo = ja.get(i).getAsJsonObject();
for (int j = 0; j < keyLen; j++) {
cell = row.createCell(j);
if(style != null){
cell.setCellStyle(style);
}
cell.setCellType(CellType.STRING);
filedName = keys[j];
if(jo.has(filedName)){
value = jo.get(filedName).getAsString();
if(formate.has(filedName)){
temp = formate.get(filedName).getAsJsonObject();
if(temp.has(value)){
value = temp.get(value).getAsString();
}else if("-1".equals(value)){
value="";
}
if(temp.has("isDate") && StringTools.isNotEmpty(value)) {
isDate = temp.get("isDate").getAsInt();
if(isDate == 1) {
if(temp.has("formatter")) {
formatter = temp.get("formatter").getAsString();
formateDate = new SimpleDateFormat(formatter).parse(value);
value = new SimpleDateFormat(formatter).format(formateDate);
}
}
}
}
}else{
value = "";
}
cell.setCellValue(value);
}
}
}
private void writeContent(List<?> list, Sheet sheet, String[] keys){
writeContent(list, sheet, keys, null);
}
public static boolean isEmptyRows(Row row,int cellNum){
if(row == null) return true;
if(row.getFirstCellNum() == -1) return true;
Cell cell = null;
for (int i = 0; i < cellNum; i++) {
cell = row.getCell(i);
if(cell != null){
if(cell != null && cell.getCellTypeEnum() != CellType.BLANK){
return false;
}
}
}
return true;
}
public String trim(String str) {
if (str == "" || str == null) {
return "";
}
int len = str.length();
int st = 0;
char[] val = str.toCharArray();
while ((st < len) && (val[st] == 160)) {
st++;
}
while ((st < len) && (val[len - 1] == 160)) {
len--;
}
return ((st > 0) || (len < str.length())) ? str.substring(st, len) : str;
}
/**
*
* @Title: getExcelColumnPosition
* @Description: 获取excel 列位置
* @param @param i 必须大于 0
* @return void 返回类型
*/
public String getExcelColumnPosition(int i,int j){
if(i <= 0) return null;
String temp = "";
String hightLitter = "";
if( i > 26){
int m = i / 26;
int s = i % 26;
if(s == 0) m = m-1;
if(m > 0){
hightLitter = (char)(64 + m) + "";
}
if(s == 0) s = 26;
s += 64;
temp = (char)s + "";
}else{
int n = 64 + i;
temp = (char) n + "";
}
return hightLitter + temp + j;
}
public synchronized static List<List<String>> readXlsb(String xlsbFileName) {
OPCPackage pkg;
try {
pkg = OPCPackage.open(xlsbFileName);
XSSFBReader r = new XSSFBReader(pkg);
XSSFBSharedStringsTable sst = new XSSFBSharedStringsTable(pkg);
XSSFBStylesTable xssfbStylesTable = r.getXSSFBStylesTable();
XSSFBReader.SheetIterator it = (XSSFBReader.SheetIterator) r.getSheetsData();
InputStream is = it.next();
String name = it.getSheetName();
XLSB2Lists testSheetHandler = new XLSB2Lists();
testSheetHandler.startSheet(name);
XSSFBSheetHandler sheetHandler = new XSSFBSheetHandler(is, xssfbStylesTable,
it.getXSSFBSheetComments(),
sst, testSheetHandler,
new DataFormatter(),
false);
sheetHandler.parse();
// sheet content
List list1 = testSheetHandler.getSheetContentAsList();
is.close();
pkg.close();
return list1;
} catch (InvalidFormatException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
} catch (IOException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
} catch (OpenXML4JException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
} catch (SAXException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
}finally {
}
return null;
}
public synchronized static List<List<List<String>>> readAllXlsb(String xlsbFileName) {
OPCPackage pkg;
try {
pkg = OPCPackage.open(xlsbFileName);
XSSFBReader r = new XSSFBReader(pkg);
XSSFBSharedStringsTable sst = new XSSFBSharedStringsTable(pkg);
XSSFBStylesTable xssfbStylesTable = r.getXSSFBStylesTable();
List<List<List<String>>> resultList = new Vector<>();
XSSFBReader.SheetIterator it = (XSSFBReader.SheetIterator) r.getSheetsData();
while(it.hasNext()){XLSB2Lists testSheetHandler = new XLSB2Lists();
InputStream is = it.next();
testSheetHandler.startSheet(it.getSheetName());
XSSFBSheetHandler sheetHandler = new XSSFBSheetHandler(is, xssfbStylesTable,
it.getXSSFBSheetComments(),
sst, testSheetHandler,
new DataFormatter(),
false);
sheetHandler.parse();
// sheet content
List list1 = testSheetHandler.getSheetContentAsList();
resultList.add(list1);
}
pkg.close();
return resultList;
} catch (InvalidFormatException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
} catch (IOException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
} catch (OpenXML4JException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
} catch (SAXException e) {
LoggerUtil.error(OmpFindDataController.class, "读取XLSB error:"+xlsbFileName, e);
}finally {
}
return null;
}
public static CellStyle[] getExportWorkBookStyle(Workbook workbook){
if(workbook == null){
return null;
}
CellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(IndexedColors.TAN.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
// 生成一个字体
Font font = workbook.createFont();
font.setColor(IndexedColors.VIOLET.getIndex());
font.setFontHeightInPoints((short) 12);
font.setBold(true);// 加粗
// 把字体应用到当前的样式
style.setFont(font);
// 生成并设置另一个样式
CellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style2.setBorderBottom(BorderStyle.THIN);
style2.setBorderLeft(BorderStyle.THIN);
style2.setBorderRight(BorderStyle.THIN);
style2.setBorderTop(BorderStyle.THIN);
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setVerticalAlignment(VerticalAlignment.CENTER);
// 生成另一个字体
Font font2 = workbook.createFont();
font2.setBold(false);// 正常
return new CellStyle[]{style,style2};
}
}
2、实际读写应用
import com.chinasoft.util.ExcelUtil;
import com.chinasoft.util.XLSXCovertCSVReader;
import com.google.gson.Gson;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class Test {
/**
* 读取excel文件数据
*
* @param fileName
* @param request
* @param response
*/
public void readeExcel(String fileName, HttpServletRequest request, HttpServletResponse response) {
// 获取文件
String tempPath = request.getServletContext().getRealPath("/");
String savePath = tempPath + "upload" + "/" + fileName;
// 读取excel
try {
// 读取指定sheet
List<String[]> xlsxList = XLSXCovertCSVReader.readerExcel(savePath, "sheetName", 5);
// 读取所有sheet
List<List<String[]>> lists = XLSXCovertCSVReader.readerAllSheetExcel(savePath);
/*
对读取的数据进行业务处理
.
.
.
*/
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 写excel，通过XSSFWorkbook写，小数据量可以，大数据量会导致内存溢出问题OOM
*
* @param request
* @param response
*/
public void writeExcelByXSSFWorkbook(HttpServletRequest request, HttpServletResponse response) {
// 模拟查询到的excel表格数据
List<ExcelEntity> list = new ArrayList<>();
for (int i = 1; i < 11; i++) {
ExcelEntity excelEntity = new ExcelEntity();
excelEntity.setId(i);
excelEntity.setName("张三" + i);
excelEntity.setGender("男");
excelEntity.setAge("18" + i + "岁");
list.add(excelEntity);
}
OutputStream ouputStream = null;
Workbook write = null;
try {
// 表名
String fileName = new String(("人员信息_" + new SimpleDateFormat("yyyy-MM-dd-HH").format(new Date())).getBytes(),
"UTF-8");
// 表头
String [] headers = new String[] {"序号", "姓名", "性别", "年龄"};
String[] keys = new String[] {"id", "name", "gender", "age"};
// sheet名
String title = "人员信息";
// 声明一个工作薄
Workbook workbook = new XSSFWorkbook();
CellStyle headStyle = createHeadStyle(workbook);
CellStyle cellStyle = createCellStyle(workbook);
// 写入excel
ExcelUtil excelTools = new ExcelUtil();
write = excelTools.write(workbook, keys, title, headers, list, cellStyle, headStyle);
// 通过浏览器下载
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition",
"attachment;filename=" + URLEncoder.encode(fileName, "utf-8") + ".xlsx");
ouputStream = response.getOutputStream();
write.write(ouputStream);
ouputStream.flush();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} finally {
if(ouputStream != null) {
try {
ouputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(write != null) {
try {
write.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* 写excel，通过SXSSFWorkbook写，将内存中的数据持久化至磁盘，避免OOM问题
*
* @param request
* @param response
*/
public void writeExcelBySXSSFWorkbook(HttpServletRequest request, HttpServletResponse response) {
Gson gson = new Gson();
SXSSFWorkbook wb = null;
OutputStream ouputStream = null;
// 模拟查询到的excel表格数据
List<ExcelEntity> list = new ArrayList<>();
for (int i = 1; i < 11; i++) {
ExcelEntity excelEntity = new ExcelEntity();
excelEntity.setId(i);
excelEntity.setName("张三" + i);
excelEntity.setGender("男");
excelEntity.setAge("18" + i + "岁");
list.add(excelEntity);
}
try {
// 表名
String fileName = new String(("人员信息_" + new SimpleDateFormat("yyyy-MM-dd-HH").format(new Date())).getBytes(),
"UTF-8");
// 表头
String [] headers = new String[] {"序号", "姓名", "性别", "年龄"};
String[] keys = new String[] {"id", "name", "gender", "age"};
// sheet名
String title = "人员信息";
wb = new SXSSFWorkbook(-1);
// 创建sheet
SXSSFSheet sheet = wb.createSheet(title);
SXSSFRow row = sheet.createRow(0);
CellStyle headStyle = createHeadStyle(wb);
CellStyle cellStyle = createCellStyle(wb);
for(int i = 0;i < headers.length; i++) {
SXSSFCell cell = row.createCell(i);
cell.setCellStyle(headStyle);
cell.setCellValue(headers[i]);
}
// 写入到磁盘
ExcelUtil.writeDiskExcelContent(gson.toJsonTree(list).getAsJsonArray(), sheet, keys, 1, cellStyle);
// 以流的形式下载文件。
response.reset();
response.setContentType(request.getSession().getServletContext().getMimeType(fileName));
response.setHeader("Content-Disposition", "attachment;filename="+fileName);
ouputStream = response.getOutputStream();
wb.write(ouputStream);
ouputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
if(ouputStream != null) {
try {
ouputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
if(wb != null) {
try {
wb.dispose();
wb.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
@Data
class ExcelEntity {
private int id;
private String name;
private String gender;
private String age;
}
/**
* 创建导出表的表头样式
*
* @param workbook Workbook
* @return CellStyle
*/
public static CellStyle createHeadStyle(Workbook workbook) {
// 生成一个表格
/* // 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);*/
// 生成一个样式
CellStyle headStyle = workbook.createCellStyle();
// 设置这些样式
headStyle.setFillForegroundColor(IndexedColors.TAN.getIndex());
headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
headStyle.setBorderBottom(BorderStyle.THIN);
headStyle.setBorderLeft(BorderStyle.THIN);
headStyle.setBorderRight(BorderStyle.THIN);
headStyle.setBorderTop(BorderStyle.THIN);
headStyle.setAlignment(HorizontalAlignment.CENTER);
headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
// 生成一个字体
Font font = workbook.createFont();
font.setColor(IndexedColors.VIOLET.getIndex());
font.setFontHeightInPoints((short) 12);
font.setBold(true);// 加粗
// 把字体应用到当前的样式
headStyle.setFont(font);
return headStyle;
}
/**
* 创建导出表的单元格样式
*
* @param workbook Workbook
* @return CellStyle
*/
public static CellStyle createCellStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(false);
cellStyle.setFont(font);
return cellStyle;
}
}
7123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
