POI导出
程序员文章站
2024-03-20 21:49:10
...
controller:mezura-sa UserAccountController
/**
* 导出会员账户列表
* @param userAccountVo
* @return
*/
@RequestMapping(BranchUrl.ACCOUNT_EXPORT)
public PageData ExportUserAccountList(UserAccountVo userAccountVo){
PageData pageData = PageData.getInstanceOfSuccess(ConstTipCode.EXPORT_SUCCESS);
Boolean isBranchcom = Boolean.FALSE ;
CommonSessionVo sysUser = SessionUtil.getSysUserFromSession();
if(sysUser.getRoleLevel()== ConstDic.DIC_ROLE_LEVEL_BRANCHCOM){
userAccountVo.setCountryId(sysUser.getCountryId());
isBranchcom = Boolean.TRUE ;
}
if(userAccountVo.getCountryId() == null){
userAccountVo.setCountryId(sysUser.getCountryId()); }
try {
int count = userAccountService.getUserAccountListNum(userAccountVo);
if(count == 0){
return PageData.getInstanceOfError(ConstTipCode.EXPORT_NO_DATA,"无数据,不允许导出");
}
//文件名
String fileName = "userAccountInfo" + DateUtils.formatDate(new Date());
String filePath = userAccountService.exportUserAccountList(isBranchcom,count, fileName,userAccountVo);
pageData.getResultMap().put("filePath", filePath);
pageData.getResultMap().put("fileName", fileName);
return pageData;
} catch (Exception e) {
return processException(e);
}
}
service:
/**
* 会员账户导出
*
* @param isBranchcom
* @param fileName
* @param userAccountVo
* @throws Exception
*/
public String exportUserAccountList(Boolean isBranchcom, int count, String fileName, UserAccountVo userAccountVo) throws Exception {
String templateRootPath = sysConfig.getFilePath();
String filePath = null;
String path = templateRootPath + File.separator + Const.TEMPLATE_EXCEL_USERACCOUNT;
if (isBranchcom) {//是公司级别,取没有国家字段的模板
path = FileUtils.AddSuffix(path, Const.TEMPLATE_EXCEL_SUFFIX, Const.TEMPLATE_EXCEL_BRANCHCOM);
}else {
//模版名称+国家编码做为唯一标识
String countryCode =sysCountryMapper.selectByPrimaryKey(userAccountVo.getCountryId()).getCountryCode();
String[] a =Const.TEMPLATE_EXCEL_USERACCOUNT.split("\\.");
String excelName=a[0];
path =templateRootPath+File.separator+excelName+countryCode+Const.TEMPLATE_EXCEL_SUFFIX;
}
File excelFile = new File(path);
if (!excelFile.exists()) {
throw new BusinessException(ConstTipCode.EXPORT_FAIL, path + ",模板文件未找到");
}
HSSFWorkbook workbook = null;
InputStream inputStream = null;
try {
inputStream = new FileInputStream(excelFile);
workbook = new HSSFWorkbook(inputStream);
} catch (IOException e) {
throw new BusinessException(ConstTipCode.EXPORT_FAIL, path + ",模板文件读取异常");
} finally {
if (inputStream != null) {
inputStream.close();
}
}
HSSFSheet sourceSheet = workbook.getSheetAt(0);
int pageNum = 1;
if (count % Const.SHEET_NUM == 0) {
pageNum = count / Const.SHEET_NUM;
} else {
pageNum = (count / Const.SHEET_NUM) + 1;
}
for (int page = 1; page <= pageNum; page++) {
if (workbook.getSheet("sheet" + page) == null) {
workbook.createSheet("sheet" + page);
}
HSSFSheet hssfSheet = workbook.getSheetAt(page - 1);//目标sheet
if (page > 1) {
PoiUtils.copyRows(sourceSheet, hssfSheet, 1, 1, 1);
}
int positionIndex = 2;
int rowSize = 0;
List<UserAccountVo> exportUserAccountList = getExportUserAccountList(userAccountVo, page);
for (UserAccountVo userAccount : exportUserAccountList) {
int rowIndex = (positionIndex) + rowSize;
PoiUtils.copyRows(sourceSheet, hssfSheet, 1, 1, rowIndex);
HSSFRow hssfRow = PoiUtils.getHSSFRow(hssfSheet, rowIndex);
PoiUtils.getHSSFCell(hssfRow, 1).setCellValue(rowSize + 1);
if (!isBranchcom) {
PoiUtils.getHSSFCell(hssfRow, 2).setCellValue(userAccount.getCountryName());
}
// PoiUtils.getHSSFCell(hssfRow,2).setCellValue(userAccount.getUserId());
PoiUtils.getHSSFCell(hssfRow, 3).setCellValue(userAccount.getUserName());
PoiUtils.getHSSFCell(hssfRow, 4).setCellValue(userAccount.getEcTotal());
PoiUtils.getHSSFCell(hssfRow, 5).setCellValue(userAccount.getEcUsable());
PoiUtils.getHSSFCell(hssfRow, 6).setCellValue(userAccount.getEcFrozen());
PoiUtils.getHSSFCell(hssfRow, 7).setCellValue(userAccount.getCreditTotal());
PoiUtils.getHSSFCell(hssfRow, 8).setCellValue(userAccount.getCreditUsable());
PoiUtils.getHSSFCell(hssfRow, 9).setCellValue(userAccount.getCreditFrozen());
rowSize = rowSize + 1;
}
}
//输出Excel文件
String exportPath = sysConfig.getExportFilePath();
String currentDateStr = new SimpleDateFormat("yyyyMMdd").format(new Date());
filePath = exportPath + currentDateStr + "/" + fileName + ".xls";
File file = new File(exportPath + currentDateStr);
if (!file.exists() && !file.isDirectory()) {
file.mkdirs();
}
FileOutputStream output = null;
try {
output = new FileOutputStream(filePath);
workbook.write(output);
output.close();
} catch (IOException e) {
throw new BusinessException(ConstTipCode.EXPORT_FAIL, filePath + ",模板导出异常");
} finally {
if (output != null) {
output.close();
}
}
return filePath;
}
依赖的POI工具类:
package com.mezura.core.utils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.Region;
import org.apache.poi.xssf.usermodel.XSSFCell;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
/**
* Created by Administrator on 2016/11/29.
*/
public class PoiUtils {
public static void copyCell(HSSFCell templet_cell, HSSFCell new_cell, String value){
HSSFCellStyle templet_style = templet_cell.getCellStyle();
new_cell.setCellStyle(templet_style);
new_cell.setCellValue(value);
}
public static void copyCell(HSSFCell templet_cell, HSSFCell new_cell, Integer value){
HSSFCellStyle templet_style = templet_cell.getCellStyle();
new_cell.setCellStyle(templet_style);
new_cell.setCellValue(value);
}
public static void copyCell(HSSFCell templet_cell, HSSFCell new_cell, Double value){
HSSFCellStyle templet_style = templet_cell.getCellStyle();
new_cell.setCellStyle(templet_style);
new_cell.setCellValue(value);
}
public static void setAndCreateCellValue(HSSFRow row_new, HSSFCell original_cell , int column, String value){
HSSFCell row_newcell = row_new.createCell(column);
PoiUtils.copyCell(original_cell, row_newcell, value);
}
public static void setAndCreateCellValue(HSSFRow row_new, HSSFCell original_cell , int column, Double value){
HSSFCell row_newcell = row_new.createCell(column);
PoiUtils.copyCell(original_cell, row_newcell, value);
}
public static void setAndCreateCellValue(HSSFRow row_new, HSSFCell original_cell , int column, Integer value){
HSSFCell row_newcell = row_new.createCell(column);
PoiUtils.copyCell(original_cell, row_newcell, value);
}
/**
* 获取excel行
* @param hssfSheet
* @param rowNum 行数(1开始)
* @return
*/
public static HSSFRow getHSSFRow(HSSFSheet hssfSheet,int rowNum){
HSSFRow hssfRow = hssfSheet.getRow(rowNum-1);
return hssfRow;
}
/**
* 获取excel列
* @param hssfRow
* @param cellNum 列数(1开始)
* @return
*/
public static HSSFCell getHSSFCell(HSSFRow hssfRow,int cellNum){
HSSFCell hssfCell = hssfRow.getCell(cellNum-1);
return hssfCell;
}
/**
* @desc 删除指定行,达到删行移动的效果,目前只能是一行一行的删
* @param sheet
* @param rowIndex
*/
public static void removeRow(HSSFSheet sheet, int rowIndex) {
int lastRowNum=sheet.getLastRowNum();
if(rowIndex>=0&&rowIndex<lastRowNum)
sheet.shiftRows(rowIndex+1,lastRowNum,-1);//将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行,以便删除rowIndex行
if(rowIndex==lastRowNum){
HSSFRow removingRow=sheet.getRow(rowIndex);
if(removingRow!=null)
sheet.removeRow(removingRow);
}
}
/**
* @desc 获取所有sheet名称列表
* @param workbook
*/
public static List<String> getSheetNameList(HSSFWorkbook workbook) {
List<String> sheetNameList = new ArrayList<>();
for(int i = 0; i < workbook.getNumberOfSheets(); i++) {
HSSFSheet sheet=workbook.getSheetAt(i);
String hospitalName = sheet.getSheetName();
sheetNameList.add(hospitalName);
}
return sheetNameList;
}
public static String getStringCellValue(XSSFCell cell){
String result = "";
if(null == cell){
return result;
}
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case XSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat TIME_FORMATTER = new SimpleDateFormat("yyyyMMdd");
result = TIME_FORMATTER.format(cell.getDateCellValue());
}
else{
double doubleValue = cell.getNumericCellValue();
// 是否为数值型
if (doubleValue - (int) doubleValue < Double.MIN_VALUE) {
// 是否为int型
result = Integer.toString((int) doubleValue);
} else {
result= new HSSFDataFormatter().formatCellValue(cell);
}
}
break;
case XSSFCell.CELL_TYPE_STRING:
if (cell.getStringCellValue() == null){
result = null;
}
else{
result = cell.getStringCellValue();
}
break;
case XSSFCell.CELL_TYPE_BLANK:
break;
case XSSFCell.CELL_TYPE_FORMULA:
try{
result = String.valueOf(cell.getNumericCellValue());
}catch(Exception e){
result = cell.getStringCellValue();
}
break;
default:
result = "";
}
return result;
}
public static String getStringCellValue(HSSFCell cell){
String result = "";
if(null == cell){
return result;
}
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
result =dateFormat.format(cell.getDateCellValue());
}else {
double doubleValue = cell.getNumericCellValue();
// 是否为数值型
if (doubleValue - (int) doubleValue < Double.MIN_VALUE) {
// 是否为int型
result = Integer.toString((int) doubleValue);
} else {
result= new HSSFDataFormatter().formatCellValue(cell);
}
}
break;
case HSSFCell.CELL_TYPE_STRING:
if (cell.getStringCellValue() == null){
result = null;
}
else{
result = cell.getStringCellValue();
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_FORMULA:
try{
result = String.valueOf(cell.getNumericCellValue());
}catch(Exception e){
result = cell.getStringCellValue();
}
break;
default:
result = "";
}
result=result.trim();
return result;
}
public static String getStringCellValue(Cell cell){
String result = "";
if(null == cell){
return result;
}
switch (cell.getCellType()) {
case Cell.CELL_TYPE_BOOLEAN:
result = String.valueOf(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm");
result =dateFormat.format(cell.getDateCellValue());
}else {
// 是否为数值型
double doubleValue = cell.getNumericCellValue();
if (doubleValue - (int) doubleValue < Double.MIN_VALUE) {
// 是否为int型
result = Integer.toString((int) doubleValue);
} else {
result= new HSSFDataFormatter().formatCellValue(cell);
}
}
break;
case Cell.CELL_TYPE_STRING:
if (cell.getStringCellValue() == null){
result = null;
}
else{
result = cell.getStringCellValue();
}
break;
case Cell.CELL_TYPE_BLANK:
break;
case Cell.CELL_TYPE_FORMULA:
try{
result = String.valueOf(cell.getNumericCellValue());
}catch(Exception e){
result = cell.getStringCellValue();
}
break;
default:
result = "";
}
result=result.trim();
return result;
}
/**
* 拷贝行
* @param sourceSheet
* @param targetSheet
* @param startRow
* @param endRow
* @param targetPosition
*/
public static int copyRows(HSSFSheet sourceSheet, HSSFSheet targetSheet, int startRow, int endRow, int targetPosition) {
int pStartRow = startRow - 1;
int pEndRow = endRow - 1;
int pPosition = targetPosition - 1;
if(targetSheet.getLastRowNum()>targetPosition){
targetSheet.shiftRows(targetPosition, targetSheet.getLastRowNum(),pEndRow-pStartRow+1,true,false);
}
HSSFRow sourceRow = null, targetRow = null;
HSSFCell sourceCell = null, targetCell = null;
Region region = null;
int cType, i, j, targetRowFrom, targetRowTo;
if (pStartRow < 0 || pEndRow < 0 || pStartRow > pEndRow) {
return 0;
}
// 获取合并行单元格的数目,并设定目标单元格合并属性
int mergedc = sourceSheet.getNumMergedRegions();
for (i = 0; i < mergedc; i++) {
region = sourceSheet.getMergedRegionAt(i);
int rf = region.getRowFrom();
int rt = region.getRowTo();
if ((rf >= pStartRow) && (rt <= pEndRow)) {
targetRowFrom = rf - pStartRow + pPosition;
targetRowTo = rt - pStartRow + pPosition;
region.setRowFrom(targetRowFrom);
region.setRowTo(targetRowTo);
targetSheet.addMergedRegion(region);
}
}
//设定个单元格的列宽
for (i = pStartRow; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow != null) {
int firstC = sourceRow.getFirstCellNum();
for (j = sourceRow.getLastCellNum(); j > firstC; j--) {
targetSheet
.setColumnWidth(j, sourceSheet.getColumnWidth(j));
targetSheet.setColumnHidden(j, false);
}
break;
}
}
//填充数据
for (; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.createRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
int psy = sourceRow.getPhysicalNumberOfCells();
for (j = sourceRow.getFirstCellNum(); j < psy; j++) {
sourceCell = sourceRow.getCell(j);
if (sourceCell == null) {
continue;
}
targetCell = targetRow.createCell(j);
targetCell.setCellStyle(sourceCell.getCellStyle());
cType = sourceCell.getCellType();
targetCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell
.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
targetCell.setCellFormula(parseFormula(sourceCell
.getCellFormula()));
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell
.setCellValue(sourceCell.getRichStringCellValue());
break;
}
}
}
return endRow-startRow+1;
}
/**
* 拷贝行样式
* @param sourceSheet
* @param targetSheet
* @param sourcePosition
* @param targetPosition
*/
public static void copyRowStyle(HSSFSheet sourceSheet, HSSFSheet targetSheet, int sourcePosition, int targetPosition) {
int pStartRow = sourcePosition - 1;
int pEndRow = pStartRow;
int pPosition = targetPosition - 1;
HSSFRow sourceRow = null, targetRow = null;
HSSFCell sourceCell = null, targetCell = null;
Region region = null;
int cType, i, j, targetRowFrom, targetRowTo;
if (pStartRow < 0 || pEndRow < 0 || pStartRow > pEndRow) {
return;
}
// 获取合并行单元格的数目,并设定目标单元格合并属性
int mergedc = sourceSheet.getNumMergedRegions();
for (i = 0; i < mergedc; i++) {
region = sourceSheet.getMergedRegionAt(i);
int rf = region.getRowFrom();
int rt = region.getRowTo();
if ((rf >= pStartRow) && (rt <= pEndRow)) {
targetRowFrom = rf - pStartRow + pPosition;
targetRowTo = rt - pStartRow + pPosition;
region.setRowFrom(targetRowFrom);
region.setRowTo(targetRowTo);
targetSheet.addMergedRegion(region);
}
}
//设定个单元格的列宽
for (i = pStartRow; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow != null) {
int firstC = sourceRow.getFirstCellNum();
for (j = sourceRow.getLastCellNum(); j > firstC; j--) {
targetSheet.setColumnWidth(j, sourceSheet.getColumnWidth(j));
targetSheet.setColumnHidden(j, false);
}
break;
}
}
//填充数据
for (; i <= pEndRow; i++) {
sourceRow = sourceSheet.getRow(i);
if (sourceRow == null) {
continue;
}
targetRow = targetSheet.getRow(i - pStartRow + pPosition);
targetRow.setHeight(sourceRow.getHeight());
int psy = sourceRow.getPhysicalNumberOfCells();
for (j = sourceRow.getFirstCellNum(); j < psy; j++) {
sourceCell = sourceRow.getCell(j);
targetCell = targetRow.getCell(j);
if (sourceCell == null||targetCell==null) {
continue;
}
targetCell.setCellStyle(sourceCell.getCellStyle());
cType = sourceCell.getCellType();
targetCell.setCellType(cType);
switch (cType) {
case HSSFCell.CELL_TYPE_BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_ERROR:
targetCell
.setCellErrorValue(sourceCell.getErrorCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
targetCell.setCellFormula(parseFormula(sourceCell
.getCellFormula()));
break;
case HSSFCell.CELL_TYPE_NUMERIC:
targetCell.setCellValue(sourceCell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_STRING:
targetCell
.setCellValue(sourceCell.getRichStringCellValue());
break;
}
}
}
}
private static String parseFormula(String pPOIFormula) {
final String cstReplaceString = "ATTR(semiVolatile)"; //$NON-NLS-1$
StringBuffer result = null;
int index;
result = new StringBuffer();
index = pPOIFormula.indexOf(cstReplaceString);
if (index >= 0) {
result.append(pPOIFormula.substring(0, index));
result.append(pPOIFormula.substring(index
+ cstReplaceString.length()));
} else {
result.append(pPOIFormula);
}
return result.toString();
}
public static void copyRows(int startRow, int endRow, int position, HSSFSheet sheet) {
int pStartRow = startRow - 1;
int pEndRow = endRow - 1;
int pPosition = position-1;
int targetRowFrom;
int targetRowTo;
int columnCount;
CellRangeAddress region = null;
int i;
int j;
if (pStartRow == -1 || pEndRow == -1) {
return;
}
// 拷贝合并的单元格
for (i = 0; i < sheet.getNumMergedRegions(); i++) {
region = sheet.getMergedRegion(i);
if ((region.getFirstRow() >= pStartRow)
&& (region.getLastRow() <= pEndRow)) {
targetRowFrom = region.getFirstRow() - pStartRow + pPosition;
targetRowTo = region.getLastRow() - pStartRow + pPosition;
CellRangeAddress newRegion = region.copy();
newRegion.setFirstRow(targetRowFrom);
newRegion.setFirstColumn(region.getFirstColumn());
newRegion.setLastRow(targetRowTo);
newRegion.setLastColumn(region.getLastColumn());
sheet.addMergedRegion(newRegion);
}
}
// 设置列宽
for (i = pStartRow; i <= pEndRow; i++) {
HSSFRow sourceRow = sheet.getRow(i);
columnCount = sourceRow.getLastCellNum();
if (sourceRow != null) {
HSSFRow newRow = sheet.createRow(pPosition - pStartRow + i);
newRow.setHeight(sourceRow.getHeight());
for (j = 0; j < columnCount; j++) {
HSSFCell templateCell = sourceRow.getCell(j);
if (templateCell != null) {
HSSFCell newCell = newRow.createCell(j);
copyCell(templateCell, newCell);
}
}
}
}
}
private static void copyCell(HSSFCell srcCell, HSSFCell distCell) {
distCell.setCellStyle(srcCell.getCellStyle());
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {
// nothing21
} else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
} else { // nothing29
}
}
}