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
*
* Also see Standard ECMA-376, 1st edition, part 4, pages 1928ff, at
* http://www.ecma-international.org/publications/standards/Ecma-376.htm
*
* 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
private List
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
return rows;
}
public void setRows(List
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
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
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
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
List
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
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
List
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
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
List
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
List
if(list != null && list.size() >0){
result.add(list);
}
stream.close();
}
return result;
}
public List
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
List
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
List
if(list != null && list.size() >0){
result.add(list);
}
stream.close();
}
return result;
}
public Map
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
Map
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetName = iter.getSheetName();
List
if(list != null && list.size() >0){
result.put(sheetName,list);
}
stream.close();
}
return result;
}
public Map
ParserConfigurationException, SAXException {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(
this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
SharedStringsTable sst = xssfReader.getSharedStringsTable();
Map
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader
.getSheetsData();
while (iter.hasNext()) {
InputStream stream = iter.next();
String sheetName = iter.getSheetName();
List
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
int minColumns) throws Exception {
return readerExcel(path, sheetName, minColumns,0);
}
public static List
int minColumns,int avalibleColumns) throws Exception {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
sheetName, minColumns, avalibleColumns);
List
p.close();
return list;
}
public static List
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerExcel(path, sheetName, minColumns,0);
}
public static List
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
p.close();
return list;
}
public static List
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerAllSameSheetExcel(path, minColumns,0);
}
public static List
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, minColumns,avalibleColumns);
List
p.close();
return list;
}
public static List
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
List
p.close();
return list;
}
public static Map
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path, PackageAccess.READ);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
Map
p.close();
return list;
}
public static List
ParserConfigurationException, SAXException {
return readerAllSheetExcel(path,0);
}
public static Map
ParserConfigurationException, SAXException {
return readerAllSheetExcelByMap(path,0);
}
public static List
ParserConfigurationException, SAXException {
return readerAllSheetExcel(path,0);
}
public static Map
ParserConfigurationException, SAXException {
return readerAllSheetExcelByMap(path,0);
}
public static List
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
List
p.close();
return list;
}
public static Map
ParserConfigurationException, SAXException {
OPCPackage p = OPCPackage.open(path);
XLSXCovertCSVReader xlsx2csv = new XLSXCovertCSVReader(p, System.out,
-1, 0,avalibleColumns);
Map
p.close();
return list;
}
public static List
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerExcel(path, sheetName, minColumns,0);
}
public static List
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
p.close();
return list;
}
public static List
int minColumns) throws IOException, OpenXML4JException,
ParserConfigurationException, SAXException {
return readerExcel(path, sheetName, minColumns,0);
}
public static List
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
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
*/
public List
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
*/
public List
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
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
List
return createKeysHeadRelations(headList, keyList);
}
public static JsonObject createKeysHeadRelations(String[] headArray, String[] keyArray){
List
List
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
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
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
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
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 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
Row row = sheet.getRow(0);
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
Cell cell = null;
CellValue cellValue = null;
String value = null;
List
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
*/
public List
Row row = sheet.getRow(0);
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
Cell cell = null;
CellValue cellValue = null;
String value = null;
List
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
Row row = sheet.getRow(headRow);
int minColIx = row.getFirstCellNum();
int maxColIx = row.getLastCellNum();
Cell cell = null;
CellValue cellValue = null;
String value = null;
List
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 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 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 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 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 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 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 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 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 { { 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 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 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 OPCPackage pkg; try { pkg = OPCPackage.open(xlsbFileName); XSSFBReader r = new XSSFBReader(pkg); XSSFBSharedStringsTable sst = new XSSFBSharedStringsTable(pkg); XSSFBStylesTable xssfbStylesTable = r.getXSSFBStylesTable(); List 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 // 读取所有sheet List /* 对读取的数据进行业务处理 . . . */ } catch (Exception e) { e.printStackTrace(); } } /** * 写excel，通过XSSFWorkbook写，小数据量可以，大数据量会导致内存溢出问题OOM * * @param request * @param response */ public void writeExcelByXSSFWorkbook(HttpServletRequest request, HttpServletResponse response) { // 模拟查询到的excel表格数据 List 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 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; } } 好文链接
发表评论