import java.io.IOException;
import java.net.URLEncoder;
import java.util.Calendar;
导入javax.servlet.servlet output stream;
import javax.servlet.http.http无servlet保罗;
importorg.Apache.poi.hssf.usermodel.hssf cell;
importorg.Apache.poi.hssf.usermodel.hssfcellstyle;
importorg.Apache.poi.hssf.usermodel.hssfdataformat;
importorg.Apache.poi.hssf.usermodel.hssf row;
importorg.Apache.poi.hssf.usermodel.hssf sheet;
importorg.Apache.poi.hssf.usermodel.hssf workbook;
importorg.Apache.poi.ss.usermodel.sheet;
公共类excel util {
私密性staticstringdate _ format=' yyyy-mm-DD '; //'YYYY-MM-DD '自定义日期格式
privatestaticstringnumber _ format=' #、##0.00 '; //自定义浮点格式
私有字符串xls文件路径; //文件名
私有字符串文件名=' export ';
隐私汇丰工作手册; //工作很少
私密hssf sheet sheet 1; //工作表
私密hssf sheet sheet 2; //工作表
//private HSSFRow row; //行
private String[] name1;
私有字符串[ ] [ ] data1;
private String[] name2;
私有字符串[ ] [ ] data 2;
私有ttpservletresponseresponse;
publicexcelutil (字符串[ ] name,字符串[ ] [ ]数据,http servlet响应响应) {
this.name1=name;
this.data1=data;
this.response=response;
init (;
}
publicexcelutil(string[]name1,String[] name2,String[][] data1,String[][] data2,httpservletresponseresponserespopopot
this.name1=name1;
this.data1=data1;
this.name2=name2;
this.data2=data2;
this.response=response;
init (“订单”、“订单详细信息”);
}
私有套件名称(String sheetName2,String sheetName2) {
this.workbook=new HSSFWorkbook (;
this.sheet1=workbook.create sheet (sheet name1);
int rowNum1=this.data1.length;
int colNum1=this.name1.length;
this.sheet2=workbook.create sheet (sheet name2);
int rowNum2=this.data2.length;
int colNum2=this.name2.length;
//输出标题
HSFrowrow1=createrow(0,sheet1);
for(intI=0; i colNum1; I ) {
Setcell(I,this.name1[i],row1);
}
//输出数据
for (int n = 0; n < rowNum1; n++) {HSSFRow row2 = createRow(n + 1,sheet1);
for (int m = 0; m < colNum1; m++) {
setCell(m, this.data1[n][m],row2);
}
}
HSSFRow row3= createRow(0,sheet2);
for (int i = 0; i < colNum2; i++) {
setCell(i, this.name2[i],row3);
}
// 输出数据
for (int n = 0; n < rowNum2; n++) {
HSSFRow row4= createRow(n + 1,sheet2);
for (int m = 0; m < colNum2; m++) {
if(data2[n]!=null && data2[n][m]!=null){
setCell(m, this.data2[n][m],row4);
}
}
}
}
private void init(){
this.workbook = new HSSFWorkbook();
this.sheet1 = workbook.createSheet();
int rowNum = this.data1.length;
int colNum = this.name1.length;
// 输出表头
HSSFRow row1= createRow(0);
for (int i = 0; i < colNum; i++) {
setCell(i, this.name1[i],row1);
}
// 输出数据
for (int n = 0; n < rowNum; n++) {
HSSFRow row2= createRow(n + 1);
for (int m = 0; m < colNum; m++) {
setCell(m, this.data1[n][m],row2);
}
}
}
// 输出流
public void write() throws IOException{
this.response.setContentType("application/vnd.ms-excel; charset=UTF-8");
this.response.setHeader("Content-Disposition", "filename="+URLEncoder.encode(this.fileName,"utf-8")+".xls");
this.response.setHeader("Cache-Control", "no-cache");
ServletOutputStream os = this.response.getOutputStream();
workbook.write(os);
os.flush();
os.close();
}
//增加一行
public HSSFRow createRow(int index) {
HSSFRow row = this.sheet1.createRow(index);
return row;
}
public HSSFRow createRow(int index,HSSFSheet sheet) {
HSSFRow row = sheet.createRow(index);
return row;
}
//设置单元格,传入值为字符串的
public void setCell(int index, String value,HSSFRow row) {
HSSFCell cell = row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(value);
}
// 设置单元格,传入值为日期的
public void setCell(int index, Calendar value,HSSFRow row) {
HSSFCell cell = row.createCell(index);
cell.setCellValue(value.getTime());
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
}
//设置单元格,传入数据为int型
public void setCell(int index, int value,HSSFRow row) {
HSSFCell cell = row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
}
//设置单元格,传入值为double型
public void setCell(int index, double value,HSSFRow row) {
HSSFCell cell = row.createCell(index);
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(value);
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
HSSFDataFormat format = workbook.createDataFormat();
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
}
public String getXlsFilePath() {
return xlsFilePath;
}
public void setXlsFilePath(String xlsFilePath) {
this.xlsFilePath = xlsFilePath;
}
public String getFileName() {
return fileName;
}
public void setFileName(String fileName) {
this.fileName = fileName;
}
public HSSFWorkbook getWorkbook() {
return workbook;
}
}
使用:
String[] name1 = {"订单编号","买家会员名","收货人姓名","详细地址","买家留言"};
String[] name2 = {"订单编号","标题","颜色","尺码","价格","购买数量","备注"};
String[][] data = new String[list.size()][11];
String[][] data2=new String[10][7];
ExcelUtil eu =new ExcelUtil(name1,name2,data,data2,response);
eu.write();