JAVA之多sheet页表格生成工具类
主要方法:
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFCell;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
//调用方法
public HSSFWorkbook exportExcel(List<Map<String, Object>> exceldata){
HSSFWorkbook workbook = new HSSFWorkbook();
try{
//OutputStream out = new FileOutputStream("");
List<List<String>> data3 = new ArrayList<List<String>>();
List<Map<String, Object>> list = exceldata;
List<List<String>> data2 = new ArrayList<List<String>>();
for(Map<String,Object> map : list){
Iterator<String> it= map.keySet().iterator();
for (String k : map.keySet())
{
List rowData = new ArrayList();
rowData.add(String.valueOf(k));
data2.add(rowData);
continue;
}
}
//新方法取字段
//用于自动生成标题行
String[] titlelist = data2.toString().replace("[", "").replace("]", "").split(",");
List<String> listtitle =new ArrayList<String>();
for(String s:titlelist){
if(!listtitle.contains(s.trim())){ //或者list.indexOf(s)!=-1
listtitle.add(s.trim());
}
}
String[] headers = {"MSISDN","HOME_NAME","NAME","NVL(A.GEN_TIME,SETTLE_MONTH||'0101')","NVL(A.WRITEOFF_TIME,SETTLE_MONTH||'0101')","A.FEE/1000","DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)"};
//数据转list<list<String>>
List rowData = new ArrayList();
for(int i=0;i<list.size();i++){
Map<String,Object> newMap = list.get(i);
for(Entry<String,Object> entry:newMap.entrySet()){
rowData.add(entry.getValue());
if((i+1) % headers.length == 0){
data3.add(rowData);
rowData = new ArrayList();
}
}
}
List<List<String>> data4 = new ArrayList<List<String>>();
List rowData = new ArrayList();
for(int i=0;i<exceldata.size();i++){
Map<String,Object> newMap = exceldata.get(i);
System.out.println("newMap="+newMap);
for(Entry<String,Object> entry:newMap.entrySet()){
rowData.add(entry.getValue());
}
data4.add(rowData);
rowData = new ArrayList();
}
System.out.println("data4="+data4);
List<List<List<String>>> result = ListDemo.splitList(data4, 5);//设置每个sheet页为5条数据
System.out.println("result="+result);
for(int i=1;i<result.size()+1;i++) {
exportExcel(workbook, i-1, "sheet"+i, headers, result.get(i-1));//设置sheet名
}
}catch (Exception e) {
e.printStackTrace();
}
return workbook;
}
/**
* @Title: exportExcel
* @Description: 导出Excel的方法
* @author: evan @ 2019-07-30
* @param workbook
* @param sheetNum (sheet的位置,0表示第一个表格中的第一个sheet)
* @param sheetTitle (sheet的名称)
* @param headers (表格的标题)
* @param result (表格的数据)
* @param out (输出流)
* @throws Exception
*/
public void exportExcel(HSSFWorkbook workbook, int sheetNum,
String sheetTitle, String[] headers, List<List<String>> result) throws Exception {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet();
workbook.setSheetName(sheetNum, sheetTitle);
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 35);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.BLACK.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text.toString());
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 1;
for (List<String> m : result) {
row = sheet.createRow(index);
int cellIndex = 0;
int count = 0;
for (String str : m) {
HSSFCell cell = row.createCell((short) cellIndex);
if(str !="" && str !=null){
count++;
if(count == 4 || count == 5){
String timevalue = str.toString().substring(0, 4) +"年"+ str.toString().substring(4, 6)+"月";
cell.setCellValue(timevalue);
}else{
cell.setCellValue(str.toString());
}
}else{
cell.setCellValue("");
}
cellIndex++;
}
index++;
}
}
}
}
数据转换工具类
import java.util.ArrayList;
import java.util.List;
public class ListDemo {
public static void main(String[] args) {
List<Integer> list = new ArrayList();
for (int i = 1; i <= 16; i++) {
list.add(i);
}
List<List<Integer>> result = splitList(list, 5);
System.out.println("分隔后List个数:\t" + result.size());
}
/**
* 按指定大小,分隔集合,将集合按规定个数分为n个部分
*
* @param list
* @param len
* @return
*/
public static <T> List<List<T>> splitList(List<T> list, int len) {
if (list == null || list.size() == 0 || len < 1) {
return null;
}
//返回结果
List<List<T>> result = new ArrayList<List<T>>();
//传入集合长度
int size = list.size();
//分隔后的集合个数
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<T> subList = list.subList(i * len, ((i + 1) * len > size ? size : len * (i + 1)));
result.add(subList);
}
return result;
}
}
- 数据样式
List<Map<String, Object>> list = [{MSISDN=15982323028, HOME_NAME=福州, NAME=手机保障服务4元套餐费, GEN_TIME=2019070101, NVL=2019070101, FEE=0.11, DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)=0.11}];
测试类
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.io.FileNotFoundException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
public class Testforexport {
public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
ExportExcelUtils2 asd = new ExportExcelUtils2();
List<Map<String, Object>> list = DBConnection.update(3, "1");
System.out.println("list="+list);
//List<Map<String, Object>> list = [{MSISDN=15982323028, HOME_NAME=福州, NAME=手机保障服务4元套餐费, GEN_TIME=2019070101, NVL=2019070101, FEE=0.11, DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)=0.11}];
Map map = new HashMap();
map.put("DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)", "8");
map.put("A.FEE/1000", "8");
map.put("NVL(A.WRITEOFF_TIME,SETTLE_MONTH||'0101')", "20190701000000");
map.put("NVL(A.GEN_TIME,SETTLE_MONTH||'0101')", "20190701000000");
map.put("HOME_NAME", "福州");
map.put("NAME", "手机保障服务4元套餐费");
map.put("MSISDN", "15982323028");
Map map2 = new HashMap();
map2.put("DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)", "8");
map2.put("A.FEE/1000", "8");
map2.put("NVL(A.WRITEOFF_TIME,SETTLE_MONTH||'0101')", "20190801000000");
map2.put("NVL(A.GEN_TIME,SETTLE_MONTH||'0101')", "20190801000000");
map2.put("HOME_NAME", "福州");
map2.put("NAME", "手机保障服务4元套餐费");
map2.put("MSISDN", "15982323028");
Map map3 = new HashMap();
map3.put("DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)", "8");
map3.put("A.FEE/1000", "8");
map3.put("NVL(A.WRITEOFF_TIME,SETTLE_MONTH||'0101')", "20190901000000");
map3.put("NVL(A.GEN_TIME,SETTLE_MONTH||'0101')", "20190901000000");
map3.put("HOME_NAME", "福州");
map3.put("NAME", "手机保障服务4元套餐费");
map3.put("MSISDN", "15982323028");
Map map4 = new HashMap();
map4.put("DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)", "8");
map4.put("A.FEE/1000", "8");
map4.put("NVL(A.WRITEOFF_TIME,SETTLE_MONTH||'0101')", "20191001000000");
map4.put("NVL(A.GEN_TIME,SETTLE_MONTH||'0101')", "20191001000000");
map4.put("HOME_NAME", "福州");
map4.put("NAME", "手机保障服务4元套餐费");
map4.put("MSISDN", "15982323028");
Map map5= new HashMap();
map5.put("DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)", "8");
map5.put("A.FEE/1000", "8");
map5.put("NVL(A.WRITEOFF_TIME,SETTLE_MONTH||'0101')", "20190701000000");
map5.put("NVL(A.GEN_TIME,SETTLE_MONTH||'0101')", "20190701000000");
map5.put("HOME_NAME", "福州");
map5.put("NAME", "手机保障服务4元套餐费");
map5.put("MSISDN", "15982323028");
Map map6 = new HashMap();
map6.put("DECODE(ACCOUNT_TYPE,84,0,83,0,99,0,A.FEE/1000)", "8");
map6.put("A.FEE/1000", "8");
map6.put("NVL(A.WRITEOFF_TIME,SETTLE_MONTH||'0101')", "20190701000000");
map6.put("NVL(A.GEN_TIME,SETTLE_MONTH||'0101')", "20190701000000");
map6.put("HOME_NAME", "福州");
map6.put("NAME", "手机保障服务4元套餐费");
map6.put("MSISDN", "15982323028");
List<Map<String, Object>> maplist = new ArrayList<>();
maplist.add(map);
maplist.add(map2);
maplist.add(map3);
maplist.add(map4);
maplist.add(map5);
maplist.add(map6);
System.out.println("maplist="+maplist);
List<List<String>> data4 = new ArrayList<List<String>>();
List rowData = new ArrayList();
for(int i=0;i<maplist.size();i++){
Map<String,Object> newMap = maplist.get(i);
for(Entry<String,Object> entry:newMap.entrySet()){
rowData.add(entry.getValue());
}
data4.add(rowData);
rowData = new ArrayList();
}
System.out.println("data5="+data4);
HSSFWorkbook wd = asd.exportExcel(maplist);
OutputStream out = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\multi-template-filter\\test.xls");
wd.write(out);
out.close();
}
}
生成样式