import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanMap;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
* @author songfeng
*
*/
public final class ExcelUtil {
private static int SHEET_SPLIT_NUM = 20000;
public static void main(String[] args) {
List<Map> list = new ArrayList<Map>();
for(int i=0 ; i < 20000; i++)
{
Map map = new HashMap<>();
map.put("a", "111");
map.put("b", "222");
map.put("c", "333");
map.put("d", "444");
map.put("e", "555");
map.put("f", "666");
map.put("g", "777");
map.put("h", "888");
map.put("i", "999");
map.put("j", "000");
map.put("k", "!!!");
map.put("l", "@@@");
map.put("m", "###");
map.put("n", "$$$");
list.add(map);
}
String[] title = "A,B,C,D,E,F,G,H,I,J,K,L,M,N".split(",");
String[] column = "a,b,c,d,e,f,g,h,i,j,k,l,m,n".split(",");
try {
OutputStream os = new FileOutputStream("D:\\test.xlsx");
Long startTime = System.currentTimeMillis();
ExcelUtil.write(os, title, column, list,new CellStyleInterface(){
@Override
public void dealWithCell(Cell cell, CellStyle cellStyle, DataFormat dataFormat, String name, String value) {
if("skuExpireTime".equals(name))
{
//set date format
//cellStyle.setDataFormat(dataFormat.getFormat("yyyy-mm-dd"));
//设置单元格格式为"文本"
cellStyle.setDataFormat(dataFormat.getFormat("@"));
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
}
}
@Override
public void dealWithSheet(Sheet sheet,CellStyle cellStyle, DataFormat dataFormat) {
cellStyle.setDataFormat(dataFormat.getFormat("@"));
sheet.setDefaultColumnStyle(3, cellStyle);
}
});
System.out.println("共使用"+(System.currentTimeMillis() - startTime)+"ms");
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void write(OutputStream outputStream, String[] title, String[] column, List data, CellStyleInterface cellStyleInterface) {
if (data.size() > 10000) {
getOutputBigFile(outputStream, title, column, data,cellStyleInterface);
} else {
getOutputFile(outputStream, title, column, data,cellStyleInterface);
}
}
private static void getOutputFile(OutputStream outputStream, String[] title, String[] column, List data, CellStyleInterface cellStyleInterface) {
HSSFWorkbook wwb = null;
try {
wwb = new HSSFWorkbook();
HSSFSheet ws = null;
HSSFRow row = null;
HSSFCell cell = null;
Iterator it = data.iterator();
int i = 0;
ws = wwb.createSheet("Sheet0");
if(cellStyleInterface != null)
{
cellStyleInterface.dealWithSheet(ws, wwb.createCellStyle(), wwb.createDataFormat());
}
row = ws.createRow(0);
for (int m = 0; m < title.length; m++) {
cell = row.createCell((short) m);
cell.setCellValue(new HSSFRichTextString(title[m]));
}
int k = 0;
while (it.hasNext()) {
if (i / SHEET_SPLIT_NUM > k) {
k = i / SHEET_SPLIT_NUM;
ws = wwb.createSheet("Sheet" + k);
row = ws.createRow(0);
for (int l = 0; l < title.length; l++) {
cell = row.createCell((short) l);
cell.setCellValue(new HSSFRichTextString(title[l]));
}
}
Object obj = it.next();
Map dataMap = new HashMap();
if(obj instanceof Map) {
dataMap = (Map)obj;
}
else
{
dataMap = new BeanMap(obj);
}
row = ws.createRow(i - SHEET_SPLIT_NUM * k + 1);
for (int j = 0; j < column.length; j++) {
cell = row.createCell((short) j);
String columnName = column[j];
cell.setCellValue(new HSSFRichTextString(getValue(dataMap, columnName)));
if(cellStyleInterface != null)
{
cellStyleInterface.dealWithCell(cell, wwb.createCellStyle(), wwb.createDataFormat(), columnName, getValue(dataMap, columnName));
}
}
i++;
}
for (int j = 0; j < column.length; j++) {
ws.autoSizeColumn(j);
}
wwb.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
try {
outputStream.close();
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private static void getOutputBigFile(OutputStream outputStream, String[] title, String[] column, List data, CellStyleInterface cellStyleInterface) {
try {
SXSSFWorkbook wb = new SXSSFWorkbook(5000);
Sheet ws = null;
Row row = null;
Cell cell = null;
Iterator it = data.iterator();
int i = 0;
ws = wb.createSheet("Sheet0");
if(cellStyleInterface != null)
{
cellStyleInterface.dealWithSheet(ws, wb.createCellStyle(), wb.createDataFormat());
}
row = ws.createRow(0);
for (int m = 0; m < title.length; m++) {
cell = row.createCell((short) m);
cell.setCellValue(new HSSFRichTextString(title[m]));
}
int k = 0;
while (it.hasNext()) {
if (i / SHEET_SPLIT_NUM > k) {
k = i / SHEET_SPLIT_NUM;
ws = wb.createSheet("Sheet" + k);
row = ws.createRow(0);
for (int l = 0; l < title.length; l++) {
cell = row.createCell((short) l);
cell.setCellValue(new HSSFRichTextString(title[l]));
}
}
Object obj = it.next();
Map dataMap = new HashMap();
if(obj instanceof Map) {
dataMap = (Map)obj;
}
else
{
dataMap = new BeanMap(obj);
}
row = ws.createRow(i - SHEET_SPLIT_NUM * k + 1);
for (int j = 0; j < column.length; j++) {
cell = row.createCell((short) j);
String columnName = column[j];
cell.setCellValue(new HSSFRichTextString(getValue(dataMap, columnName)));
if(cellStyleInterface != null)
{
cellStyleInterface.dealWithCell(cell, wb.createCellStyle(), wb.createDataFormat(), columnName, getValue(dataMap, columnName));
}
}
i++;
}
for (int j = 0; j < column.length; j++) {
ws.autoSizeColumn(j);
}
wb.write(outputStream);
} catch (Exception e) {
e.printStackTrace();
try {
outputStream.close();
} catch (Exception e1) {
e1.printStackTrace();
}
} finally {
try {
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private static String getValue(Map dataMap, String columnName) {
return (dataMap.get(columnName) == null) || (String.valueOf(dataMap.get(columnName)).equalsIgnoreCase("null"))
? "" : String.valueOf(dataMap.get(columnName)).trim();
}
/**
* 根据文件路径和行数读取文件内容,并返回Map集合对象
* @param fileItem FileItem
* @param cellNumber 每行单元格数量
* @param sheetNumer sheet编号,从0开始
* @param start 第几行开始读取数据
* @return
*/
public static List<Map<Integer, Object>> readObjFromXls(FileItem fileItem, int cellNumber, int sheetNumer,int start) {
Workbook wb = null;
List<Map<Integer, Object>> listMap = new ArrayList<Map<Integer, Object>>();
try {
if (isExcel2003(fileItem.getName()))
{
wb = new HSSFWorkbook(fileItem.getInputStream());
}
else
{
wb = new XSSFWorkbook(fileItem.getInputStream());
}
// 循环工作表Sheet
Sheet sheet = wb.getSheetAt(sheetNumer); // 默认取第一个模板
if (null == sheet) {// 如果sheet为null则模板不匹配
throw new Exception("导入模板与所选的导入方案不匹配");
}
// HSSFRow titleRow = hssfSheet.getRow(0); // 第一行,表头
FormulaEvaluator eval = wb.getCreationHelper().createFormulaEvaluator();
Map<Integer, Object> map = null;
for (int rowNum = start; rowNum <= sheet.getLastRowNum(); rowNum++) {
map = new HashMap<Integer, Object>();
Row row = sheet.getRow(rowNum);
if (row == null) {
continue;
}
// 开始读行
for (int cellNum = 0; cellNum < cellNumber; cellNum++) {
Cell cell = row.getCell(cellNum);
if (cell == null) {
map.put(cellNum, ""); // 存放第多少列的值
continue;
}
// 具体读取单元格的值
String value = getValue(cell,eval); // 当前行的第(cellNum+1)个单元格里面的值
if (!StringUtils.isEmpty(value) && value.endsWith(".0")) {
// 判断是否以.0结尾
value = value.substring(0, value.lastIndexOf(".0"));
}
map.put(cellNum, value); // 存放第多少列的值
}
listMap.add(map);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fileItem.getInputStream().close();
} catch (IOException e) {
e.printStackTrace();
}
}
return listMap;
}
/**
* 获取单元格里面的值
*
* @param cell
* @return
*/
private static String getValue(Cell cell,FormulaEvaluator eval) {
String value = "";
switch (eval.evaluateInCell(cell).getCellType()) {
case Cell.CELL_TYPE_STRING:// excel数据是字符串类型
value = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_FORMULA:// excel数据是公式生成的
value = cell.getCellFormula();
// eval.evaluateFormulaCell(hssfCell);
// CellValue cell = eval.evaluate(hssfCell);
// value = cell.getStringValue();
break;
case Cell.CELL_TYPE_NUMERIC:// excel数据是数字类型
value = NumberToTextConverter.toText(cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_BLANK:// excel数据是空值
value = "";
break;
case Cell.CELL_TYPE_ERROR:// excel数据类型读取出错
value = "";
break;
}
return value;
}
/**
* 是否是excel2003
* @param filePath
* @return
*/
public static boolean isExcel2003(String filePath)
{
return filePath.matches("^.+\\.(?i)(xls)$");
}
/**
* 是否是excel2007
* @param filePath
* @return
*/
public static boolean isExcel2007(String filePath)
{
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
}
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Sheet;
public interface CellStyleInterface {
void dealWithCell(Cell cell, CellStyle cellStyle, DataFormat dataFormat,String name, String value);
void dealWithSheet(Sheet sheet, CellStyle cellStyle, DataFormat dataFormat);
}