概述
1.版本依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.8</version>
</dependency>
2.poi工具类
package com.cloud.user.util;
import com.cloud.framework.util.DateUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
public class MyPoiUtils {
private static final String REGEX = "[a-zA-Z]";
public static final int EXPORT_ROWS_MAX_INDEX = 65535;
private String title;
private String sheetName;
private String[] FixedRowname;
private int [] FixedRownameColumnWidth;
private List<List<Object>> dataList;
HttpServletResponse response;
private static String convertToMethodName(String attribute, Class<?> objClass, boolean isSet) {
Pattern p = Pattern.compile("[a-zA-Z]");
Matcher m = p.matcher(attribute);
StringBuilder sb = new StringBuilder();
if (isSet) {
sb.append("set");
} else {
try {
Field attributeField = objClass.getDeclaredField(attribute);
if (attributeField.getType() != Boolean.TYPE && attributeField.getType() != Boolean.class) {
sb.append("get");
} else {
sb.append("is");
}
} catch (SecurityException var7) {
var7.printStackTrace();
} catch (NoSuchFieldException var8) {
var8.printStackTrace();
}
}
if (attribute.charAt(0) != '_' && m.find()) {
sb.append(m.replaceFirst(m.group().toUpperCase()));
} else {
sb.append(attribute);
}
return sb.toString();
}
public static HSSFWorkbook create(Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("sheet");
sheet.setDefaultColumnWidth(15);
HSSFCellStyle style = wb.createCellStyle();
HSSFRow row = sheet.createRow(0);
style.setAlignment((short)2);
HSSFCell cell = null;
int i;
for(i = 0; i < titleMap.size(); ++i) {
cell = row.createCell(i);
cell.setCellValue((String)titleMap.get(i));
cell.setCellStyle(style);
if (dataMap.get(i) != null) {
if (((String[])dataMap.get(i)).length > 10) {
sheet = validationHidden(sheet, wb, (String[])dataMap.get(i), 1, 100, i, i);
} else {
sheet = validation(sheet, (String[])dataMap.get(i), 1, 65535, i, i);
}
}
}
if (dates != null && dates.length > 0) {
for(i = 0; i < dates.length; ++i) {
dataformat(wb, sheet, dates[i]);
}
}
return wb;
}
public static void create(OutputStream out, Map<Integer, String> titleMap, Map<Integer, String[]> dataMap, int[] dates) throws IOException {
create(titleMap, dataMap, dates).write(out);
}
public static void dataformat(HSSFWorkbook wb, HSSFSheet sheet, int Col) {
HSSFCellStyle hcs = wb.createCellStyle();
hcs.setDataFormat(wb.createDataFormat().getFormat("yyyy/m/d h:mm"));
sheet.setDefaultColumnStyle(Col, hcs);
}
private static List<Object> doImportExcel(String originUrl, int startRow, int endRow, boolean showInfo, Class<?> clazz) throws IOException {
File file = new File(originUrl);
if (!file.exists()) {
throw new IOException("文件名为" + file.getName() + "Excel文件不存在！");
} else {
HSSFWorkbook wb = null;
FileInputStream fis = null;
ArrayList rowList = new ArrayList();
try {
fis = new FileInputStream(file);
wb = new HSSFWorkbook(fis);
Sheet sheet = wb.getSheetAt(0);
int lastRowNum = sheet.getLastRowNum();
if (lastRowNum > 0) {
out("n开始读取名为【" + sheet.getSheetName() + "】的内容：", showInfo);
}
Row row = null;
for(int i = startRow; i <= lastRowNum + endRow; ++i) {
row = sheet.getRow(i);
if (row != null) {
rowList.add(row);
out("第" + (i + 1) + "行：", showInfo, false);
for(int j = 0; j < row.getLastCellNum(); ++j) {
String value = getCellValue(row.getCell(j));
if (!value.equals("")) {
out(value + " | ", showInfo, false);
}
}
out("", showInfo);
}
}
} catch (IOException var18) {
var18.printStackTrace();
} finally {
fis.close();
}
return returnObjectList(rowList, clazz);
}
}
private static String getCellValue(Cell cell) {
Object result = "";
if (cell != null) {
switch(cell.getCellType()) {
case 0:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
result = DateUtils.getInstance().dateFormat3(date);
} else {
result = cell.getNumericCellValue();
}
break;
case 1:
result = cell.getStringCellValue();
break;
case 2:
result = cell.getCellFormula();
case 3:
default:
break;
case 4:
result = cell.getBooleanCellValue();
break;
case 5:
result = cell.getErrorCellValue();
}
}
return result.toString();
}
public static List<?> importExcel(String originUrl, int startRow, int endRow, Class<?> clazz) throws IOException {
boolean showInfo = true;
return doImportExcel(originUrl, startRow, endRow, showInfo, clazz);
}
public static HttpServletResponse out(HttpServletResponse response, String filename) throws UnsupportedEncodingException {
response.setCharacterEncoding("utf-8");
response.setContentType("multipart/form-data");
response.setHeader("Content-Disposition", "attachment;fileName=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
return response;
}
private static void out(String info, boolean showInfo) {
if (showInfo) {
System.out.print(info + (showInfo ? "n" : ""));
}
}
private static void out(String info, boolean showInfo, boolean nextLine) {
if (showInfo) {
if (nextLine) {
System.out.print(info + (showInfo ? "n" : ""));
} else {
System.out.print(info);
}
}
}
private static List<Object> returnObjectList(List<Row> rowList, Class<?> clazz) {
List<Object> objectList = null;
Object obj = null;
String attribute = null;
String value = null;
boolean var6 = false;
try {
objectList = new ArrayList();
Field[] declaredFields = clazz.getDeclaredFields();
Iterator var8 = rowList.iterator();
while(var8.hasNext()) {
Row row = (Row)var8.next();
int j = 0;
obj = clazz.newInstance();
Field[] var10 = declaredFields;
int var11 = declaredFields.length;
for(int var12 = 0; var12 < var11; ++var12) {
Field field = var10[var12];
attribute = field.getName().toString();
value = getCellValue(row.getCell(j));
setAttrributeValue(obj, attribute, value);
++j;
}
objectList.add(obj);
}
} catch (Exception var14) {
var14.printStackTrace();
}
return objectList;
}
private static void setAttrributeValue(Object obj, String attribute, String value) {
String method_name = convertToMethodName(attribute, obj.getClass(), true);
Method[] methods = obj.getClass().getMethods();
Method[] var5 = methods;
int var6 = methods.length;
for(int var7 = 0; var7 < var6; ++var7) {
Method method = var5[var7];
if (method.getName().equals(method_name)) {
Class[] parameterC = method.getParameterTypes();
try {
if (parameterC[0] != Integer.TYPE && parameterC[0] != Integer.class) {
if (parameterC[0] != Float.TYPE && parameterC[0] != Float.class) {
if (parameterC[0] != Double.TYPE && parameterC[0] != Double.class) {
if (parameterC[0] != Byte.TYPE && parameterC[0] != Byte.class) {
if (parameterC[0] != Boolean.TYPE && parameterC[0] != Boolean.class) {
if (parameterC[0] == Date.class) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
Date date = null;
try {
if (!StringUtils.isEmpty(value)) {
date = sdf.parse(value);
}
} catch (Exception var13) {
var13.printStackTrace();
}
method.invoke(obj, date);
} else {
method.invoke(obj, parameterC[0].cast(value));
}
break;
}
method.invoke(obj, Boolean.valueOf(value));
break;
}
method.invoke(obj, Byte.valueOf(value));
break;
}
method.invoke(obj, Double.valueOf(value));
break;
}
method.invoke(obj, Float.valueOf(value));
break;
}
value = value.substring(0, value.lastIndexOf("."));
method.invoke(obj, Integer.valueOf(value));
break;
} catch (IllegalArgumentException var14) {
var14.printStackTrace();
} catch (IllegalAccessException var15) {
var15.printStackTrace();
} catch (InvocationTargetException var16) {
var16.printStackTrace();
} catch (SecurityException var17) {
var17.printStackTrace();
}
}
}
}
public static HSSFSheet validation(HSSFSheet sheet, String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
DVConstraint constraint = DVConstraint.createExplicitListConstraint(strFormulaArray);
CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(regions, constraint);
sheet.addValidationData(validation);
return sheet;
}
public static HSSFSheet validationHidden(HSSFSheet sheet, HSSFWorkbook wb, String[] strFormulaArray, int firstRow, int endRow, int firstCol, int endCol) {
String hiddenSheet = "hidden" + firstCol;
HSSFSheet hidden = wb.createSheet(hiddenSheet);
int i = 0;
for(int length = strFormulaArray.length; i < length; ++i) {
hidden.createRow(endRow + i).createCell(firstCol).setCellValue(strFormulaArray[i]);
}
HSSFName name = wb.createName();
name.setNameName(hiddenSheet);
name.setRefersToFormula(hiddenSheet + "!A1:A" + (strFormulaArray.length + endRow));
DVConstraint constraint = DVConstraint.createFormulaListConstraint(hiddenSheet);
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
HSSFDataValidation validation = new HSSFDataValidation(addressList, constraint);
wb.setSheetHidden(1, true);
sheet.addValidationData(validation);
return sheet;
}
public MyPoiUtils(String title, String[] fixedRowname, List<List<Object>> dataList) {
this(title, "sheet",fixedRowname, null, dataList);
}
public MyPoiUtils(String title, String[] fixedRowname,int[] fixedRownameColumnWidth , List<List<Object>> dataList) {
this(title, "sheet", fixedRowname,fixedRownameColumnWidth , dataList);
}
public MyPoiUtils(String title, String sheetName, String[] fixedRowname, int [] fixedRownameColumnWidth ,List<List<Object>> dataList) {
this.dataList = new ArrayList();
this.title = title;
this.sheetName = sheetName;
this.FixedRowname = fixedRowname;
this.dataList = dataList;
this.FixedRownameColumnWidth=fixedRownameColumnWidth;
}
public HSSFWorkbook export() throws Exception {
try {
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(this.sheetName);
HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
HSSFCellStyle style = this.getStyle(workbook);
int fixedColumnNum = this.FixedRowname.length;
HSSFRow rowRowTitle = sheet.createRow(0);
if(this.FixedRownameColumnWidth == null ){
sheet.setDefaultColumnWidth(10);
}else{
int fixedRownameColumnWidthNum=this.FixedRownameColumnWidth.length;
for(int i=0; i<fixedRownameColumnWidthNum;++i){
sheet.setColumnWidth(i,this.FixedRownameColumnWidth[i]);
}
}
for(int i = 0; i < fixedColumnNum; ++i) {
HSSFCell titleCell = rowRowTitle.createCell(i);
titleCell.setCellType(1);
if (i == 0) {
titleCell.setCellValue(this.title);
}
titleCell.setCellStyle(columnTopStyle);
}
sheet.addMergedRegion(new Region(0, (short)0, 0, (short)(fixedColumnNum - 1)));
HSSFRow rowRowName = sheet.createRow(1);
int i;
for(i = 0; i < fixedColumnNum; ++i) {
HSSFCell fixedCellRowName = rowRowName.createCell(i);
fixedCellRowName.setCellType(1);
HSSFRichTextString text = new HSSFRichTextString(this.FixedRowname[i]);
fixedCellRowName.setCellValue(text);
fixedCellRowName.setCellStyle(columnTopStyle);
}
for(i = 0; i < this.dataList.size(); ++i) {
List<Object> obj = (List)this.dataList.get(i);
HSSFRow row = sheet.createRow(i + 2);
for(int j = 0; j < obj.size(); ++j) {
HSSFCell cell = row.createCell(j, 0);
cell.setCellValue(String.valueOf(obj.get(j)));
cell.setCellStyle(style);
}
}
return workbook;
} catch (Exception var13) {
var13.printStackTrace();
return null;
}
}
public void export(OutputStream out) throws Exception {
this.export().write(out);
}
public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short)11);
font.setBoldweight((short)700);
font.setFontName("Courier New");
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom((short)1);
style.setBottomBorderColor((short)8);
style.setBorderLeft((short)1);
style.setLeftBorderColor((short)8);
style.setBorderRight((short)1);
style.setRightBorderColor((short)8);
style.setBorderTop((short)1);
style.setTopBorderColor((short)8);
style.setFont(font);
style.setWrapText(false);
style.setAlignment((short)2);
style.setVerticalAlignment((short)1);
return style;
}
public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
HSSFFont font = workbook.createFont();
font.setFontName("Courier New");
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom((short)1);
style.setBottomBorderColor((short)8);
style.setBorderLeft((short)1);
style.setLeftBorderColor((short)8);
style.setBorderRight((short)1);
style.setRightBorderColor((short)8);
style.setBorderTop((short)1);
style.setTopBorderColor((short)8);
style.setFont(font);
style.setWrapText(false);
style.setAlignment((short)2);
style.setVerticalAlignment((short)1);
return style;
}
}2.1设计工具类
DateUtils
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.cloud.framework.util;
import java.text.SimpleDateFormat;
import java.util.Date;
public class DateUtils {
private static DateUtils instance;
public DateUtils() {
}
public static DateUtils getInstance() {
if (instance == null) {
instance = new DateUtils();
}
return instance;
}
public long dateformat(String datestr) throws Exception {
return this.dateformat(datestr, "yyyy-MM-dd HH:mm:ss").getTime();
}
public Date dateformat(String datestr, String format) throws Exception {
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.parse(datestr);
}
public String dateFormat() {
return this.dateFormat(System.currentTimeMillis());
}
public String dateFormat(Date date) {
return this.dateFormat(date, "yyyyMMddHHmmssSSS");
}
public String dateFormat(Date date, String format) {
SimpleDateFormat sdf = new SimpleDateFormat(format);
return sdf.format(date);
}
public String dateFormat(Long currentTimeMillis) {
return this.dateFormat(new Date(currentTimeMillis), "yyyy/MM/dd");
}
public String dateformat2(String datestr) throws Exception {
return String.valueOf(this.dateformat(datestr));
}
public String dateFormat2(Date date) {
return this.dateFormat(date, "yyyyMMddHHmmss");
}
public String dateFormat3(Date date) {
return this.dateFormat(date, "yyyy-MM-dd HH:mm:ss");
}
public String dateFormat4(Date date) {
return this.dateFormat(date, "yyyy-MM-dd HH:mm:ss:SSS");
}
public String dateFormat5(Date date) {
return this.dateFormat(date, "yyyy");
}
public String dateFormat6() {
return this.dateFormat(new Date(System.currentTimeMillis()), "yyyy/MM/dd HH:mm:ss");
}
public String dateFormat6(Date date) {
return this.dateFormat(date, "yyyy/MM/dd HH:mm:ss");
}
public String dateFormat7(Date date) {
return this.dateFormat(date, "yyyy-MM-dd HH:mm:ss");
}
public long dateformat8(String datestr) throws Exception {
return this.dateformat(datestr, "yyyy/MM/dd HH:mm:ss").getTime();
}
public long dateformat9(String datestr) throws Exception {
return this.dateformat(datestr, "yyyyMMddHHmmss").getTime();
}
}
3.使用
controller
//支持 orgId searchName startTime endTime
@ApiOperation(value = "账户管理账单明细导出", notes = "")
@ApiImplicitParams({@ApiImplicitParam(name = "pd", value = "参数条件", dataType = "PageData")})
@RequestMapping(value = "/exportAccountDetaiBill", method = RequestMethod.GET)
public void exportAccountDetaiBill(HttpServletResponse response, CostDownLoadVo vo) {
PageData param = new PageData();
param.put("orgId", vo.getOrgId());
param.put("searchName", vo.getSearchName());
param.put("startTime", vo.getStartTime());
param.put("endTime", vo.getEndTime());
costSetService.exportAccountDetaiBill(response, param);
}
service
@Override
public void exportAccountDetaiBill(HttpServletResponse response, PageData param) {
List<CostBillDetailed> list = this.getAccountDetaiBillList(param);
String orgTopName = "";
if (!org.springframework.util.CollectionUtils.isEmpty(list)) {
orgTopName = getSplitByIndx(list.get(0).getOrgTopName(), 1);
}
OutputStream os = null;
try {
//标题 此处为国际化 根据自己业务需求，如果没有国际化可以直接不加
String[] headers = sysContext.getLang().contains("en") ? ConstantUtils.EXPORT_ACCOUNT_DETAI_BILL_HEADER_EN : ConstantUtils.EXPORT_ACCOUNT_DETAI_BILL_HEADER_ZH;
//数据集合
List<List<Object>> outList = new ArrayList<List<Object>>();
BigDecimal detaiBillMoenySum = NumberUtils.getBigDecimalInstans();
for (CostBillDetailed csd : list) {
List<Object> inList = new ArrayList<Object>();
BigDecimal workPrice = csd.getWorkPrice();
inList.add(csd.getRealName() == null ? "" : csd.getRealName());
inList.add(csd.getOrgTopName() == null ? "" : csd.getOrgTopName());
inList.add(csd.getOpenTime() == null ? "" : DateUtils.getInstance().dateFormat3(csd.getOpenTime()));
inList.add(csd.getCloseTime() == null ? "" : DateUtils.getInstance().dateFormat3(csd.getCloseTime()));
inList.add(csd.getWorkHours() == null ? 0 : csd.getWorkHours().doubleValue());
inList.add(workPrice == null ? 0 : workPrice);
inList.add(csd.getContent() == null ? "" : csd.getContent());
detaiBillMoenySum = NumberUtils.getResultAdd(detaiBillMoenySum, workPrice);
outList.add(inList);
}
//合计
List<Object> inList = new ArrayList<Object>();
inList.add("");
inList.add("");
inList.add("");
inList.add("");
inList.add("");
inList.add(detaiBillMoenySum);
inList.add("");
outList.add(inList);
response.reset();
response.setCharacterEncoding("UTF-8");
response.setContentType("multipart/form-data");
String fileName = "exportAccountDetaiBill.xls";
response.setHeader("Content-Disposition",
"attachment;fileName=" + new String(fileName.getBytes(StandardCharsets.UTF_8), "ISO8859-1"));
//设置每列宽度
int [] FixedRownameColumnWidth={10*256,35*256,25*256,25*256,15*256,15*256,60*256,};
MyPoiUtils excelSoftWare = new MyPoiUtils(orgTopName + "账单明细_" + DateUtils.getInstance().dateFormat(new Date(), "yyyy-MM-dd"), headers,FixedRownameColumnWidth, outList);
os = response.getOutputStream();
excelSoftWare.export(os);
} catch (Exception e) {
e.printStackTrace();
logger.info("/exportAccountDetaiBill 导出出现异常：", e);
} finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
常量
package com.cloud.common.util;
import io.lions.security.encryption.bidirectional.DESede;
import java.util.*;
/**
* 常量工具类
*/
public class ConstantUtils {
//计费管理 -用户账单明细连接信息 内容拼接
public static final String COSTBILLDETAILED_CONTENT_X1="使用";
public static final String COSTBILLDETAILED_CONTENT_X2="软件";
public static final String COSTBILLDETAILED_CONTENT_X3="使用存储容量";
public static final String COSTBILLDETAILED_CONTENT_XGB="GB";
public static final String COSTBILLDETAILED_CONTENT_X4="使用CPU";
public static final String COSTBILLDETAILED_CONTENT_X5="核";
public static final String COSTBILLDETAILED_CONTENT_X6="内存";
public static final String COSTBILLDETAILED_CONTENT_XG="G";
public static final String COSTBILLDETAILED_CONTENT_X7="GPU";
public static final String COSTBILLDETAILED_CONTENT_X8="，";
public static final String COSTBILLDETAILED_CONTENT_X9="、";
//计费管理 -用户账单明细导出 列
public static String[] EXPORT_ACCOUNT_DETAI_BILL_HEADER_ZH = {"用户名称","隶属部门","开始使用时间","结束使用时间","应用时长(h)","费用","扣费内容"};
public static String[] EXPORT_ACCOUNT_DETAI_BILL_HEADER_EN = {"User name","Subordinate departmen","Start time","End use time","Application duration(h)","cost","Deduction content"};
//计费管理 -计费统计概览导出 列
public static String[] EXPORT_ACCOUNT_COST_INDEX_ZH = {"计费统计名称","金额（元）"};
public static String[] EXPORT_ACCOUNT_COST_INDEX_EN = {"Billing statistics name","Amount (yuan)"};
}
效果
最后
以上就是积极灰狼为你收集整理的poi工具类1.版本依赖2.poi工具类 3.使用效果的全部内容，希望文章能够帮你解决poi工具类1.版本依赖2.poi工具类 3.使用效果所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复