|
package framework.util.excel;
|
|
import java.io.File;
|
import java.io.FileInputStream;
|
import java.io.FileOutputStream;
|
import java.io.InputStream;
|
import java.text.SimpleDateFormat;
|
import java.util.Date;
|
import java.util.HashMap;
|
import java.util.Map;
|
|
import org.apache.poi.hssf.usermodel.HSSFCell;
|
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
|
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.hssf.util.HSSFColor;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
/**
|
* poi 3.6
|
* jdk1.4
|
*/
|
public final class ExcelWriter {
|
|
public static final int STYLE_BORDER = 0X01;
|
public static final int STYLE_BACK_GRAY = 0x02;
|
public static final int STYLE_BACK_YELLOW = 0x04;
|
|
public static final int STYLE_ALIGN_LEFT = 0x0100;
|
public static final int STYLE_ALIGN_CENTER = 0x0200;
|
public static final int STYLE_ALIGN_RIGHT = 0x0400;
|
|
public static final int MAX_ROWS = 65500;
|
|
private ExcelWriter(){
|
|
}
|
private static String modulePath = null;
|
|
/**
|
* 设置模板路径
|
* @param path
|
*/
|
public static void setModulePath(String path){
|
if(path.endsWith("/")){
|
path = path.substring(0, path.length()-1);
|
}
|
modulePath = path;
|
}
|
private static String downloadPath = null;
|
/**
|
* 设置生成路径
|
* @param path
|
*/
|
public static void setTempPath(String path){
|
if(path.endsWith("/")){
|
path = path.substring(0, path.length()-1);
|
}
|
downloadPath = path;
|
}
|
protected static Map<String, byte[]> moduleMap = new HashMap<String, byte[]>();
|
|
protected static byte[] getModuleContent(String moduleName)
|
throws Exception {
|
byte[] b = (byte[])moduleMap.get(moduleName);
|
if(b==null){
|
synchronized(moduleMap){
|
b = (byte[])moduleMap.get(moduleName);
|
if(b==null){
|
String moduleFileName = modulePath +"/"+moduleName;
|
FileInputStream fis = null;
|
try{
|
fis = new FileInputStream(moduleFileName);
|
b = new byte[fis.available()];
|
fis.read(b);
|
moduleMap.put(moduleName, b);
|
}catch(Exception e){
|
throw e;
|
}finally{
|
try{
|
fis.close();
|
}catch(Exception e){
|
|
}
|
}
|
}
|
}
|
}
|
return b;
|
}
|
static {
|
String moduleName = "blank.xls";
|
InputStream is = ExcelWriter.class.getResourceAsStream(moduleName);
|
try{
|
byte[] b = new byte[is.available()];
|
is.read(b);
|
moduleMap.put(moduleName, b);
|
}catch(Exception e){
|
|
}finally{
|
try{
|
is.close();
|
}catch(Exception e){
|
|
}
|
}
|
}
|
/**
|
* 使用默认模板创建Writer
|
* @param fileName
|
* @return
|
* @throws Exception
|
*/
|
public static ExcelWriter open(String fileName) throws Exception {
|
return open(fileName, "blank");
|
}
|
private static SimpleDateFormat timeFmt = new SimpleDateFormat("yyyy-MM-dd_HHmmss.SSSS");
|
/**
|
* 使用模板创建Writer
|
* @param fileName
|
* @param moduleName
|
* @return
|
* @throws Exception
|
*/
|
public static ExcelWriter open(String fileName, String moduleName)
|
throws Exception {
|
ExcelWriter writer = new ExcelWriter();
|
String time = "_"+timeFmt.format(new Date());
|
|
fileName = fileName.replaceAll("[\r\n\\*?<>|/]", "");
|
|
writer.fileName = downloadPath + "/" + fileName + time +".xls";
|
|
FileOutputStream fos = null;
|
try{
|
fos = new FileOutputStream(writer.fileName);
|
fos.write(getModuleContent(moduleName+".xls"));
|
fos.flush();
|
}catch(Exception e){
|
throw e;
|
}finally{
|
fos.close();
|
}
|
|
writer.fis = new FileInputStream(writer.fileName);
|
writer.book = new HSSFWorkbook(writer.fis);
|
|
writer.dateStyle = writer.book.createCellStyle();
|
writer.dateStyle.setDataFormat(writer.book.createDataFormat()
|
.getFormat("yyyy-mm-dd"));
|
|
writer.timeStyle = writer.book.createCellStyle();
|
writer.timeStyle.setDataFormat(writer.book.createDataFormat()
|
.getFormat("hh:mm:ss"));
|
|
writer.datetimeStyle = writer.book.createCellStyle();
|
writer.datetimeStyle.setDataFormat(writer.book.createDataFormat()
|
.getFormat("yyyy-mm-dd hh:mm:ss"));
|
|
writer.monthStyle = writer.book.createCellStyle();
|
writer.monthStyle.setDataFormat(writer.book.createDataFormat()
|
.getFormat("yyyy-mm"));
|
|
writer.sheet = null; //初始没有sheet
|
|
return writer;
|
}
|
|
protected String fileName;
|
protected FileInputStream fis;
|
protected HSSFWorkbook book;
|
protected HSSFCellStyle monthStyle;
|
protected HSSFCellStyle dateStyle;
|
protected HSSFCellStyle timeStyle;
|
protected HSSFCellStyle datetimeStyle;
|
|
protected HSSFSheet sheet;
|
|
/**
|
* 冻结窗口
|
* @param row 从0开始
|
* @param col 从0开始
|
*/
|
public ExcelWriter freeze(int row, int col){
|
if(sheet!=null){
|
sheet.createFreezePane(col, row);
|
}
|
return this;
|
}
|
/**
|
* 删除sheet
|
*
|
* @param sheetName
|
*/
|
public ExcelWriter deleteSheet(String sheetName){
|
try{
|
if(sheetName!=null){
|
book.removeSheetAt(book.getSheetIndex(sheetName));
|
}
|
}catch(Throwable e){
|
|
}
|
return this;
|
}
|
/**
|
* 转到sheet
|
*
|
* @param sheetName
|
*/
|
public ExcelWriter setSheet(String sheetName){
|
if(sheetName == null){
|
sheet = book.getSheetAt(0);
|
}else{
|
sheet = book.getSheet(sheetName);
|
if(sheet == null){
|
int len = book.getNumberOfSheets();
|
String s = ""+len+(new Date()).getTime();
|
sheet = book.createSheet(s);
|
book.setSheetName(book.getSheetIndex(s), sheetName);
|
}
|
book.setActiveSheet(book.getSheetIndex(sheetName));
|
}
|
return this;
|
}
|
/**
|
* 复制sheet并选择新sheet
|
* @param src
|
* @param des
|
* @return
|
* @throws Exception
|
*/
|
public ExcelWriter copySheet(String src, String des) {
|
HSSFSheet sh = book.cloneSheet(book.getSheetIndex(src));
|
String sn = sh.getSheetName();
|
book.setSheetName(book.getSheetIndex(sn), des);
|
sheet = sh;
|
return this;
|
}
|
/**
|
* 自动适应列
|
*
|
* @param col
|
* @return
|
*/
|
public ExcelWriter autoSize(int col){
|
if(sheet!=null){
|
sheet.autoSizeColumn(col);
|
}
|
return this;
|
}
|
/**
|
* 得到单元格
|
*
|
* @param row 从0开始
|
* @param col 从0开始
|
* @return
|
*/
|
public HSSFCell getCell(int row, int col){
|
if(sheet == null){
|
setSheet(null);
|
}
|
HSSFRow curRow = sheet.getRow(row);
|
if(curRow == null){
|
curRow = sheet.createRow(row);
|
}
|
HSSFCell cell = curRow.getCell(col);
|
if(cell == null){
|
cell = curRow.createCell(col);
|
}
|
|
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
|
return cell;
|
}
|
|
/**
|
* 合并单元格
|
*
|
* @param row1
|
* @param col1
|
* @param row2
|
* @param col2
|
* @return
|
*/
|
public ExcelWriter margeCell(int row1, int col1, int row2, int col2){
|
CellRangeAddress region = new CellRangeAddress(row1, row2, col1, col2);
|
sheet.addMergedRegion(region);
|
return this;
|
}
|
|
/**
|
* 设置样式
|
*
|
* @param row
|
* @param col
|
* @param style
|
* @return
|
*/
|
public ExcelWriter setCellStyle(int row, int col, int style){
|
HSSFCellStyle s = book.createCellStyle();
|
|
if((style & STYLE_BORDER)==STYLE_BORDER){
|
s.setBorderBottom(HSSFCellStyle.BORDER_THIN);
|
s.setBorderTop(HSSFCellStyle.BORDER_THIN);
|
s.setBorderLeft(HSSFCellStyle.BORDER_THIN);
|
s.setBorderRight(HSSFCellStyle.BORDER_THIN);
|
}
|
if((style & STYLE_BACK_GRAY)==STYLE_BACK_GRAY){
|
s.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
|
s.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
|
}
|
if((style & STYLE_BACK_YELLOW)==STYLE_BACK_YELLOW){
|
s.setFillForegroundColor(HSSFColor.YELLOW.index);
|
s.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
|
}
|
|
if((style & STYLE_ALIGN_LEFT)==STYLE_ALIGN_LEFT){
|
s.setAlignment(HSSFCellStyle.ALIGN_LEFT);
|
}else if((style & STYLE_ALIGN_RIGHT)==STYLE_ALIGN_RIGHT){
|
s.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
|
}else if((style & STYLE_ALIGN_CENTER)==STYLE_ALIGN_CENTER){
|
s.setAlignment(HSSFCellStyle.ALIGN_CENTER);
|
}
|
this.getCell(row, col).setCellStyle(s);
|
|
return this;
|
}
|
/**
|
* 以 STYLE_BACK_GRAY|STYLE_BORDER 样式填充
|
* @param row
|
* @param col
|
* @param s
|
* @return
|
*/
|
public ExcelWriter setTitle(int row, int col, String s){
|
this.setCellStyle(row, col, STYLE_BACK_GRAY|STYLE_BORDER);
|
this.setCellValue(row, col, s);
|
this.autoSize(col);
|
return this;
|
}
|
/**
|
* 以STYLE_BACK_GRAY|STYLE_BORDER样式填充第一行,并冻结
|
* @param s
|
* @return
|
*/
|
public ExcelWriter setTitle(String[] s){
|
for(int i=0; i<s.length; i++){
|
setTitle(0,i,s[i]);
|
}
|
this.freeze(1, 0);
|
return this;
|
}
|
public ExcelWriter setCellValue(int row, int col, String str) {
|
getCell(row, col).setCellValue(str==null?"":str);
|
return this;
|
}
|
|
public ExcelWriter setCellValue(int row, int col, Integer d) {
|
if(d!=null){
|
getCell(row, col).setCellValue(d);
|
}
|
return this;
|
}
|
public ExcelWriter setCellValue(int row, int col, Long d) {
|
if(d!=null){
|
getCell(row, col).setCellValue(d);
|
}
|
return this;
|
}
|
public ExcelWriter setCellValue(int row, int col, Double d) {
|
if(d!=null){
|
getCell(row, col).setCellValue(d);
|
}
|
return this;
|
}
|
/**
|
* yyyy-MM-dd
|
*
|
* @param row
|
* @param col
|
* @param date
|
* @return
|
*/
|
public ExcelWriter setCellValue(int row, int col, Date date) {
|
HSSFCell cell = getCell(row, col);
|
cell.setCellStyle(dateStyle);
|
if(date!=null){
|
cell.setCellValue(date);
|
}
|
return this;
|
}
|
/**
|
* yyyy-MM-dd HH:mm:ss
|
*
|
* @param row
|
* @param col
|
* @param date
|
* @return
|
*/
|
public ExcelWriter setCellValueDatetime(int row, int col, Date date) {
|
HSSFCell cell = getCell(row, col);
|
cell.setCellStyle(datetimeStyle);
|
if(date!=null){
|
cell.setCellValue(date);
|
}
|
return this;
|
}
|
/**
|
* HH:mm:ss
|
*
|
* @param row
|
* @param col
|
* @param date
|
* @return
|
*/
|
public ExcelWriter setCellValueTime(int row, int col, Date date) {
|
HSSFCell cell = getCell(row, col);
|
cell.setCellStyle(timeStyle);
|
if(date!=null){
|
cell.setCellValue(date);
|
}
|
return this;
|
}
|
/**
|
* yyyy-MM
|
*
|
* @param row
|
* @param col
|
* @param date
|
* @return
|
*/
|
public ExcelWriter setCellValueMonth(int row, int col, Date date) {
|
HSSFCell cell = getCell(row, col);
|
cell.setCellStyle(monthStyle);
|
if(date!=null){
|
cell.setCellValue(date);
|
}
|
return this;
|
}
|
|
/**
|
* 关闭，生成文件
|
*
|
*/
|
public void saveAndClose() throws Exception {
|
FileOutputStream fos = new FileOutputStream(fileName);
|
book.write(fos);
|
fos.flush();
|
fos.close();
|
fis.close();
|
}
|
/**
|
* 得到生成的文件
|
*
|
* @return
|
*/
|
public File getFile(){
|
return new File(fileName);
|
}
|
|
public static void main(String[] args) throws Exception{
|
ExcelWriter.setTempPath("f:/");
|
//System.err.println(moduleMap);
|
ExcelWriter w = ExcelWriter.open("测试");
|
w.setSheet("sheet1");
|
w.setTitle(new String[]{
|
"序号","姓\n名","地址"
|
});
|
w.saveAndClose();
|
}
|
public final HSSFWorkbook getBook() {
|
return book;
|
}
|
public final HSSFSheet getSheet() {
|
return sheet;
|
}
|
}
|
