package com.cash.base.excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import com.alibaba.fastjson.JSONObject;
public class ExcelUtil {
private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
// 每页记录数
private static final int MAX_OF_SHEET_SIZE = 65530;
public static HSSFWorkbook exportExcel(String title, String[] headName, List<Object[]> dataList) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
try {
HSSFSheet sheet = workbook.createSheet(title); // 创建工作表
// 产生表格标题行
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
// sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象
HSSFCellStyle style = getStyle(workbook); // 单元格样式对象
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headName.length - 1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
// 定义所需列数
int columnNum = headName.length;
HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(headName[n]);
cellRowName.setCellValue(text); // 设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
}
// 将查询出的数据设置到sheet对应的单元格中
for (int i = 0; i < dataList.size(); i++) {
Object[] obj = dataList.get(i);// 遍历每个对象
HSSFRow row = sheet.createRow(i + 3);// 创建所需的行数
for (int j = 0; j < obj.length; j++) {
HSSFCell cell = null; // 设置单元格的数据类型
if (j == 0) {
cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i + 1);
} else {
cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
if (!"".equals(obj[j]) && obj[j] != null) {
cell.setCellValue(obj[j].toString()); // 设置单元格的值
}
}
cell.setCellStyle(style); // 设置单元格样式
}
}
// 让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
// 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null)
workbook.close();
}
return workbook;
}
public static boolean writeExcel(String file, HSSFWorkbook work) {
try {
createDirs(file);
OutputStream out = new FileOutputStream(file);
work.write(out);
work.close();
out.flush();
out.close();
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
private static void createDirs(String filePath) {
File file = new File(filePath);
File parent = file.getParentFile();
if (parent != null && !parent.exists()) {
parent.mkdirs();
}
}
/**
* 自动分页50000
*
* @param title
* 标题
* @param keyValue
* map.put("code", "批次号");
* @param JSONObject根据keyValue的code取值
* 批次号为对应列的标题
*/
public static HSSFWorkbook exportExcel(String title, Map<String, String> keyValue, List<JSONObject> dataList) throws Exception {
try {
if (dataList == null || dataList.isEmpty())
return null;
String[] headName = null;
String[] key = null;
if (keyValue != null && !keyValue.isEmpty()) {
headName = new String[keyValue.size()];
key = new String[keyValue.size()];
int n = 0;
for (Map.Entry<String, String> entry : keyValue.entrySet()) {
headName[n] = entry.getValue();
key[n++] = entry.getKey();
}
} else {
return null;
}
return exportExcel(title, headName, key, dataList);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static HSSFWorkbook exportExcel(String title, String[] headName, String[] key, List<JSONObject> dataList) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
try {
if (headName == null || headName.length == 0 || key == null || key.length == 0 || dataList == null || dataList.isEmpty())
return null;
// 分页计算开始开始
for (int k = 0; k < (dataList.size() % MAX_OF_SHEET_SIZE == 0 ? dataList.size() / MAX_OF_SHEET_SIZE : dataList.size() / MAX_OF_SHEET_SIZE + 1); k++) {
HSSFSheet sheet = workbook.createSheet(title + " 第-" + (k + 1) + "-页"); // 创建工作表
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
HSSFCell cellTiltle = row.createCell(0);
HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象
HSSFCellStyle style = getStyle(workbook); // 单元格样式对象
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headName.length)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
// 定义所需列数
HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
HSSFCell cellRowName = rowRowName.createCell(0); // 创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString("序号");
cellRowName.setCellValue(text); // 设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
// 将列头设置到sheet的单元格中
for (int n = 0; n < headName.length; n++) {
cellRowName = rowRowName.createCell(n + 1); // 创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
text = new HSSFRichTextString(headName[n]);
cellRowName.setCellValue(text); // 设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
}
// 将查询出的数据设置到sheet对应的单元格中
for (int i = MAX_OF_SHEET_SIZE * k; i < dataList.size() && i < MAX_OF_SHEET_SIZE * (k + 1); i++) {
JSONObject obj = dataList.get(i);// 遍历每个对象
if (k == 0)
row = sheet.createRow(i + 3);// 创建所需的行数
else
row = sheet.createRow(i + 3 - MAX_OF_SHEET_SIZE * k);
HSSFCell cell = null; // 设置单元格的数据类型
cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(i + 1);
cell.setCellStyle(style);
for (int j = 0; j < key.length; j++) {
cell = row.createCell(j + 1, HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(obj.getString(key[j])); // 设置单元格的值
cell.setCellStyle(style); // 设置单元格样式
}
}
// 让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < headName.length + 1; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
// 当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = currentCell.getStringCellValue().getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if (colNum == 0) {
sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
} else {
sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
workbook.close();
}
return workbook;
}
public static HSSFWorkbook exportExcel(String title, String[] headName, Collection<?> dataList) throws Exception {
HSSFWorkbook workbook = new HSSFWorkbook(); // 创建工作簿对象
try {
Iterator<? extends Object> it = dataList.iterator();
for (int k = 0; k < (dataList.size() % MAX_OF_SHEET_SIZE == 0 ? dataList.size() / MAX_OF_SHEET_SIZE : dataList.size() / MAX_OF_SHEET_SIZE + 1); k++) {
HSSFSheet sheet = workbook.createSheet(title + " 第-" + (k + 1) + "-页"); // 创建工作表
HSSFRow rowm = sheet.createRow(0);
HSSFCell cellTiltle = rowm.createCell(0);
HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);// 获取列头样式对象
HSSFCellStyle style = getStyle(workbook); // 单元格样式对象
sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headName.length - 1)));
cellTiltle.setCellStyle(columnTopStyle);
cellTiltle.setCellValue(title);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString("添加注释！"));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor("admin");
// 定义所需列数
int columnNum = headName.length;
HSSFRow rowRowName = sheet.createRow(2); // 在索引2的位置创建行(最顶端的行开始的第二行)
// 将列头设置到sheet的单元格中
for (int n = 0; n < columnNum; n++) {
HSSFCell cellRowName = rowRowName.createCell(n); // 创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); // 设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(headName[n]);
cellRowName.setCellValue(text); // 设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle); // 设置列头单元格样式
}
int index = 0;
while (it.hasNext()) {
if (MAX_OF_SHEET_SIZE == index)
break;
index++;
HSSFRow row = sheet.createRow(index + 2);// 创建所需的行数;
HSSFCell cell = null; // 设置单元格的数据类型
cell = row.createCell(0, HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(k * MAX_OF_SHEET_SIZE + index);
cell.setCellStyle(style);
Object t = it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (int i = 0; i < fields.length; i++) {
cell = row.createCell(i + 1, HSSFCell.CELL_TYPE_STRING);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class<? extends Object> tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = "男";
if (!bValue) {
textValue = "女";
}
} else if (value instanceof Date) {
Date date = (Date) value;
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
// 有图片时，设置行高为60px;
row.setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80));
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 6, index, (short) 6, index);
anchor.setAnchorType(AnchorType.MOVE_AND_RESIZE);
patriarch.createPicture(anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?{1}");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
cell.setCellStyle(style); // 设置单元格样式
}
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
workbook.close();
}
}
return workbook;
}
/*
* 列头单元格样式
*/
private static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
font.setFontHeightInPoints((short) 11);
// 字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
/*
* 列数据信息单元格样式
*/
private static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
// 设置字体大小
// font.setFontHeightInPoints((short)10);
// 字体加粗
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 设置字体名字
font.setFontName("Courier New");
// 设置样式;
HSSFCellStyle style = workbook.createCellStyle();
// 设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
// 设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
// 设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// 设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
// 设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
// 设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
// 设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
// 在样式用应用设置的字体;
style.setFont(font);
// 设置自动换行;
style.setWrapText(false);
// 设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
return style;
}
private static Workbook readWorkbook(File file) {
InputStream inputStream = null;
String fileName = null;
Workbook wb = null;
try {
inputStream = new FileInputStream(file);
fileName = file.getName();
if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
// 如果是2003版本
if (fileName.endsWith(".xls")) {
// 1.先解析文件
POIFSFileSystem fs = new POIFSFileSystem(inputStream);
wb = new HSSFWorkbook(fs);
} else if (fileName.endsWith(".xlsx")) {// 如果是2007以上版本
wb = new XSSFWorkbook(inputStream);
} else {
return null;
}
}
} catch (IOException e) {
e.printStackTrace();
}finally{
if (inputStream != null) {
try {
inputStream.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return wb;
}
/**
* 将excel解析为指定的对象集合 <br>
* 例如： 要导入的excel格式为 <br>
* 第1行： | id | username | password |(与对象的字段对应)<br>
* 调用： readExcel(file,User.class);
*
* @param file
* -----要解析的excel文件
* @param c
* --------指定的对象类型
* @throws IOException
* @return---------对象集合
*/
public static <T> List<T> readExcel(File file, Class<T> c) throws Exception {
List<T> list = new ArrayList<T>();
Workbook wb = readWorkbook(file);
Sheet sheet = null;
Row row = null;
if(wb==null) return null;
for (int k = 0; k < wb.getNumberOfSheets(); k++) {// 获取每个Sheet表
sheet = wb.getSheetAt(k);
// 获取第一行（标题行）
row = sheet.getRow(0);
// 总列数
int colNum = row.getPhysicalNumberOfCells();
// 总行数
int rowNum = sheet.getLastRowNum();
// 将标题行一一放入数组
String[] titles = new String[colNum];
for (int i = 0; i < colNum; i++) {
titles[i] = row.getCell(i).getStringCellValue();
}
// 获取指定对象所有的字段
Field fields[] = c.getDeclaredFields();
Map<String, Field> fieldMap = new HashMap<String, Field>();
for (int i = 0; i < fields.length; i++) {
fieldMap.put(fields[i].getName(), fields[i]);
}
// 使用反射机制，将值存入对应对象中
try {
for (int i = 1; i < rowNum + 1; i++) {
T t = c.newInstance();
for (int j = 0; j < titles.length; j++) {
// 当excel中有这个字段
if (fieldMap.containsKey(titles[j])) {
String fieldName = titles[j];
String methodName = "set" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
// 调用该字段对应的set方法
Class<?> cc = fieldMap.get(titles[j]).getType();
Method method = c.getMethod(methodName, cc);
String value = String.valueOf(sheet.getRow(i).getCell(j));
if ("男".equals(value))
value = "true";
if ("女".equals(value))
value = "false";
method.invoke(t, parseValue(value, cc));
}
}
list.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (wb != null) {
wb.close();
}
}
}
return list;
}
public static <T> List<T> readExcel(String path, Class<T> c) throws Exception {
return readExcel(new File(path), c);
}
/**
* 将字符串转化为指定类型的对象
*
* @param <T>
* @param s
* ----要转化的字符串
* @param c
* ----目标对象类型
* @return
*/
private static Object parseValue(String s, Class c) {
Object obj = null;
String className = c.getName();
// excel中的数字解析之后可能末尾会有.0，需要去除
if (s.endsWith(".0"))
s = s.substring(0, s.length() - 2);
if (className.equals("java.lang.Integer")) { // Integer
obj = new Integer(s);
} else if (className.equals("int")) { // int
obj = (int) Integer.parseInt(s);
} else if (className.equals("java.lang.String")) { // String
obj = s;
} else if (className.equals("java.lang.Double")) { // Double
obj = new Double(s);
} else if (className.equals("double")) { // double
obj = (double) new Double(s);
} else if (className.equals("java.lang.Float")) { // Float
obj = new Float(s);
} else if (className.equals("float")) { // float
obj = (float) new Float(s);
} else if (className.equals("java.util.Date")) { // Date
try {
obj = sdf.parse(s);
} catch (ParseException e) {
e.printStackTrace();
}
} else if (className.equals("long")) { // long
obj = Long.parseLong(s);
} else if (className.equals("java.util.Long")) { // Long
obj = new Long(s);
} else if (className.equals("boolean")) {
obj = Boolean.parseBoolean(s);
} else if (className.equals("java.lang.Boolean")) {
obj = new Boolean(s);
}
return obj;
}
public static void main(String[] args) {
Map<String, String> map = new LinkedHashMap<>();
map.put("id", "批次号");
map.put("userName", "姓名");
map.put("score", "数值");
map.put("credit", "积分");
map.put("birthDay", "出生日期");
List<JSONObject> dataList = new ArrayList<>();
Collection<User> dataUserList = new ArrayList<>();
Object[] objs = null;
for (int i = 0; i < 100006; i++) {
JSONObject params = new JSONObject();
params.put("id", i);
params.put("userName", "name" + i);
params.put("score", 20L);
params.put("credit", Math.random() * 100000);
String date = sdf.format(new Date());
params.put("birthDay", date);
dataList.add(params);
User user = new User();
user.setId(i);
user.setUserName("name" + i);
user.setScore(20L);
user.setCredit(Math.random() * 100000);
user.setBirthDay(new Date());
user.setSex(true);
dataUserList.add(user);
}
try {
ExcelUtil.writeExcel("d:/test/1/2/1.xls", ExcelUtil.exportExcel("测试数据", map, dataList));
ExcelUtil.writeExcel("d:/test/1/2/3.xls", ExcelUtil.exportExcel("测试数据", new String[] { "序号", "批次号", "姓名", "数值", "积分", "出生日期", "性别" }, dataUserList));
List<User> users = ExcelUtil.readExcel("d:/test/1/2/2.xls", User.class);
if (users != null && !users.isEmpty()) {
for (User user : users) {
System.out.println(user.getId());
System.out.println(user.getUserName());
System.out.println(user.getSex());
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
class User {
private Integer id;
private String userName;
private float score;
private double credit;
private Date birthDay;
private boolean sex;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public float getScore() {
return score;
}
public void setScore(float score) {
this.score = score;
}
public double getCredit() {
return credit;
}
public void setCredit(double credit) {
this.credit = credit;
}
public Date getBirthDay() {
return birthDay;
}
public void setBirthDay(Date birthDay) {
this.birthDay = birthDay;
}
public boolean getSex() {
return sex;
}
public void setSex(boolean sex) {
this.sex = sex;
}
}
@RequestMapping(value = "/export.html")
public void export(HttpServletRequest request, HttpServletResponse response) {
logger.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>");
HSSFWorkbook wb = null;
Map<String, String> map = new LinkedHashMap<>();
map.put("code", "货物运输批次号");
map.put("time", "录入时间");
map.put("num", "数值");
List<JSONObject> dataList = new ArrayList<>();
Object[] objs = null;
for (int i = 0; i < 10; i++) {
JSONObject params = new JSONObject();
params.put("id", i);
params.put("code", Math.random() * 100000);
params.put("time", new Date());
params.put("num", i);
dataList.add(params);
}
OutputStream ouput = null;
try {
wb = ExcelUtil.exportExcel("测试数据2", map, dataList);
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-disposition", "attachment;filename=export.xls");
ouput = response.getOutputStream();
wb.write(ouput);
ouput.flush();
wb.close();
ouput.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (ouput != null) {
try {
ouput.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
5941
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
