package util;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
public class CellStyleUtils {
public static HorizontalCellStyleStrategy getHorizontalCellStyleStrategy() {
WriteCellStyle headWriteCellStyle=new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
headWriteCellStyle.setWrapped(true);
WriteFont headWriteFont=new WriteFont();
headWriteFont.setBold(true);
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short)12);
headWriteCellStyle.setWriteFont(headWriteFont);
WriteCellStyle contentWriteCellStyle=new WriteCellStyle();
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setWrapped(true);
WriteFont contentWriteFont=new WriteFont();
contentWriteFont.setFontHeightInPoints((short)12);
contentWriteFont.setFontName("宋体");
contentWriteCellStyle.setWriteFont(contentWriteFont);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
package util;
import java.util.List;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy{
@Override
protected void setColumnWidth(WriteSheetHolder WriteSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
Integer relativeRowIndex,Boolean isHead) {
// TODO Auto-generated method stub
Sheet sheet=WriteSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 5000);
}
}
package util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.*;
public class ModelConvert{
/用于结果集转换,放多条记录
public static List<Map<String,Object>> convertList(ResultSet rs){
//新建一个map,list集合用于存放多条查询记录
List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
try{
//结果集rs的结构信息,比如字段数,字段名等
ResultSetMetaData md=(ResultSetMetaData) rs.getMetaData();
int columnCount=md.getColumnCount();//取得查询出来的字段个数
while(rs.next){
//新建一个Map集合,将查询出来的内容按照字段名:值的键值对存储在map集合中
Map<String,Object> rowData=new HashMap<String,Object>();
for(int i=1;i<=columnCount;i++){
rowData.put(md.getColumnName(i),rs.getObject(i));
}
list.add(rowData);
}
}catch(SQLException e){
e.printStackTrace();
}finally{
try{
if(rs!=null) rs.close;
rs=null;
}catch(SQLException e){
e.printStackTrace();
}
}
return list;
}
public List<Map<String,Object>> findAll(){
Connection conn=null;
try{
conn=JDBCUtil.getConn();
String sql="select * from data1";
PreparedStatement pstm=conn.prepareStatement(sql);
ResultSet rs=pstm.executeQuery();
List<Map<String,Object>> list=convertList(rs);
return list;
}catch(SQLException e){
e.printStackTrace();
throw new RuntimeException("查询用户失败",e);
}finally{
JdbcUtil.close(conn);
}
}
}
package test;
import java.util.*;
import com.google.common.collect.Lists;
import util.ModelConvert;
public class LastProductRequestTime{
List<List<String>> headTities=Lists.newArrayList();
String aliasInfo="产品线";
headTities.add(Lists.newArrayList(aliasInfo,aliasInfo));
return headTities;
}
public static List<List<Object>> contentData3(){
List<List<Object>> contentList=Lists.newArrayList();
ModelConvert dao=new ModelConvert();
List<Map<String,Object>> list=dao.findLastProducts();
for(Map<String,Object> map:list){
contentList.add(Lists.newArrayList(map.get("alias")));
}
return contentList;
}
public static void LastProductsReqsBugs() throws IOException{
OutputStream out3=new FileOutputStream("d://LastProductsReqsBugs.xlsx");
EasyExcel.write(out3).needHead(true)
.head(head3())
.excelType(ExcelTypeEnum.XLSX)
.registerWriteHandler(CellStyleUtils.getHorizontalCellStyleStrategy()).
.registerWriteHandler(new ExcelWidthStyleStrategy())
.sheet(0,"延期问题和需求表")
.dowrite(contentData3());
out3.close();
}