package com.yuxinglab.poi.test;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
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;
/**
* Excel模版类 提供Excel模版文件,找到"datas"所在位置,以此作为数据插入位置
*
* @author yuxing
*
*/
public class ExcelTemplate {
private static ExcelTemplate excelTemplate = new ExcelTemplate();
private Workbook workbook;
private int initColIndex; // 数据的初始化列数
private int initRowIndex; // 数据的初始化行数
private int curColIndex; // 数据当前列数
private int curRowIndex; // 数据当前行数
private Row curRow; // 当前行
private Sheet sheet;
private ExcelTemplate() {
}
public static ExcelTemplate getInstance() {
return excelTemplate;
}
public final static String DATA_BEGIN = "datas";
// 读取模版
public ExcelTemplate readExcelTemplateFromClassPath(String path) {
try {
workbook = WorkbookFactory.create(ExcelTemplate.class
.getResourceAsStream(path));
initTemplate();
} catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("读取模版文件失败!请检查文件格式.");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("模版文件不存在!请检查.");
}
return this;
}
public ExcelTemplate readExcelTemplateFromPath(String path) {
try {
workbook = WorkbookFactory.create(new File(path));
initTemplate();
} catch (InvalidFormatException e) {
e.printStackTrace();
throw new RuntimeException("读取模版文件失败!请检查文件格式.");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("模版文件不存在!请检查.");
}
return this;
}
private void initTemplate() {
sheet = workbook.getSheetAt(0);
initConfigData();
createRow();
}
private void initConfigData() {
boolean flag = false;
for (Row row : sheet) {
if (flag) {
break;
}
for (Cell c : row) {
if (c.getCellType() != c.CELL_TYPE_STRING) {
continue;
}
String str = c.getStringCellValue().trim();
if (str.equals(DATA_BEGIN)) {
initColIndex = c.getColumnIndex();
initRowIndex = row.getRowNum();
curColIndex = initColIndex;
curRowIndex = initRowIndex;
flag = true;
break;
}
}
}
}
public static void main(String[] args) {
ExcelTemplate excelTemplate = getInstance().readExcelTemplateFromPath(
"d:/template.xls");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createCell("111");
excelTemplate.createRow();
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createCell("222");
excelTemplate.createRow();
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createCell("333");
excelTemplate.createRow();
excelTemplate.writeToFile("d:/01.xls");
}
public void createCell(String value) {
curRow.createCell(curColIndex).setCellValue(value);
curColIndex++;
}
public void createRow() {
curRow = sheet.createRow(curRowIndex);
curRowIndex++;
curColIndex = initColIndex;
}
public void writeToFile(String filePath) {
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream(filePath);
workbook.write(fileOutputStream);
} catch (FileNotFoundException e) {
e.printStackTrace();
throw new RuntimeException("写入的文件" + filePath + "不存在!");
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("写入数据失败:"+e.getMessage());
} finally {
try {
if (fileOutputStream != null) {
fileOutputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
public void writeToStream(OutputStream outputStream) {
try {
workbook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("写入流失败:"+e.getMessage());
}
}
}
对于web应用,可以使用如下代码:
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition",
"attachment;filename=file.xls");
OutputStream ouputStream = null;
try {
ouputStream = response.getOutputStream();
excelTemplate.writeToStream(ouputStream);
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
ouputStream.flush();
ouputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
效果如下:
Excel最大行数65536!
注意jar包是否冲突!