package com.hxkr.util;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
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.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
/**
*
* @author 陈欢
* @Date 2019年3月7日
* 数据库导出数据到Excel
*
*/
public class ExportUtil {
public static void main(String[] args) {
List<HashMap<String, Object>> professionField = testField();
System.out.println(professionField);
List<HashMap<String, Object>> professionMsg = testData();
System.out.println(professionMsg);
export("C:\\Users\\Administrator\\Desktop\\export.xls", "sheet","测试标题",professionField,professionMsg);
}
/**
* 测试字段
* @return
*/
private static List<HashMap<String, Object>> testField(){
HashMap<String, Object> hashMap = new HashMap<>();
HashMap<String, Object> hashMap1 = new HashMap<>();
List<HashMap<String, Object>> professionField = new ArrayList<>();
hashMap.put("field_guid", "5657a72d-4270-46a6-a635-78b09f267f78");
hashMap.put("name", "专业名称");
hashMap.put("state", "yes");
professionField.add(hashMap);
hashMap1.put("field_guid", "b111fabd-9752-4ca6-9ac1-84e0ef175c50");
hashMap1.put("name", "专业代码");
hashMap1.put("state", "yes");
professionField.add(hashMap1);
return professionField;
}
/**
* 测试字段
* @return
*/
private static List<HashMap<String, Object>> testData(){
HashMap<String, Object> hashMap = new HashMap<>();
HashMap<String, Object> hashMap1 = new HashMap<>();
List<HashMap<String, Object>> professionField = new ArrayList<>();
hashMap.put("5657a72d-4270-46a6-a635-78b09f267f78", "生物学");
hashMap.put("b111fabd-9752-4ca6-9ac1-84e0ef175c50", "1010101");
professionField.add(hashMap);
hashMap1.put("5657a72d-4270-46a6-a635-78b09f267f78", "物理学");
hashMap1.put("b111fabd-9752-4ca6-9ac1-84e0ef175c50", "2020202");
professionField.add(hashMap1);
return professionField;
}
/**
* 生成一个Excel
* @param address 文件存放地址
* @param sheetName 工作表名称
* @param headerName 标题名称
* @param professionField 表头字段数据源
* @param professionMsg 表数据数据源
*
*
*/
public static void export(String address,String sheetName,String headerName,List<HashMap<String, Object>> professionField,List<HashMap<String, Object>> professionMsg){
HSSFWorkbook workbook = new HSSFWorkbook();//创建一个Excel文件
HSSFSheet sheet = workbook.createSheet(sheetName);//创建一个工作表
setTop(workbook, sheet, headerName,headerDataList(professionField).size()-1);//设置标题
setheader(workbook, sheet, professionField);//设置表头字段
setData(workbook, sheet, professionField,professionMsg);//设置数据
try {
FileOutputStream out = new FileOutputStream(address);
workbook.write(out);
out.close();
System.out.println("完成");
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 设置标题
* @param workbook
* @param sheet
* @param topContent 标题名称
*/
public static void setTop(HSSFWorkbook workbook,HSSFSheet sheet,String topContent,Integer num) {
HSSFRow row = sheet.createRow((int) 0); //创建第一行
HSSFCell cell = row.createCell((short) 0); //创建第一列
/**
* CellRangeAddress(firstRow, lastRow, firstCol, lastCol)
* firstRow 区域中第一个单元格的行号
lastRow 区域中最后一个单元格的行号
firstCol 区域中第一个单元格的列号
lastCol 区域中最后一个单元格的列号
*/
CellRangeAddress cellRangeAddress = new CellRangeAddress(0,0,0,num);
sheet.addMergedRegion(cellRangeAddress);
row.setHeightInPoints((short)40);//行高
cell.setCellStyle(topStyle(workbook));//设置样式
cell.setCellValue(topContent);
}
/**
* 设置标题字体
* @param wb
* @return
*/
private static CellStyle topStyle(Workbook wb){
CellStyle cellStyle=wb.createCellStyle();
// 设置字体
Font font = wb.createFont();
font.setFontName("微软雅黑");//字体类型
font.setFontHeightInPoints((short) 20);// 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
font.setColor(HSSFColor.RED.index);//设置字体颜色
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
return cellStyle;
}
/**
* 设置表头字段
* @param workbook
* @param sheet
* @param professionMsg 数据源
*/
public static void setheader(HSSFWorkbook workbook,HSSFSheet sheet,List<HashMap<String, Object>> professionMsg) {
HSSFRow row = sheet.createRow((int) 2); //创建第三行
row.setHeightInPoints((short)25);//行高
List<String> headerDataList = headerDataList(professionMsg);
for (int i = 0; i < headerDataList.size(); i++) {
sheet.setColumnWidth(i, headerDataList.get(i).toString().getBytes().length*256+1500);
HSSFCell cell = row.createCell((short) i); //创建每一列
cell.setCellStyle(headerStyle(workbook,HSSFColor.LIGHT_GREEN.index));
cell.setCellValue(headerDataList.get(i));//每一列赋值
}
}
/**
* 设置表头字段样式
* @param wb
* @param color 前景色下标
* @return
*/
private static CellStyle headerStyle(Workbook wb,short color){
CellStyle cellStyle=wb.createCellStyle();
// 设置字体
Font font = wb.createFont();
font.setFontName("微软雅黑");//字体类型
font.setFontHeightInPoints((short) 14);// 字号
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 加粗
font.setColor(HSSFColor.BLACK.index);//设置字体颜色
cellStyle.setFont(font);
cellStyle.setWrapText(true);//自动换行
cellStyle.setFillForegroundColor(color); // 前景色
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
borderStyle(cellStyle, IndexedColors.BLACK.getIndex());
return cellStyle;
}
/**
* 边框颜色
* @param wb
* @param color 颜色代码
* @return
*/
private static void borderStyle(CellStyle cellStyle,short color){
/**
* CellStyle.BORDER_THIN 实线
* CellStyle.BORDER_MEDIUM_DASHED 虚线
*/
cellStyle.setBorderBottom(CellStyle.BORDER_THIN); // 底部边框
cellStyle.setBottomBorderColor(color); // 底部边框颜色
cellStyle.setBorderLeft(CellStyle.BORDER_THIN); // 左边边框
cellStyle.setLeftBorderColor(color); // 左边边框颜色
cellStyle.setBorderRight(CellStyle.BORDER_THIN); // 右边边框
cellStyle.setRightBorderColor(color); // 右边边框颜色
cellStyle.setBorderTop(CellStyle.BORDER_THIN); // 上边边框
cellStyle.setTopBorderColor(color); // 上边边框颜色
}
/**
* 获取所有的表头字段
* @param professionMsg 数据源
* @return 所有的表头字段
*/
public static List<String> headerDataList(List<HashMap<String, Object>> professionMsg){
List<String> list = new ArrayList<>();
for (HashMap<String, Object> hashMap : professionMsg) {
String state = hashMap.get("state").toString();
String fieldName = hashMap.get("name").toString();
if(!"del".equals(state) && !"no".equals(state) && !"简介".equals(fieldName) && !"专业所属大类".equals(fieldName)){
list.add(fieldName);
}
}
return list;
}
/**
* 设置数据
* @param workbook
* @param sheet
* @param professionField 字段数据
* @param professionMsg 数据源
*/
public static void setData(HSSFWorkbook workbook,HSSFSheet sheet,List<HashMap<String, Object>> professionField,List<HashMap<String, Object>> professionMsg) {
for (int d = 0; d < professionMsg.size(); d++) {
HSSFRow row = sheet.createRow((int) d+3); //创建第四行、五行...
row.setHeightInPoints((short)20);//行高
List<String> headerDataList = headerDataList(professionField);
for (int i = 0; i < headerDataList.size(); i++) {
HSSFCell cell = row.createCell((short) i); //创建每一列
String guid = getGuId(headerDataList.get(i).toString(), professionField);
if(professionMsg.get(d).get(guid).toString().getBytes().length > headerDataList.get(i).toString().getBytes().length){
sheet.setColumnWidth(i, professionMsg.get(d).get(guid).toString().getBytes().length*256+1500);
}
cell.setCellStyle(dataStyle(workbook));
cell.setCellValue(professionMsg.get(d).get(guid).toString());//每一列赋值
}
}
}
/**
* 设置表头字段样式
* @param wb
* @param color 前景色下标
* @return
*/
private static CellStyle dataStyle(Workbook wb){
CellStyle cellStyle=wb.createCellStyle();
// 设置字体
Font font = wb.createFont();
font.setFontName("微软雅黑");//字体类型
font.setFontHeightInPoints((short) 12);// 字号
font.setColor(HSSFColor.BLACK.index);//设置字体颜色
cellStyle.setFont(font);
cellStyle.setWrapText(true);//自动换行
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
borderStyle(cellStyle, IndexedColors.BLACK.getIndex());
return cellStyle;
}
/**
* 根据字段名称查询字段field_guid
* @param fieldName 字段名称
* @param professionMsg 数据源
* @return
*/
private static String getGuId(String fieldName,List<HashMap<String, Object>> professionField){
for (HashMap<String, Object> hashMap : professionField) {
if(!"".equals(fieldName)){
if(fieldName.equals(hashMap.get("name"))){
return hashMap.get("field_guid").toString();
}
}
}
return null;
}
}
