import java.io.IOException;
import java.net.URLEncoder;
import java.util.Calendar;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.Sheet;
public class ExcelUtil {
private static String DATE_FORMAT = "yyyy-MM-dd"; // "YYYY-MM-DD" 定制日期格式
private static String NUMBER_FORMAT = "#,##0.00";// 定制浮点数格式
private String xlsFilePath;// 文件名
private String fileName = "export";
private HSSFWorkbook workbook;// 工作薄
private HSSFSheet sheet1;// 工作表
private HSSFSheet sheet2;// 工作表
// private HSSFRow row;// 行
private String[] name1;
private String[][] data1;
private String[] name2;
private String[][] data2;
private HttpServletResponse response;
public ExcelUtil(String[] name,String[][] data,HttpServletResponse response) {
this.name1 = name;
this.data1 = data;
this.response = response;
init();
}
public ExcelUtil(String[] name1,String[] name2,String[][] data1,String[][] data2,HttpServletResponse response) {
this.name1 = name1;
this.data1 = data1;
this.name2 = name2;
this.data2 = data2;
this.response = response;
init("订单","订单详情");
}
private void init(String sheetName1,String sheetName2){
this.workbook = new HSSFWorkbook();
this.sheet1 = workbook.createSheet(sheetName1);
int rowNum1 = this.data1.length;
int colNum1 = this.name1.length;
this.sheet2 = workbook.createSheet(sheetName2);
int rowNum2 = this.data2.length;
int colNum2 = this.name2.length;
// 输出表头
HSSFRow row1= createRow(0,sheet1);
for (int i = 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();
分享到:
相关推荐
Excelutil的使用说明书
封装了excel工具类,只需要几行代码就能实现导入导出功能,适用导出List和List的类型。项目来源于:https://github.com/SargerasWang/ExcelUtil
ExcelUtil.java
ExcelUtil,一个简单封装的excel解析和生成类,基于java poi组件实现excel通用导入导出
工具类JAVA代码 博文链接:https://uule.iteye.com/blog/1864863
Excel的一个工具类,可以实现Excel的导入导出等功能
vue页面导入导出功能,简单实用,一键导入导出页面的表格
操作Excel的工具ExcelUtil源码
ExcelUtil java excel工具类
ExcelUtil便捷读取工具 v3.1.6.zip
ExcelUtil 借助反射和 POI 对 Excel 读取,省略了以往读取 Excel 的繁琐步骤,调用 ExcelUtil 只需要2步,对,你没有看错,2步足以读取到 Excel 的内容。自动赋值,传入对应Model所在路径,即可自动赋值。自动匹配...
最新导入导出工具类ExcelUtil.java
ExcelUtil万能读取类兼容03/07
ExcelUtil借助反射和POI对Excel读取,省略了以往读取Excel的繁琐步骤,调用ExcelUtil只需要2步,对,你没有看错,2步足以读取到Excel的内容.兼容03/07版Excel.
资源分类:Python库 所属语言:Python 资源全名:robotframework-excelutil-9.1.tar.gz 资源来源:官方 安装方法:https://lanzao.blog.csdn.net/article/details/101784059
为您提供ExcelUtil便捷读取工具下载,ExcelUtil 借助反射和 POI 对 Excel 读取,省略了以往读取 Excel 的繁琐步骤,调用 ExcelUtil 只需要2步,对,你没有看错,2步足以读取到 Excel 的内容。自动赋值,传入对应...