excel文档导出

最新推荐文章于 2025-08-21 15:44:32 发布

原创最新推荐文章于 2025-08-21 15:44:32 发布 · 337 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

工具类专栏收录该内容

8 篇文章

订阅专栏

excel导出工具类

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;
import java.util.List;

/**
* created by zhangyingbin on 2019/10/11 0011 上午 10:59
* description: excel导入导出工具类
*/
public class ExcelUtil {

/**
* @create: zhangyingbin 2019/10/11 0011 上午 11:00
* @Modifier:
* @Description: 解析Excel
*/
public static <T> List<T> parserExcel(MultipartFile file, int header, Class<T> clazz, String[] beanPropertys) {
// 得到workbook
List<T> list = new ArrayList<T>();
try {
Workbook workbook = WorkbookFactory.create(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
int rowSize = sheet.getPhysicalNumberOfRows();
// 去除头行
if (rowSize > header) {
for (int i = header; i < rowSize; i++) {
T t = clazz.newInstance();
Row row = sheet.getRow(i);
for (int j = 0; j < beanPropertys.length; j++) {
Object cellValue = getCellValue(row.getCell(j));
org.apache.commons.beanutils.BeanUtils.copyProperty(t, beanPropertys[j], cellValue);
}
list.add(t);
}
}

} catch (Exception e) {
e.printStackTrace();
}
return list;

}

/**
* 读取excel单元格
*
* @param cell
* @return
*/
private static Object getCellValue(Cell cell) {
Object result = null;
if (cell != null) {
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
result = cell.getStringCellValue();
break;
case Cell.CELL_TYPE_NUMERIC:
// 对日期进行判断和解析
if (HSSFDateUtil.isCellDateFormatted(cell)) {
double cellValue = cell.getNumericCellValue();
result = HSSFDateUtil.getJavaDate(cellValue);
} else {

//                        result = String.valueOf(cell.getNumericCellValue()).split("\\.");
                        result = cell.getNumericCellValue();

                        // 是否为数值型
                        double d = cell.getNumericCellValue();
                        if (d - (int) d < Double.MIN_VALUE) {
                            // 是否为int型
                            result = Integer.toString((int) d);
                        } else {
                            System.out.println("double.....");
                            // 是否为double型
                            // result = Double.toString(cell.getNumericCellValue());
                            DecimalFormat df = new DecimalFormat("#");

                            result = df.format(cell.getNumericCellValue());
                        }

}
break;
case Cell.CELL_TYPE_BOOLEAN:
result = cell.getBooleanCellValue();
break;
case Cell.CELL_TYPE_FORMULA:
result = cell.getCellFormula();
break;
case Cell.CELL_TYPE_ERROR:
result = cell.getErrorCellValue();
break;
case Cell.CELL_TYPE_BLANK:
break;
default:
break;
}
}
return result;
}

/**
* @create: zhangyingbin 2019/10/11 0011 上午 11:13
* @Modifier:
* @Description: 导出Excel
*/
public static HSSFWorkbook export(String sheetName, String title, List<String> keys, List<List<String>> values) {
// 创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook wb = new HSSFWorkbook();
// 建立新的sheet对象（excel的表单）
HSSFSheet sheet = wb.createSheet(sheetName);

// 在sheet里创建第二行
HSSFRow row2 = null;

if (title != null && !StringUtils.isEmpty(title)) {
    // 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
    HSSFRow row1 = sheet.createRow(0);
    // 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个

    HSSFCell cell = row1.createCell(0);
    // 设置单元格内容
    cell.setCellValue(title);
    // 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列，截至列
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, keys.size()));
    row2 = sheet.createRow(1);
} else {
    row2 = sheet.createRow(0);
}

// 创建单元格并设置单元格内容
for (int i = 0; i < keys.size(); i++) {
row2.createCell(i).setCellValue(keys.get(i));
}
for (int i = 0; i < values.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
List<String> rowValue = values.get(i);
for (int j = 0; j < rowValue.size(); j++) {
row.createCell(j).setCellValue(rowValue.get(j));
}
}
return wb;
}

/**
* @create: zhangyingbin 2019/10/23 0023 上午 9:25
* @Modifier:
* @Description: 批量导出sheet到excel
*/
public static void bitchExport(HSSFWorkbook wb, String sheetName, String title, List<String> keys, List<List<String>> values) {
// 建立新的sheet对象（excel的表单）
HSSFSheet sheet = wb.createSheet(sheetName);
// 在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
HSSFRow row1 = sheet.createRow(0);
// 创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
HSSFCell cell = row1.createCell(0);
// 设置单元格内容
cell.setCellValue(title);
// 合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列，截至列
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, keys.size()));
// 在sheet里创建第二行
HSSFRow row2 = sheet.createRow(1);
// 创建单元格并设置单元格内容
for (int i = 0; i < keys.size(); i++) {
row2.createCell(i).setCellValue(keys.get(i));
}
for (int i = 0; i < values.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
List<String> rowValue = values.get(i);
for (int j = 0; j < rowValue.size(); j++) {
row.createCell(j).setCellValue(rowValue.get(j));
}
}
}
}

实例：

导入：

@Override
public Result batchImportData(MultipartFile uploadFile) {
log.info("-------批量导入数据----------");
List<ChangeLoanParam> changeLoanParams = new ArrayList<>();
String[] beanPropertys = {"oldChannelName", "newChannelName", "bankName", "month", "count"};
changeLoanParams = ExcelUtil.parserExcel(uploadFile, 1, ChangeLoanParam.class, beanPropertys);

List<FailDataVo> failDataVoList = new ArrayList<>();
Result result = null;
for (ChangeLoanParam changeLoanParam : changeLoanParams) {
result = this.changeLoan(changeLoanParam);
if (result.getCode() != Result.SUCCESS_CODE) {
FailDataVo failDataVo = new FailDataVo();
failDataVo.setChangeLoanParam(changeLoanParam);
failDataVo.setMessage(result.getMessage());
failDataVoList.add(failDataVo);
log.info("该数据批量操作时导入失败：{} ，原因：{}", changeLoanParam, result.getMessage());
}
}
if (failDataVoList != null && failDataVoList.size() > 0) {
return new Result(Result.ERROR_CODE, "部分数据导入失败", failDataVoList);
} else {
return new Result(Result.SUCCESS_CODE, "操作成功", null);
}
}

导出：

/**
* @create: zhangyingbin 2019/10/23 0023 上午 9:37
* @Modifier:
* @Description: 根据新老渠道及月份导出银行结算数据
*/
@Override
public void expotrBankSettleDatas(MultipartFile uploadFile, HttpServletRequest request, HttpServletResponse response) {
List<ChangeLoanParam> changeLoanParams = new ArrayList<>();
String[] beanPropertys = {"oldChannelName", "newChannelName", "bankName", "month", "count"};
changeLoanParams = ExcelUtil.parserExcel(uploadFile, 1, ChangeLoanParam.class, beanPropertys);

List<ExportOldDatasDto> exportNewChannelDatasDtoList = new ArrayList<>();
List<ExportOldDatasDto> exportOldChannelDatasDtoList = new ArrayList<>();
for (ChangeLoanParam changeLoanParam : changeLoanParams) {
//调整查询日期
DateTimeFormatter pattern = DateTimeFormatter.ofPattern("yyyy-MM-dd");
String beginDate = changeLoanParam.getMonth() + "-01";
String endDate = LocalDate.parse(beginDate, pattern).plusMonths(1).minusDays(1).format(pattern);

List<ExportOldDatasDto> exportOldDatasDtos = otherMapper.selectBankSettleByChannel(changeLoanParam.getBankName(), changeLoanParam.getMonth(), changeLoanParam.getNewChannelName(), beginDate, endDate);
exportNewChannelDatasDtoList.addAll(exportOldDatasDtos);
List<ExportOldDatasDto> exportOldDatasDtosOldChannel = otherMapper.selectBankSettleByChannel(changeLoanParam.getBankName(), changeLoanParam.getMonth(), changeLoanParam.getOldChannelName(), beginDate, endDate);
exportOldChannelDatasDtoList.addAll(exportOldDatasDtosOldChannel);
}
//设置excel标题列
List<String> keys = new ArrayList<>();
keys.add("渠道");
keys.add("银行");
keys.add("代码");
keys.add("月份");
keys.add("结算量");

//TODO 获取要导出的数据列表
List<List<String>> newChannelValues = new ArrayList<>();
List<List<String>> oldChannelValues = new ArrayList<>();
for (ExportOldDatasDto exportOldDatasDto : exportNewChannelDatasDtoList) {
List<String> cellList = new ArrayList<>();
cellList.add(exportOldDatasDto.getChannelName());
cellList.add(exportOldDatasDto.getBankName());
cellList.add(exportOldDatasDto.getCode());
cellList.add(exportOldDatasDto.getBillMonth());
cellList.add(exportOldDatasDto.getAllCount());
newChannelValues.add(cellList);
}
for (ExportOldDatasDto exportOldDatasDto : exportOldChannelDatasDtoList) {
List<String> cellList = new ArrayList<>();
cellList.add(exportOldDatasDto.getChannelName());
cellList.add(exportOldDatasDto.getBankName());
cellList.add(exportOldDatasDto.getCode());
cellList.add(exportOldDatasDto.getBillMonth());
cellList.add(exportOldDatasDto.getAllCount());
oldChannelValues.add(cellList);
}

HSSFWorkbook wb = new HSSFWorkbook();
ExcelUtil.bitchExport(wb, "新渠道银行结算数据", "根据新渠道查询银行结算数据", keys, newChannelValues);
ExcelUtil.bitchExport(wb, "老渠道银行结算数据", "根据老渠道查询银行结算数据", keys, oldChannelValues);

try {
String fileName = "Old_New_Channel_bankSetting.xlsx";
OutputStream os = response.getOutputStream();
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "UTF-8"));
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
wb.write(os);
os.flush();
os.close();
} catch (IOException e) {
log.error("导入失败数据导出excel异常，{}", e);
throw new RuntimeException("生成excel异常", null);
}
}