很久之前写好的工具类 现分享给大家使用(使用超简单)
首先上工具类代码(代码可自行调整)
public static int DEFAULT_COLOUMN_WIDTH = 17;
static List<String> dutyList = Arrays.asList("请假", "病假", "事假", "上班", "早退", "调休", "婚假", "缺勤");
/**
* @param title
* @param ja
* @param headMap
*/
public static ByteArrayOutputStream exportListExport(String title, JSONArray ja, Map<String, String> headMap, Map<String, Integer> lengthMap) {
int colWidth = 0;
// 声明一个工作薄
SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
workbook.setCompressTempFiles(true);
//表头样式
CellStyle titleStyle = workbook.createCellStyle();
titleStyle = getCellStyle(workbook, titleStyle);
// 列头样式
CellStyle headerStyle = workbook.createCellStyle();
headerStyle = getCellStyle2(workbook, headerStyle);
// 单元格样式
CellStyle cellStyle = workbook.createCellStyle();
cellStyle = getCellStyle1(workbook, cellStyle);
//带颜色的单元格样式
CellStyle cellStyleColor = workbook.createCellStyle();
cellStyleColor = getCellStyle4(workbook, cellStyleColor);
// 生成一个(带标题)表格
Sheet sheet = workbook.createSheet();
//设置列宽
int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
int[] arrColWidth = new int[headMap.size()];
// 产生表格标题行,以及设置列宽
String[] properties = new String[headMap.size()];
String[] headers = new String[headMap.size()];
int ii = 0;
for (Iterator<String> iter = headMap.keySet().iterator(); iter.hasNext(); ) {
String fieldName = iter.next();
properties[ii] = fieldName;
headers[ii] = headMap.get(fieldName);
int bytes = 0;
if (lengthMap != null && lengthMap.get(fieldName) != null) {
bytes = lengthMap.get(fieldName);
} else {
bytes = fieldName.getBytes().length;
}
arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
ii++;
}
// 遍历集合数据，产生数据行
int rowIndex = 0;
for (Object obj : ja) {
if (rowIndex == 65535 || rowIndex == 0) {
if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示
//标题行
Row titleRow = sheet.createRow(0);//表头 rowIndex=0
titleRow.createCell(0).setCellValue(title);
titleRow.getCell(0).setCellStyle(titleStyle);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));
//表头行
Row headerRow = sheet.createRow(1); //列头 rowIndex =1
for (int i = 0; i < headers.length; i++) {
headerRow.createCell(i).setCellValue(headers[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
rowIndex = 2;//数据内容从 rowIndex=2开始
}
JSONObject jo = JSONObject.fromObject(obj);
Row dataRow = sheet.createRow(rowIndex);
for (int i = 0; i < properties.length; i++) {
String cellValue = "";
Cell newCell = dataRow.createCell(i);
Object o = jo.get(properties[i]);
if (o == null || "null".equals(o.toString())) cellValue = "";
else if (o instanceof Date) cellValue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss ").format(o);
else if (o instanceof Float || o instanceof Double)
cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
else cellValue = o.toString();
String[] arr = cellValue.split(",");
if (arr.length == 9) {
cellValue = "2017" + (arr[3]) + arr[0];
}
newCell.setCellValue(cellValue);
try{
Boolean flag = true;
if(title.contains("项目归集")){
for (String s:dutyList) {
if(cellValue.contains(s)){
flag = false;
break;
}
}
}
if(flag){
newCell.setCellStyle(cellStyle);
}else {
newCell.setCellStyle(cellStyleColor);
}
}catch (Exception e){
System.out.println("The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook");
}
}
rowIndex++;
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
try {
workbook.write(os);
workbook.dispose();
} catch (IOException e) {
e.printStackTrace();
}
return os;
}
//导出excel
public static HttpServletResponse download(String path, HttpServletResponse response) {
try {
// path是指欲下载的文件的路径。
File file = new File(path);
// 取得文件名。
String filename = file.getName();
// 以流的形式下载文件。
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
// 清空response
response.reset();
// 设置response的Header
// response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.addHeader("Content-Length", "" + file.length());
response.setHeader("Content-Disposition", "attachment;filename="
+ new String(filename.getBytes(), "iso-8859-1"));
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
return response;
}
//表头样式
public static CellStyle getCellStyle(SXSSFWorkbook workbook, CellStyle titleStyle) {
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font titleFont = workbook.createFont();
titleFont.setFontHeightInPoints((short) 20);
titleFont.setBoldweight((short) 500);
titleFont.setFontName("微软雅黑");
titleStyle.setFont(titleFont);
return titleStyle;
}
//单元格样式
public static CellStyle getCellStyle1(SXSSFWorkbook workbook, CellStyle cellStyle) {
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setWrapText(true);//自动换行
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellFont.setFontName("微软雅黑");
cellFont.setFontHeightInPoints((short) 11);
cellStyle.setFont(cellFont);
return cellStyle;
}
//列头样式
public static CellStyle getCellStyle2(SXSSFWorkbook workbook, CellStyle headerStyle) {
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font headerFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerFont.setFontName("微软雅黑");
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(headerFont);
//分类样式
headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
Font typeFont = workbook.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("微软雅黑");
headerStyle.setFont(typeFont);
return headerStyle;
}
//内容 设置颜色
public static CellStyle getCellStyle4(SXSSFWorkbook workbook, CellStyle style){
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setWrapText(true);//自动换行
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font cellFont = workbook.createFont();
cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
cellFont.setFontName("微软雅黑");
cellFont.setFontHeightInPoints((short) 11);
cellFont.setColor(Font.COLOR_RED);
style.setFont(cellFont);
style.setWrapText(false);//自动换行
return style;
}
可直接复制使用,接下来是展示怎么调用以及使用工具类方法
按照这样的方法使用 就能导出一个简单的excel
前端阔以直接调用接口路径 则直接提示用户下载excel
1498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
