package kr.weitao.common.util;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.http.protocol.RequestDate;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.bson.types.ObjectId;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FilterInputStream;
import java.io.InputStream;
import java.util.*;
/***
* @ClassName: OutExcelUtil
* @Description: TODO
* @author: yanyd
* @Date: 3:19 2020/4/23
* @version : V1.0
*/
@Slf4j
public class OutExcelUtil {
private static final String XLS = "xls";
private static final String XLSX = "xlsx";
private static final String SHEET_NAME = "表格1";
private static final String SAVE_FILE_PATH = "/tmp/";
/**
* 导出jsonArray数据
*
* @param dataArray
* @param headerMap
* @param fileName
* @return
*/
public static String outExcel(JSONArray dataArray, LinkedHashMap<String, Object> headerMap, String fileName) {
String path = SAVE_FILE_PATH + fileName;
FileOutputStream fileOutputStream = null;
Workbook workbook = null;
try {
String suffixName = fileName.split("\\.")[1];
workbook = createWriteWorkBook(suffixName);
//创建sheet
Sheet sheet = workbook.createSheet(SHEET_NAME);
//创建表头
Row hssfRow = sheet.createRow(0);
Iterator<Map.Entry<String, Object>> iterator = headerMap.entrySet().iterator();
int i = 0;
CellStyle headStyle = createHeadStyle(workbook);
while (iterator.hasNext()) {
Map.Entry<String, Object> next = iterator.next();
//创建单元格
Cell hssfCell = hssfRow.createCell(i);
//单元格赋值
hssfCell.setCellValue(String.valueOf(next.getValue()));
//设置单元格宽度
sheet.setDefaultColumnWidth(30);
//设置单元格样式
hssfCell.setCellStyle(headStyle);
i++;
}
//设置数据体
List<List<String>> dataList = new ArrayList<>();
for (int d = 0; d < dataArray.size(); d++) {
JSONObject dataObj = dataArray.getJSONObject(d);
List<String> tempList = new ArrayList();
for (String key : headerMap.keySet()) {
String value = "";
if (dataObj.containsKey(key)) {
value = String.valueOf(dataObj.getString(key));
}
tempList.add(value);
}
dataList.add(tempList);
}
CellStyle dataStyle = createDataStyle(workbook);
for (int i1 = 0; i1 < dataList.size(); i1++) {
//创建行
Row dataHssfRow = sheet.createRow(i1 + 1);
List<String> tempList = dataList.get(i1);
for (int i2 = 0; i2 < tempList.size(); i2++) {
//创建单元格
Cell dataHssfCell = dataHssfRow.createCell(i2);
//单元格赋值
dataHssfCell.setCellValue(String.valueOf(tempList.get(i2)));
//设置单元格样式
dataHssfCell.setCellStyle(dataStyle);
}
}
fileOutputStream = new FileOutputStream(path);
workbook.write(fileOutputStream);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
fileOutputStream.close();
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return path;
}
public static Workbook createWriteWorkBook(String suffixName) {
Workbook workbook = null;
if (XLSX.equals(suffixName.toLowerCase())) {
workbook = new XSSFWorkbook();
} else {
workbook = new HSSFWorkbook();
}
return workbook;
}
public static Workbook createReadWorkBook(String suffixName, InputStream inputStream) {
Workbook workbook = null;
try {
if (XLSX.equals(suffixName.toLowerCase())) {
workbook = new XSSFWorkbook(inputStream);
} else {
workbook = new HSSFWorkbook(inputStream);
}
} catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
/**
* 创建表头样式
*
* @param workbook
* @return
*/
public static CellStyle createHeadStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//垂直居中
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//背景蓝色
cellStyle.setFillForegroundColor(IndexedColors.ROYAL_BLUE.getIndex());
cellStyle.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
//设置单元格字体
Font font = workbook.createFont();
//字体 加粗
font.setBold(true);
//字体 微软雅黑
font.setFontName("微软雅黑");
//字体大小12
font.setFontHeightInPoints((short) 12);
//字体颜色 白色
font.setColor(IndexedColors.WHITE.getIndex());
cellStyle.setFont(font);
return cellStyle;
}
/**
* 创建数据样式
*
* @param workbook
* @return
*/
public static CellStyle createDataStyle(Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
//垂直居中
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
//设置单元格字体
Font font = workbook.createFont();
//字体 微软雅黑
font.setFontName("微软雅黑");
//字体大小12
font.setFontHeightInPoints((short) 12);
//字体颜色 白色
font.setColor(IndexedColors.BLACK.getIndex());
cellStyle.setFont(font);
return cellStyle;
}
/**
* 导出：
* LinkedHashMap linkedHashMap = new LinkedHashMap();
* linkedHashMap.put("name", "名字22222222222222222");
* linkedHashMap.put("age", "年龄");
*
* JSONArray jsonArray = new JSONArray();
* JSONObject jsonObject = new JSONObject();
* jsonObject.put("name", "1");
* jsonObject.put("age", null);
* jsonObject.put("sex", "3");
*
* JSONObject jsonObject1 = new JSONObject();
* jsonObject1.put("name", "4");
* jsonObject1.put("age", "5");
* jsonObject1.put("sex", "6");
* jsonArray.add(jsonObject);
* jsonArray.add(jsonObject1);
* outExcel(jsonArray, linkedHashMap, "a.xls");
* outExcel(jsonArray, linkedHashMap, "a.xlsx");
*
* 导入：
* Workbook workbook = OutExcelUtil.createReadWorkBook("xlsx", new FileInputStream("D:\\CKJ第三波调价明细.xlsx"));
* Sheet sheet = workbook.getSheetAt(0);
* //获得数据的总行数
* int totalRowNum = sheet.getLastRowNum();
* // 获得表头
* Row rowHead = sheet.getRow(0);
* // 获得表头总列数
* int cols = rowHead.getPhysicalNumberOfCells();
* // 遍历所有行
* for (int i = 1; i <= totalRowNum; i++) {
* Row row = sheet.getRow(i);
* // 遍历该行所有列
* for (short j = 0; j < cols; j++) {
* Cell cell = row.getCell(j);
* if (cell != null) {
* cell.setCellType(Cell.CELL_TYPE_STRING);
* String cellValue = cell.getStringCellValue();
* System.out.print(cellValue+"|");
* }
* }
* System.out.println();
* }
*
* @param args
* @throws Exception
*/
public static void main(String[] args) throws Exception {
}
}
pom.xml
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
网友评论