All Downloads are FREE. Search and download functionalities are using the official Maven repository.
Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
org.liyifeng.html.ExcelUtils Maven / Gradle / Ivy
/*
* @Title ExcelUtils.java
* @Package com.doctor.utils
* @author liyifeng
* @date 2016年7月1日 下午4:55:30
* @version V1.0
*/
package org.liyifeng.html;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collection;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @author liyifeng
* 生成excel工具类
* 2016年7月1日 下午4:55:30
*/
public class ExcelUtils {
/**
* Collection 转 excel文件
*
* @param dataList 数据集合
* @param filename 文件名
* @return excel文件
*/
public static File toExcelFile(Collection dataList, String filename) {
return tableToExcelFile(HtmlUtil.toTable(dataList), filename);
}
/**
* Collection 转 excel对象
*
* @param dataList 数据集合
* @return excel对象
*/
public static SXSSFWorkbook toExcel(Collection dataList) {
return tableToExcel(HtmlUtil.toTable(dataList), true);
}
/**
* 保存table到excel文件
*
* @param table table
* @param filename excel文件名
* @return 文件
*/
public static File tableToExcelFile(Table table, String filename) {
return tableToExcelFile(table, filename, true);
}
/**
* @param table table
* @param filename 保存文件名
* @param striped 是否把数据行条纹化
* @return 文件
*/
public static File tableToExcelFile(Table table, String filename, boolean striped) {
File file = getTmpFile(filename);
SXSSFWorkbook wb = tableToExcel(table, striped);
FileOutputStream fileOut = null;
BufferedOutputStream out = null;
try {
fileOut = new FileOutputStream(file);
out = new BufferedOutputStream(fileOut);
// 防止poi内置安全方面限制导致输出失败
ZipSecureFile.setMinInflateRatio(0.0009);
wb.write(out);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
out.close();
fileOut.close();
} catch (IOException e) {
e.printStackTrace();
}
}
return file;
}
/**
* 保存table集合到同一个excel文件的不同sheet中
*
* @param tables table集合，生成excel时候会存储到不同的sheet中
* @param filename 文件名称
* @return 文件
*/
public static File tablesToExcelFile(Collection tables, String filename) {
return tablesToExcelFile(tables, filename, true);
}
/**
* 保存table集合到同一个excel文件的不同sheet中
*
* @param tables table List
* @param filename 存储文件名
* @param striped 是否把数据行条纹化
* @return 文件绝对路径
*/
public static File tablesToExcelFile(Collection tables, String filename, boolean striped) {
File file = getTmpFile(filename);
SXSSFWorkbook wb = tablesToExcel(tables, striped);
try {
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
}
return file;
}
/**
* @param tableList table集合
* @param striped 是否隔行换色
* @return excel文件对象
*/
public static SXSSFWorkbook tablesToExcel(Collection tableList, boolean striped) {
SXSSFWorkbook wb = new SXSSFWorkbook();
for (Table table : tableList) {
tableToSheet(wb, table, striped);
}
return wb;
}
/**
* @param table table
* @param striped 是否各行换色
* @return excel文件对象
*/
public static SXSSFWorkbook tableToExcel(Table table, boolean striped) {
SXSSFWorkbook wb = new SXSSFWorkbook();
tableToSheet(wb, table, striped);
return wb;
}
private static void tableToSheet(SXSSFWorkbook wb, Table table, boolean striped) {
String sheetName = table.title() == null ? "table" : table.title();
Sheet sheet = wb.createSheet(sheetName);
Row row;
Cell cell;
List allTrList = table.tr();
int totalSize = allTrList.size();
int bodySize = table.bodySize();
int headSize = totalSize - bodySize;
boolean hasSetColWidth = false;
for (int i = 0; i < totalSize; i++) {
Tr tr = allTrList.get(i);
row = sheet.createRow(i);
int cell_index = 0;
CellStyle style = null;
if (i < headSize) {// 表头
style = getHeadStyle(wb);
if (i < headSize - 1) {
style.setBorderBottom(CellStyle.BORDER_NONE);
}
} else { // 表格内容
style = getBodyStyle(wb, i, striped);
}
int hasSetColWidthFlag = 0;
for (Td td : tr.allTds()) {
int col = td.colspan() == null ? 1 : td.colspan().intValue();
cell = row.createCell(cell_index);
setValue(cell, td.text());
cell.setCellStyle(style);
// 设置生成excel的列宽度
if (!hasSetColWidth) {
Integer excelColWidth = td.width();
if (excelColWidth != null) {
hasSetColWidthFlag++;
sheet.setColumnWidth(cell_index, excelColWidth.intValue() * 2 * 256);
}
}
if (col > 1) {
CellRangeAddress region = new CellRangeAddress(i, i, cell_index, cell_index + col - 1);
sheet.addMergedRegion(region);
} else {
}
cell_index += col;
}
// 设置列宽度完成标记
if (hasSetColWidthFlag > 0) {
hasSetColWidth = true;
}
}
}
private static void setValue(Cell cell, String text) {
// 转换格式
try {
if (isNumType(text)) { // 整型
Long val = Long.parseLong(text);
cell.setCellValue(val);
} else if (isDoubleType(text)) { // 浮点型
Double val = Double.parseDouble(text);
cell.setCellValue(val);
} else {
cell.setCellValue(text);
}
} catch (Exception e) {
cell.setCellValue(text);
}
}
private static boolean isNumType(String text) {
Pattern pattern = Pattern.compile("[0-9]*");
Matcher isNum = pattern.matcher(text);
return isNum.matches();
}
private static boolean isDoubleType(String text) {
Pattern pattern = Pattern.compile("^(-?\\d+)(\\.\\d+)?$");
Matcher isNum = pattern.matcher(text);
return isNum.matches();
}
private static CellStyle getHeadStyle(SXSSFWorkbook wb) {
Font f = wb.createFont();
f.setFontName("微软雅黑"); // 设置英文字体
f.setCharSet(HSSFFont.DEFAULT_CHARSET);
f.setFontHeightInPoints((short) 11);// 字号
// f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
CellStyle style = wb.createCellStyle();
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
style.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
return style;
}
private static CellStyle getBodyStyle(SXSSFWorkbook wb, int rowNum, boolean striped) {
Font f = wb.createFont();
f.setFontName("微软雅黑"); // 设置英文字体
CellStyle style = wb.createCellStyle();
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
// 偶数行设置背景色
if (striped && (rowNum % 2 == 0)) {
style.setFillForegroundColor(HSSFColor.LEMON_CHIFFON.index);
style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
}
return style;
}
private static File getTmpFile(String filename) {
if (filename.endsWith(".xlsx")) {
filename = filename.substring(0, filename.indexOf(".xlsx"));
}
File file = null;
try {
file = File.createTempFile(filename, ".xlsx");
} catch (IOException e) {
e.printStackTrace();
}
return file;
}
}
