因为业务需要，不知道读取的系统能识别什么样格式的dbf，由此集合多个dbf导出方式为工具类
java
package com.lhw.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.MapUtils;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
//import com.ewell.medqc.rec.enums.N420DfbEnum;
//import com.linuxense.javadbf.DBFDataType;
//import com.linuxense.javadbf.DBFField;
//import com.linuxense.javadbf.DBFWriter;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
//import java.nio.charset.Charset;
import java.util.*;
import java.util.stream.Collectors;
//import static org.apache.tika.utils.CharsetUtils.forName;
public class ExportUtils {
/**
* 基于poi的方式导出dbf文件
* @param fileName 文件名
* @param response HttpServletResponse
* @param dataList 需要导出的行数据
* @param headList 需要导出的列数据
* @throws Exception
*/
public static void exportPoiToDbf(String fileName, List<List<String>> headList ,List<List<Object>> dataList, HttpServletResponse response) throws IOException {
try (Workbook workbook = new XSSFWorkbook(); OutputStream out = response.getOutputStream()) {
Sheet sheet = workbook.createSheet();
Row rowHeader = sheet.createRow(0);
for (int i = 0; i < headList.size(); i++) {
List<String> head = headList.get(i);
Cell cell = rowHeader.createCell(i, CellType.STRING);
String headerName = StringUtils.defaultString(head.get(0));
cell.setCellValue(headerName);
}
if (CollectionUtil.isNotEmpty(dataList)) {
for (int i = 0; i < dataList.size(); i++) {
List<Object> data = dataList.get(i);
Row rowData = sheet.createRow(i + 1);
for (int j = 0; j < data.size(); j++) {
Cell cell = rowData.createCell(j);
String value = data.get(j) != null ? data.get(j).toString() : "";
cell.setCellValue(value);
}
}
}
response.setContentType("application/x-dbf");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".dbf", "utf-8"));
workbook.write(out);
} catch (Exception e) {
e.printStackTrace();
// 重置response
outputErrorJson(response,e.getMessage());
}
}
/**
* 基于xml的方式导出dbf文件
* @param fileName 导出的文件名
* @param response HttpServletResponse
* @param dataList 需要导出的行数据
* @param headList 需要导出的列数据
* @throws Exception
*/
@SuppressWarnings({ "unchecked", "rawtypes" }) //忽略警告
public static void exportXmlToDbf(String fileName,List<List<String>> headList,List<List<Object>> dataList, HttpServletResponse response) throws IOException {
// 创建一个excel应用文件
StringBuffer sb = new StringBuffer();
sb.append("<?xml version=\"1.0\"?>");
sb.append("\n");
sb.append("<?mso-application progid=\"Excel.Sheet\"?>");
sb.append("\n");
sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sb.append("\n");
sb.append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
sb.append("\n");
sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
sb.append("\n");
sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
sb.append("\n");
sb.append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
sb.append("\n");
sb.append("<Styles>\n");
/*设置列头样式*/
sb.append("<Style ss:ID=\"header\" ss:Name=\"header\">\n");//ss:ID=“header”对应下面的Row ss:StyleID=“header”
sb.append("<Font ss:FontName=\"Arial\" x:CharSet=\"134\" ss:Bold=\"Bolder\" ss:Size=\"10\"/>\n");//设置字体
sb.append("</Style>\n");
/*其它默认样式设置*/
sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
sb.append("<Alignment ss:Vertical=\"Center\"/>\n");
sb.append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"10\"/>\n");
sb.append("<Interior/>\n");
sb.append("<NumberFormat/>\n");
sb.append("<Protection/>\n");
sb.append("</Style>\n");
sb.append("</Styles>\n");
try {
// 生成表格
int headersLength = headList.size();
sb.append("<Worksheet ss:Name=\"" + "Sheet1" + "\">");
sb.append("\n");
sb.append("<Table ss:ExpandedColumnCount=\"" + headersLength
+ "\" ss:ExpandedRowCount=\"1000000\" x:FullColumns=\"1\" x:FullRows=\"1\">");
sb.append("\n");
// 输出列头
sb.append("<Row>");
for (int i = 0; i < headersLength; i++) {
sb.append("<Cell ss:StyleID=\"header\"><Data ss:Type=\"String\">" + headList.get(i).get(0) + "</Data></Cell>");
}
sb.append("</Row>");
// 构建表体数据
for (int j = 0; j < dataList.size(); j++) {
sb.append("<Row>");
for (int i = 0; i < dataList.get(0).size(); i++) {
sb.append("<Cell><Data ss:Type=\"String\">");
sb.append(String.valueOf(dataList.get(j).get(i)).equals("null")?"":String.valueOf(dataList.get(j).get(i)));
sb.append("</Data></Cell>");
}
sb.append("</Row>");
sb.append("\n");
}
sb.append("</Table>");
sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
sb.append("\n");
sb.append("<ProtectObjects>False</ProtectObjects>");
sb.append("\n");
sb.append("<ProtectScenarios>False</ProtectScenarios>");
sb.append("\n");
sb.append("</WorksheetOptions>");
sb.append("\n");
sb.append("</Worksheet>");
sb.append("</Workbook>");
sb.append("\n");
response.setContentType("application/x-dbf;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".dbf", "utf-8"));
response.getOutputStream().write(sb.toString().getBytes());
response.getOutputStream().flush();
response.getOutputStream().close();
} catch (IOException e) {
e.printStackTrace();
outputErrorJson(response,e.getMessage());
}
}
/**
* 基于easyExcel的方式导出dbf文件
* @param fileName 导出的文件名
* @param response HttpServletResponse
* @param dataList 需要导出的行数据
* @param headList 需要导出的列数据
* @throws Exception
*/
public static void exportEasyExcelToDbf(String fileName,List<List<String>> headList,List<List<Object>> dataList,HttpServletResponse response) throws IOException {
//导出dbf的话需要将date里的类型转成String类型才能满足上报要求
List<List<String>> strdataList=new ArrayList<>();
if (CollectionUtil.isNotEmpty(dataList)){
strdataList =dataList.stream().map(innerList -> innerList.stream().map(m->m==null? "": String.valueOf(m)).collect(Collectors.toList())).collect(Collectors.toList());
}
try {
response.setContentType("application/x-dbf;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".dbf", "utf-8"));
//设置列名的字体大小
WriteFont headWriteFont = new WriteFont();
//设置字体大小为10
headWriteFont.setFontHeightInPoints((short) 10);
//取消加粗
headWriteFont.setBold(false);
//设置字体为Arial
headWriteFont.setFontName("Arial");
//列名单元的风格对象
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setWriteFont(headWriteFont);
//设置前景色为白色
// headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//去除列名边框
headWriteCellStyle.setBorderTop(BorderStyle.NONE);
headWriteCellStyle.setBorderLeft(BorderStyle.NONE);
headWriteCellStyle.setBorderRight(BorderStyle.NONE);
headWriteCellStyle.setBorderBottom(BorderStyle.NONE);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy();
horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);
WriteCellStyle cellStyle=new WriteCellStyle();
WriteFont cellWriteFont = new WriteFont();
cellWriteFont.setFontHeightInPoints((short) 10);
cellWriteFont.setFontName("宋体");
cellStyle.setWriteFont(cellWriteFont);
List<WriteCellStyle> writeCellStyleList = new ArrayList<>();
writeCellStyleList.add(cellStyle);
horizontalCellStyleStrategy.setContentWriteCellStyleList(writeCellStyleList);
EasyExcel.write(response.getOutputStream()).registerWriteHandler(horizontalCellStyleStrategy).head(headList).autoCloseStream(Boolean.FALSE).sheet("sheet").doWrite(strdataList);
} catch (IOException e) {
e.printStackTrace();
// 重置response
outputErrorJson(response,e.getMessage());
}
}
/**
* 基于javadbf的方式导出dbf文件
* @param response HttpServletResponse
* @param dataList 需要导出的行数据
* @param headList 需要导出的列数据
* @throws Exception
*/
// public static void exportJavaDbfToDbf(String fileName,List<List<String>> headList,List<List<Object>> dataList,HttpServletResponse response) throws IOException {
// try (OutputStream fos = response.getOutputStream()) {
// response.setContentType("application/x-dbf");
// response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName + ".dbf", "utf-8"));
// //先需要设置一下表结构--列名
// DBFField[] dbfFields = new DBFField[headList.size()];
// Map<Integer,String> noStrMap= new HashMap<>();
// for (int i = 0; i < headList.size(); i++) {
// dbfFields[i] = new DBFField();
// dbfFields[i].setName(headList.get(i).get(0));
// // dbf表 字符类型 的字段长度最大不能超过254
// if (N420DfbEnum.getColumnType(headList.get(i).get(0))!=null){
// dbfFields[i].setType(DBFDataType.NUMERIC);
// dbfFields[i].setLength(5);
// noStrMap.put(i,"F");
// }else {
// dbfFields[i].setType(DBFDataType.CHARACTER);
// dbfFields[i].setLength(30);
// }
// }
// Charset defaultCharset = forName("GBK");
// DBFWriter writer = new DBFWriter(fos, defaultCharset);
// writer.setFields(dbfFields);
// //然后一条一条的往里面插数据
// for (int i = 0; i < dataList.size(); i++) {
// Object[] rowData = new Object[dataList.get(0).size()];
// for (int j = 0; j < dataList.get(0).size(); j++) {
// if (!noStrMap.containsKey(j)){
// rowData[j]=String.valueOf(dataList.get(i).get(j)).equals("null")?"":String.valueOf(dataList.get(i).get(j));
// }else {
// if (String.valueOf(dataList.get(i).get(j)).equals("null")){
// rowData[j]=Float.valueOf(0);
// }else {
// rowData[j]=Float.valueOf(String.valueOf(dataList.get(i).get(j)));
// }
// }
// }
// writer.addRecord(rowData);
// }
// // 写入数据
// writer.write(fos);
// } catch (IOException e) {
// e.printStackTrace();
// // 重置response
// outputErrorJson(response,e.getMessage());
// }
// }
/**
* 这个是通用的easyexcel无对象导出方式,使用这个方式需要调用方法前设置ContentType类型
* @param fileName 导出的文件名+文件后缀
* @param response HttpServletResponse
* @param dataList 需要导出的行数据
* @param headList 需要导出的列数据
* @throws Exception
*/
public static void exportEasyExcel(String fileName, List<List<String>> headList, List<List<Object>> dataList, HttpServletResponse response) throws IOException {
try {
// response.setContentType("application/x-dbf;charset=UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
//设置列名的字体大小
WriteFont headWriteFont = new WriteFont();
//设置字体大小为10
headWriteFont.setFontHeightInPoints((short) 10);
//取消加粗
headWriteFont.setBold(false);
//设置字体为Arial
headWriteFont.setFontName("Arial");
//列名单元的风格对象
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setWriteFont(headWriteFont);
//设置前景色为白色
// headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
// headWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//去除列名边框
headWriteCellStyle.setBorderTop(BorderStyle.NONE);
headWriteCellStyle.setBorderLeft(BorderStyle.NONE);
headWriteCellStyle.setBorderRight(BorderStyle.NONE);
headWriteCellStyle.setBorderBottom(BorderStyle.NONE);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy();
horizontalCellStyleStrategy.setHeadWriteCellStyle(headWriteCellStyle);
WriteCellStyle cellStyle=new WriteCellStyle();
WriteFont cellWriteFont = new WriteFont();
cellWriteFont.setFontHeightInPoints((short) 10);
cellWriteFont.setFontName("宋体");
cellStyle.setWriteFont(cellWriteFont);
List<WriteCellStyle> writeCellStyleList = new ArrayList<>();
writeCellStyleList.add(cellStyle);
horizontalCellStyleStrategy.setContentWriteCellStyleList(writeCellStyleList);
EasyExcel.write(response.getOutputStream()).registerWriteHandler(horizontalCellStyleStrategy).head(headList).autoCloseStream(Boolean.FALSE).sheet("sheet").doWrite(dataList);
} catch (IOException e) {
e.printStackTrace();
// 重置response
outputErrorJson(response,e.getMessage());
}
}
public static void outputErrorJson(HttpServletResponse response,String message) throws IOException {
response.reset();
response.setContentType("application/json");
response.setCharacterEncoding("utf-8");
Map<String, String> map = MapUtils.newHashMap();
map.put("status", "failure");
map.put("message", "下载文件失败" + message);
response.getWriter().println(JSON.toJSONString(map));
}
/**
* 这个是通用的easyexcel有对象导出方式
* @param response HttpServletResponse
* @param type 导出excel后缀类型
* @param fileName 导出文件名
* @param clazz 列表中的类型对象
* @param list 导出列表
* @throws Exception
*/
public static <T> void easyExcelObjectToExcel(HttpServletResponse response,String fileName,String type, Class<T> clazz,List<T> list) throws IOException {
if ("csv".equals(type)){
response.setContentType("text/csv");
}else if("dbf".equals(type)){
response.setContentType("application/x-dbf");
}else if("xls".equals(type)){
response.setContentType("application/vnd.ms-excel");
}else {
//默认xlsx
type="xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(fileName+"."+type, "utf-8"));
try {
HorizontalCellStyleStrategy horizontalCellStyleStrategy = setMyCellStyle();
EasyExcel.write(response.getOutputStream(), clazz).sheet("Sheel1").registerWriteHandler(horizontalCellStyleStrategy).doWrite(list);
} catch (IOException e) {
e.printStackTrace();
outputErrorJson(response, e.getMessage());
}
}
public static HorizontalCellStyleStrategy setMyCellStyle() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
// 字体
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setWrapped(true);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容靠中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
return horizontalCellStyleStrategy;
}
/**
* 这个是通用的easyPoi有对象导出方式
* @param response HttpServletResponse
* @param type 导出excel后缀类型
* @param fileName 导出文件名
* @param clazz 列表中的类型对象
* @param list 导出列表
* @throws Exception
*/
public static <T> void easyPoiObjectToExcel(HttpServletResponse response,String fileName, String type, Class<T> clazz, List<T> list) throws IOException {
if ("csv".equals(type)){
response.setContentType("text/csv");
}else if("dbf".equals(type)){
response.setContentType("application/x-dbf");
}else if("xls".equals(type)){
response.setContentType("application/vnd.ms-excel");
}else {
//默认xlsx
type="xlsx";
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
}
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + type, "utf-8"));
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
try {
ExportParams exportParams = new ExportParams(fileName, "Sheel1");
exportParams.setStyle(ExcelStylesUtil.class);
// 生成workbook 并导出
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, clazz, list);
//获取sheet对象
Sheet sheet = workbook.getSheetAt(0);
//去除窗口冻结
sheet.createFreezePane(0,0);
workbook.write(response.getOutputStream());
workbook.close();
} catch (IOException e) {
e.printStackTrace();
outputErrorJson(response, e.getMessage());
}
}
}
本文作者:Weee
本文链接:
版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！