package com.xxx.util;
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.usermodel.WorkbookFactory;
public class ExcelTemplate {
/**
* 数据行标识
*/
public final static String DATA_LINE = "datas";
/**
* 默认样式标识
*/
public final static String DEFAULT_STYLE = "defaultStyles";
/**
* 行样式标识
*/
public final static String STYLE = "styles";
/**
* 插入序号样式标识
*/
public final static String SER_NUM = "sernums";
public static ExcelTemplate et = new ExcelTemplate();
public Workbook wb;
public Sheet sheet;
/**
* 数据的初始化列数
*/
public int initColIndex;
/**
* 数据的初始化行数
*/
public int initRowIndex;
/**
* 当前列数
*/
public int curColIndex;
/**
* 当前行数
*/
public int curRowIndex;
/**
* 当前行对象
*/
public Row curRow;
/**
* 最后一行的数据
*/
public int lastRowIndex;
/**
* 默认样式
*/
public CellStyle defaultStyle;
/**
* 默认行高
*/
public float rowHeight;
/**
* 存储某一方所对应的样式
*/
public Map<Integer, CellStyle> styles;
/**
* 序号的列
*/
public int serColIndex;
public ExcelTemplate() {
}
public static ExcelTemplate getInstance() {
return et;
}
/**
* 1、读取相应的模板文档
*/
public ExcelTemplate readTemplateByClasspath(String path) {
try {
wb = WorkbookFactory.create(ExcelTemplate.class.getResourceAsStream(path));
initTemplate();
} catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("InvalidFormatException, please check.");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("The template is not exist, please check.");
}
return this;
}
public ExcelTemplate readTemplateByPath(String path) {
try {
wb = WorkbookFactory.create(new File(path));
initTemplate();
} catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("InvalidFormatException, please check.");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("The template is not exist, please check.");
}
return this;
}
public void initTemplate() {
sheet = wb.getSheetAt(0);
initConfigData();
lastRowIndex = sheet.getLastRowNum();
curRow = sheet.getRow(curRowIndex);
}
/**
* 循环遍历,找到有datas字符的那个单元,记录initColIndex,initRowIndex,curColIndex,curRowIndex
* 调用initStyles()方法
* 在寻找datas字符的时候会顺便找一下sernums,如果有则记录其列号serColIndex;如果没有则调用initSer()方法,重新循环查找
*/
public void initConfigData() {
boolean findData = false;
boolean findSer = false;
for (Row row : sheet) {
if (findData)
break;
for (Cell c : row) {
if (c.getCellType() != Cell.CELL_TYPE_STRING)
continue;
String str = c.getStringCellValue().trim();
if (str.equals(SER_NUM)) {
serColIndex = c.getColumnIndex();
findSer = true;
}
if (str.equals(DATA_LINE)) {
initCo