利用poi实现java导出数据到excel
POI简介:Jakarta POI 是一套用于访问微软格式文档的Java API。Jakarta POI有很多组件组成,其中有用于操作Excel格式文件的HSSF和用于操作Word的HWPF,在各种组件中目前只有用于操作Excel的HSSF相对成熟。
官方主页,
现在用的比较多的都是用POI技术来导出或者导入Excel,所以我们就用POI吧,用POI导出Excel我们首先要下载所需的jar包然后导入到我们的项目中,用maven的同学只需找到相关
1、依赖加入到pom.xml里面即可。
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
2、excel的工具类excelToolClass 代码:
package com.wondersgroup.materiel.bomsys.bom.utils;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;
import org.apache.poi.ss.util.Region;
public class excelToolClass {
private HSSFWorkbook wb = null;// 得到Excel工作簿对象
private HSSFSheet sheet = null;// 得到Excel工作表对象
public excelToolClass(HSSFWorkbook wb, HSSFSheet sheet) {
this.wb = wb;
this.sheet = sheet;
}
/**
* 创建通用的Excel头
*
* @param headString 头部显示的字符
* @param colSum 该报表的列数
*/
//第一行
public void createNormalHead(String headString) {
// 设置第一行
HSSFRow row = sheet.createRow(0);// 创建Excel工作表的行
HSSFCell cell = row.createCell(0);// 创建Excel工作表指定行的单元格
row.setHeight((short) 1000);// 设置高度
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell.setCellValue(new HSSFRichTextString(headString));
// 指定合并区域
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 8);
sheet.addMergedRegion(region);
// 定义单元格格式,添加单元格表样式,并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格水平对齐类型
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("微软雅黑");// 字体
font.setFontHeightInPoints((short) 24);// 设置字体
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
//第二行
public void createNormalTwoRow(String headString0,String headString1) {
// 设置第一行 两列
HSSFRow row = sheet.createRow(1);// 创建Excel工作表的行
HSSFCell cell10 = row.createCell(0);// 创建Excel工作表指定行的单元格
row.setHeight((short) 1000);// 设置高度
// 定义单元格为字符串类型
cell10.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell10.setCellValue(new HSSFRichTextString(headString0));
HSSFCell cell14 = row.createCell(4);// 创建Excel工作表指定行的单元格
// 定义单元格为字符串类型
cell14.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell14.setCellValue(new HSSFRichTextString(headString1));
// 指定合并区域
CellRangeAddress region1 = new CellRangeAddress(1, 1, 0, 3);//1103
sheet.addMergedRegion(region1);
CellRangeAddress region2 = new CellRangeAddress(1, 1, 4, 8);//1148
sheet.addMergedRegion(region2);
// 定义单元格格式,添加单元格表样式,并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格水平对齐类型
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("微软雅黑");// 字体
font.setFontHeightInPoints((short) 10);// 设置字体
cellStyle.setFont(font);
cell10.setCellStyle(cellStyle);
cell14.setCellStyle(cellStyle);
}
//第三行 每两个合并为一个单元格
public void createNormalThreeRow(String headString0,String headString1,String headString2,String headString3) {
// 设置第三行 四格
HSSFRow row2 = sheet.createRow(2);// 创建Excel工作表的行
HSSFCell cell20 = row2.createCell(0);// 创建Excel工作表指定行的单元格
row2.setHeight((short) 1000);// 设置高度
cell20.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell20.setCellValue(new HSSFRichTextString(headString0));
HSSFCell cell22 = row2.createCell(2);// 创建Excel工作表指定行的单元格
cell22.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell22.setCellValue(new HSSFRichTextString(headString1));
HSSFCell cell24 = row2.createCell(4);// 创建Excel工作表指定行的单元格
cell24.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell24.setCellValue(new HSSFRichTextString(headString2));
HSSFCell cell26 = row2.createCell(6);// 创建Excel工作表指定行的单元格
cell26.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
cell26.setCellValue(new HSSFRichTextString(headString3));
// 指定合并区域
CellRangeAddress region1 = new CellRangeAddress(2, 2, 0, 1);
CellRangeAddress region2 = new CellRangeAddress(2, 2, 2, 3);
CellRangeAddress region3 = new CellRangeAddress(2, 2, 4, 5);
CellRangeAddress region4 = new CellRangeAddress(2, 2, 6, 7);
sheet.addMergedRegion(region1);
sheet.addMergedRegion(region2);
sheet.addMergedRegion(region3);
sheet.addMergedRegion(region4);
// 定义单元格格式,添加单元格表样式,并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
// 设置单元格水平对齐类型
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("微软雅黑");// 字体
font.setFontHeightInPoints((short) 10);// 设置字体
cellStyle.setFont(font);
cell20.setCellStyle(cellStyle);
cell22.setCellStyle(cellStyle);
cell24.setCellStyle(cellStyle);
cell26.setCellStyle(cellStyle);
}
/**
* 创建报表第四行
*
* @param params 统计条件数组
* @param colSum 需要合并到的列索引
*
*/
@SuppressWarnings("deprecation")
public void createNormalFourRow(List<String> list, int colSum) {
// 创建第四行
HSSFRow row1 = sheet.createRow(3);
row1.setHeight((short) 400);// 设置高度
// HSSFCell cell2 = row1.createCell(0);// 创建Excel工作表指定行的单元格
// cell2.setCellType(HSSFCell.ENCODING_UTF_16);// 中文处理
// 指定合并区域
// CellRangeAddress region = new CellRangeAddress(0, 0, 0, colSum);
// sheet.addMergedRegion(region);
// 定义单元格格式,添加单元格表样式,并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");// 字体
font.setFontHeightInPoints((short) 10);// 设置字体
cellStyle.setFont(font);
// HSSFCellStyle.ALIGN_CENTER 设定居中
for (int i = 0; i < list.size(); i++) {
cteateCell(wb, row1, i, HSSFCellStyle.ALIGN_CENTER, list.get(i), cellStyle);
}
}
/**
* 设置报表标题
*
* @param columHeader 标题字符串数组
*/
public void createColumHeader(List<Map<String, Object>> listInfo) {
// 定义单元格格式,添加单元格表样式,并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");// 字体
font.setFontHeightInPoints((short) 10);// 设置字体
cellStyle.setFont(font);
// 设置单元格背景色
// cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFCell cell3 = null;
for (int i = 0; i < listInfo.size(); i++) {
// 循环插入数据
HSSFRow row2 = sheet.createRow(i + 4);
row2.setHeight((short) 400);// 指定行高
cell3 = row2.createCell(0);
cell3.setCellStyle(cellStyle);
cell3.setCellValue(new HSSFRichTextString(String.valueOf(i)));
cell3 = row2.createCell(1);
cell3.setCellStyle(cellStyle);
if (listInfo.get(i).get("prodCodeSellPtr") != null) {
cell3.setCellValue(new HSSFRichTextString(listInfo.get(i).get("prodCodeSellPtr").toString()));// DESC
} else {
cell3.setCellValue(new HSSFRichTextString(""));
}
cell3 = row2.createCell(2);
cell3.setCellStyle(cellStyle);
if (listInfo.get(i).get("invPartDescriptionC") != null) {
cell3.setCellValue(new HSSFRichTextString((String) listInfo.get(i).get("invPartDescriptionC")));// PART inv_part_description_c
} else {
cell3.setCellValue(new HSSFRichTextString(""));
}
cell3 = row2.createCell(3);
cell3.setCellStyle(cellStyle);
if (listInfo.get(i).get("package_") != null) {
cell3.setCellValue(new HSSFRichTextString((String) listInfo.get(i).get("package_")));// PACKAGE
} else {
cell3.setCellValue(new HSSFRichTextString(""));
}
cell3 = row2.createCell(4);
cell3.setCellStyle(cellStyle);
if (listInfo.get(i).get("prodSupperStr") != null) {
cell3.setCellValue(new HSSFRichTextString((String) listInfo.get(i).get("prodSupperStr")));// MANUFA
} else {
cell3.setCellValue(new HSSFRichTextString(""));// MANUFA
}
cell3 = row2.createCell(5);
cell3.setCellStyle(cellStyle);
if (listInfo.get(i).get("smtFlagStr") != null) {
cell3.setCellValue(new HSSFRichTextString(listInfo.get(i).get("smtFlagStr").toString()));// ASSY
} else {
cell3.setCellValue(new HSSFRichTextString(""));// ASSY
}
cell3 = row2.createCell(6);
cell3.setCellStyle(cellStyle);
cell3.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
if (listInfo.get(i).get("how") != null) {
cell3.setCellValue(new HSSFRichTextString((String) listInfo.get(i).get("how").toString()));// QTY
} else {
cell3.setCellValue(new HSSFRichTextString(""));
}
cell3 = row2.createCell(7);
cell3.setCellStyle(cellStyle);
if (listInfo.get(i).get("top") != null) {
cell3.setCellValue(new HSSFRichTextString((String) listInfo.get(i).get("top").toString()));// top
} else {
cell3.setCellValue(new HSSFRichTextString(""));
}
cell3 = row2.createCell(8);
cell3.setCellStyle(cellStyle);
if (listInfo.get(i).get("bottom") != null) {
cell3.setCellValue(new HSSFRichTextString((String) listInfo.get(i).get("bottom").toString()));// bottom
} else {
cell3.setCellValue(new HSSFRichTextString(""));
}
cell3 = row2.createCell(9);
cell3.setCellStyle(cellStyle);
cell3.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
if (listInfo.get(i).get("std_cost") != null) {
cell3.setCellValue(new HSSFRichTextString((String) listInfo.get(i).get("std_cost").toString()));// price
} else {
cell3.setCellValue(new HSSFRichTextString("0.00"));
}
int h=i+5;
cell3 = row2.createCell(10);
cell3.setCellStyle(cellStyle);
cell3.setCellType(HSSFCell.CELL_TYPE_FORMULA);
// 设置计算公式
if (listInfo.get(i).get("std_cost") != null&& listInfo.get(i).get("how") != null) {
cell3.setCellFormula("G"+h+"*J"+h);
} else {
cell3.setCellValue(new HSSFRichTextString("0.00"));
}
}
}
/**
* 创建内容单元格
*
* @param wb HSSFWorkbook
* @param row HSSFRow
* @param col short型的列索引
* @param align 对齐方式
* @param val 列值
*/
public void cteateCell(HSSFWorkbook wb, HSSFRow row, int col, short align, String val, HSSFCellStyle cellstyle) {
HSSFCell cell = row.createCell(col);
cell.setCellType(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(new HSSFRichTextString(val));
cell.setCellStyle(cellstyle);
}
/**
* 创建合计行
*
* @param colSum 需要合并到的列索引
* @param cellValue
*/
public void createLastSumRow(int colSum, List<String> list) {
// 定义单元格格式,添加单元格表样式,并添加到工作簿
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle.setWrapText(true);// 指定单元格自动换行
// 单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 250);
cellStyle.setFont(font);
// 获取工作表最后一行
HSSFRow lastRow = sheet.createRow((short) (sheet.getLastRowNum() + 1));
HSSFCell sumCell = lastRow.createCell(0);// 创建Excel工作表指定行的单元格
sumCell.setCellValue(new HSSFRichTextString("合计"));
sumCell.setCellStyle(cellStyle);
// 合并 最后一行的第零列-最后一行的第一列
// sheet.addMergedRegion(new Region(sheet.getLastRowNum(), (short) 0,
// sheet.getLastRowNum(), (short) colSum));// 指定合并区域
for (int i = 2; i < (list.size() + 2); i++) {
// 定义最后一行的第三列
sumCell = lastRow.createCell(i);
sumCell.setCellStyle(cellStyle);
// 定义数组 从0开始。
sumCell.setCellValue(new HSSFRichTextString(list.get(i - 2)));
}
}
/**
* 输入EXCEL文件
*
* @param fileName 文件名
*/
public void outputExcel(String fileName) {
FileOutputStream fos = null;
try {
fos = new FileOutputStream(new File(fileName));
wb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public HSSFWorkbook getWb() {
return wb;
}
public void setWb(HSSFWorkbook wb) {
this.wb = wb;
}
public HSSFSheet getSheet() {
return sheet;
}
public void setSheet(HSSFSheet sheet) {
this.sheet = sheet;
}
}
3、service调用excelToolClass:
public void importBomExcelItem(List<Map<String, Object>> list, String filepath, String productName, String version,
String date, String edituserName) throws UnsupportedEncodingException {
if (count == true) {
count = false;
zipPath = filepath;
}
String excelPath = filepath + "\\" + productName + ".xls";
String worksheetTitle = list.get(0).get("custPartCode") + "Excel导出信息";// sheet名
String sheetName = "数据1";
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);// 定义sheet名
excelToolClass exportExcel = new excelToolClass(wb, sheet);// 调用Excel工具类
excelPath = new String(excelPath.getBytes("GBK"), "GBK");// 修改编码格式
// 定义第二行标题
List<String> l = new ArrayList<String>();
l.add("ITEM");
l.add("DESC");
l.add("PART");
l.add("PACKAGE");
l.add("MANUFA");
l.add("ASSY");
l.add("QTY");
l.add("TOP");
l.add("BOTTOM");
l.add("PRICE");
l.add("AMOUNT");
// 定义第一行 创建报表头部
exportExcel.createNormalHead("BILL OF MATERIAL 产品物料清单");
// 定义第二行
if (productName.equals("")) {
exportExcel.createNormalTwoRow("Product Name:", "Product Name:");
} else {
exportExcel.createNormalTwoRow("Product Name:" + productName, "Product Name:" + productName);
}
// 定义第三行
String c1 = "BOM Revision:" + version, c2 = "DATE:" + date, c3 = "Edit UserName :" + edituserName,
c4 = "Approved :";
if (version.equals("")) {
c1 = "BOM Revision:";
}
if (date.equals("")) {
c2 = "DATE:";
}
if (edituserName.equals("")) {
c3 = "Edit UserName :";
}
exportExcel.createNormalThreeRow(c1, c2, c3, c4);
// 定义第四行
exportExcel.createNormalFourRow(l, 8);
// 导入数据
exportExcel.createColumHeader(list);
// 输出文件流,把相应的Excel工作簿 输出到本地
exportExcel.outputExcel(excelPath);
}
public String importBomExcel(Map<String, Object> paramMap, String filePar) throws UnsupportedEncodingException {
Integer productid = Integer.parseInt((String) paramMap.get("productid"));
Data0017 data0017 = data0017Mapper.selectByKeyId(productid);
String productName = data0017.getInvPartDescriptionC();
String version = "";
if (data0017.getPeVersion() != null) {
version = data0017.getPeVersion();
}
String date = "";
if (data0017.getCreatetime() != null) {
date = data0017.getCreatetime().toString();
}
String edituserName = "";
if (data0017.getEditUserName() != null) {
edituserName = data0017.getEditUserName();
}
String filePathFather = createFile(filePar, productName);
Map<String, Object> checkParam = new HashMap();
checkParam.put("productid", productid);
checkParam.put("pageSize", 10);
checkParam.put("pageIndex", 0);
List<MaterielBom> bomList = queryBom(checkParam);
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
for (int i = 0; i < bomList.size(); i++) {
list.add(ObjectToMapUtil.object2Map(bomList.get(i)));
}
importBomExcelItem(list, filePathFather, productName, version, date, edituserName);
for (int j = 0; j < list.size(); j++) {
String bomType = list.get(j).get("bomType").toString();
if (!"1".equals(bomType)) {
checkParam.put("productid", list.get(j).get("infoid").toString());
importBomExcel(checkParam, filePathFather);
}
}
return filePathFather;
}
// 导出bom excel表
String zipPath = "";
boolean count = true;
public String importExcel(Map<String, Object> paramMap, String filePar) throws Throwable {
importBomExcel(paramMap, filePar);
zipFile(zipPath);
File file = new File(zipPath);
deleteIt(file);
// return "http://localhost:8080/ybsjtc/"+filename+".zip";
return zipPath;
}
4、Controller代码:
// 导出BOM表
@ResponseBody
@RequestMapping(value = "exportBom", method = RequestMethod.POST)
public Map<String, Object> exportBom(@RequestParam Map<String, Object> paramMap, HttpServletRequest request)
throws Throwable {
String catalinaHome = System.getProperty("catalina.base");
String doenloadPath = null;
if (catalinaHome.contains("org.eclipse.wst.server.core")) {
doenloadPath = catalinaHome + File.separator + "wtpwebapps";
} else {
doenloadPath = catalinaHome + File.separator + "webapps";
}
doenloadPath = doenloadPath + File.separator + "ybsjtc" + File.separator + "resource" + File.separator
+ "download" + File.separator;
Map<String, Object> result = new HashMap<String, Object>();
String path = materielBomService.importExcel(paramMap, doenloadPath);
result.put("data", path);
return result;
}