package com.device.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import java.io.File;
import java.io.FileOutputStream;
import java.util.*;
;
/**
* @Author: zsh
* @Date: 2019/11/27 15:26
* @Description:
* @DateTime: 15:26
*/
public class MyExcelUtil {
/**
* @author: zsh
* @Descirpt: 将数据写入到excle文件中,格式为.xls
* @date: 2019/11/27 15:27
*/
public static File excelExport(List<Map<String, String>> param, String sheetName) {
try {
Map<String, String> cMap = System.getenv();
File tempFile = new File(new File(System.getProperty("java.io.tmpdir")),
UUID.randomUUID().toString() + ".xlsx");
tempFile.createNewFile();
FileOutputStream xlsxFileOutputStream = new FileOutputStream(tempFile);
List<List<String>> listList = new ArrayList<>();
List<String> headList = new ArrayList<>();
for (int i = 0; i < param.size(); i++) {
Map<String, String> map = param.get(i);
List<String> list = new ArrayList<>();
Set<Map.Entry<String, String>> entries = map.entrySet();
Iterator<Map.Entry<String, String>> iterator = entries.iterator();
while (iterator.hasNext()) {
Map.Entry<String, String> next = iterator.next();
if (i == 0) {
headList.add(next.getKey());
}
list.add(next.getValue());
}
listList.add(list);
}
HSSFWorkbook fworkbook = new HSSFWorkbook();
HSSFSheet sheet = fworkbook.createSheet(sheetName + "_0");
sheet.setActive(true);
HSSFCellStyle cellStyle = fworkbook.createCellStyle();
Font font = fworkbook.createFont();
font.setFontHeightInPoints((short) 16);
font.setFontName("宋体");
font.setBold(true);
cellStyle.setFont(font);
int sheetnumber = 0;
HSSFSheet crrsheet = sheet;
Integer listsize = listList.size();
for (int j = 0; j < listsize; j++) {
boolean flag = false;
if ((j / 60000) > sheetnumber) {
flag = true;
sheetnumber++;
crrsheet = fworkbook.createSheet(sheetName + "_" + sheetnumber);
crrsheet.setActive(true);
}
if (flag || j == 0) {
crrsheet.setColumnWidth(0, 4000);
HSSFRow row = crrsheet.createRow(0);
row.setHeightInPoints(25);
for (int n = 0; n < headList.size(); n++) {
HSSFCell cell = row.createCell(n);
cell.setCellValue(headList.get(n));
cell.setCellStyle(cellStyle);
}
}
crrsheet.setColumnWidth(j, 4000);
HSSFRow row = crrsheet.createRow(j - sheetnumber * 60000 + 1);
row.setHeightInPoints(25);
for (int n = 0; n < headList.size(); n++) {
HSSFCell cell = row.createCell(n);
cell.setCellValue(listList.get(j).get(n));
}
}
fworkbook.write(xlsxFileOutputStream);
fworkbook.close();
return tempFile;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* @author: zsh
* @param: * @param param list的map,sheetname和文件名
* @Descirpt: 将数据写入到excle文件中,格式为.xls,支持续写,不过这个续写是重复的读写,效率比较低,
* 在数量多的时候不建议使用
* @date: 2019/11/27 15:27
*/
public static File write(List<Map<String, String>> param, String sheetName) {
try {
Map<String, String> cMap = System.getenv();
File tempFile = new File(new File(System.getProperty("java.io.tmpdir")),
UUID.randomUUID().toString() + ".xlsx");
tempFile.createNewFile();
FileOutputStream xlsxFileOutputStream = new FileOutputStream(tempFile);
List<List<String>> listList = new ArrayList<>();
List<String> headList = new ArrayList<>();
for (int i = 0; i < param.size(); i++) {
Map<String, String> map = param.get(i);
List<String> list = new ArrayList<>();
Set<Map.Entry<String, String>> entries = map.entrySet();
Iterator<Map.Entry<String, String>> iterator = entries.iterator();
while (iterator.hasNext()) {
Map.Entry<String, String> next = iterator.next();
if (i == 0) {
headList.add(next.getKey());
}
list.add(next.getValue());
}
listList.add(list);
}
HSSFWorkbook fworkbook = new HSSFWorkbook();
HSSFSheet sheet = fworkbook.createSheet(sheetName);
sheet.setActive(true);
HSSFCellStyle cellStyle = fworkbook.createCellStyle();
Font font = fworkbook.createFont();
font.setFontHeightInPoints((short) 16);
font.setFontName("宋体");
font.setBold(true);
cellStyle.setFont(font);
for (int j = 0; j < listList.size(); j++) {
sheet.setColumnWidth(j, 4000);
HSSFRow row = sheet.createRow(j);
row.setHeightInPoints(25);
for (int n = 0; n < headList.size(); n++) {
HSSFCell cell = row.createCell(n);
if (j == 0) {
cell.setCellValue(headList.get(n));
cell.setCellStyle(cellStyle);
} else {
cell.setCellValue(listList.get(j).get(n));
}
}
}
fworkbook.write(xlsxFileOutputStream);
fworkbook.close();
return tempFile;
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
/**
* @author: zsh
* @Descirpt: 比较两个list是否相同
* @date: 2019/11/27 15:29
*/
public static boolean dif2List(List<String> l1, List<String> l2) {
for (int i = 0; i < l1.size(); i++) {
if (!l1.get(i).equals(l2.get(i))) {
return false;
}
}
return true;
}
// /**
// * @author: zsh
// * @Descirpt: 读取excel文件
// * @date: 2019/11/27 15:35
// */
// public static List<Map<String, String>> readExcel(String filePath) {
// try {
// Workbook wb = null;
// Sheet sheet = null;
// Row row = null;
// List<Map<String, String>> list = new ArrayList<>();
// if (checkRoute(filePath)) {
// list = dealExcel(filePath, wb, sheet, row);
// return list;
// }
// return null;
// } catch (IOException e) {
// return null;
// }
// }
/**
* @author: zsh
* @Descirpt: 检测路径,文件格式是否正确
* @date: 2019/11/27 15:31
*/
public static boolean checkRoute(String path) {
File file = new File(path);
if (file.exists()) {
String type = path.substring(path.lastIndexOf("."));
if (!".xls".equals(type) && !".xlsx".equals(type)) {
System.out.println("文件名称既不是xls又不是xlsx,此文件不能进行处理!");
return false;
}
return true;
} else {
System.out.println("文件不存在!");
return false;
}
}
}
JAVA工具类系列之Excel
最新推荐文章于 2023-01-13 09:00:46 发布