package com.using.judge.web.client.common.entity.extra;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
import jxl.Cell;
import jxl.Range;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
public class ExportExcel<T> {
private static final Logger LOGGER = LoggerFactory.getLogger(ExportExcel.class);
/**
* 将数据导出到excel中
* @param headers excel中表头信息
* @param dataList excel中需要显示的数据
* @param fileName excel文件名称
* @param response 响应
*/
public HSSFWorkbook exportDataToExcel(String[] headers, Collection<T> dataList, String fileName, HttpServletResponse response,String firstName){
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(fileName);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 20);
// 产生表格标题行
// 写excel 表头,
HSSFRow row0 = sheet.createRow(0);
HSSFCellStyle alignStyle = workbook.createCellStyle();
alignStyle.setAlignment(HorizontalAlignment.CENTER);
alignStyle.setVerticalAlignment(VerticalAlignment.CENTER);
HSSFFont font = workbook.createFont();
font.setBold(true);
//font.setFontHeight((byte)12);
alignStyle.setFont(font);
row0.setRowStyle(alignStyle);
HSSFCell cell0 = row0.createCell(0);
// 设置第一标题
cell0.setCellValue(firstName);
cell0.setCellStyle(alignStyle);
CellRangeAddress region = new CellRangeAddress(0, 2, 0, 5);
sheet.addMergedRegion(region);
HSSFRow row = sheet.createRow(3);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
//cell.setCellStyle(alignStyle);
}
try {
Iterator<T> it = dataList.iterator();
int index = 3;
while (it.hasNext()){
index ++;
row = sheet.createRow(index);
T t = (T)it.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
// 其它数据类型都当作字符串简单处理
if(value != null && value != ""){
textValue = value.toString();
}
if (textValue != null) {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
cell.setCellValue(richString);
}
}
}
}catch (Exception e){
LOGGER.info("导出仲裁员名称异常"+e.toString());
}
return workbook;
}
//将数据库中数据以excel的形式导出
public void getExportedFile(HSSFWorkbook workbook, String name,HttpServletResponse response) throws Exception {
/*String oldPath = "D://TEST.xls";
File file = new File(oldPath);
if(!file.exists()){
file.createNewFile();
}
FileOutputStream os = new FileOutputStream(file);
workbook.write(os);
String newFilePath = "D://Test1.pdf";
excelToPdf(oldPath,newFilePath);*/
BufferedOutputStream fos = null;
try {
String fileName = name + ".xls";
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + new String( fileName.getBytes("gb2312"), "ISO8859-1" ));
fos = new BufferedOutputStream(response.getOutputStream());
workbook.write(fos);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (fos != null) {
fos.close();
}
}
}
//将数据导入到excel中并将excel转成pdf
public File excelToPdf(String oldFilePath, String pdfFilePath) throws IOException, DocumentException, BiffException {
Document document = new Document(PageSize.A4,0,0,50,0);
File pdfFile = new File(pdfFilePath);
if(!pdfFile.exists()){
pdfFile.createNewFile();
}
FileOutputStream os = new FileOutputStream(pdfFile);
PdfWriter writer = PdfWriter.getInstance(document, os);
//字体设置
/*
* 由于itext不支持中文,所以需要进行字体的设置,我这里让itext调用windows系统的中文字体,
* 找到文件后,打开属性,将文件名及所在路径作为字体名即可。
*/
//创建BaseFont对象,指明字体,编码方式,是否嵌入
BaseFont bf=bf = BaseFont.createFont("STSong-Light", "UniGB-UCS2-H", false);
//创建Font对象,将基础字体对象,字体大小,字体风格
Font font=new Font(bf,10,Font.NORMAL);
int rowNum = 0;
int colNum = 0;
try {
File file = new File(oldFilePath);
if(!file.exists()){
file.createNewFile();
}
Workbook workbook=Workbook.getWorkbook(file);
jxl.Sheet sheet=workbook.getSheet(0);
int column=sheet.getColumns();
//下面是找出表格中的空行和空列
List<Integer> nullCol = new ArrayList<>();
List<Integer> nullRow = new ArrayList<>();
for(int j=0;j<sheet.getColumns();j++){
int nullColNum = 0;
for(int i=0;i<sheet.getRows();i++){
Cell cell=sheet.getCell(j, i);
String str = cell.getContents();
if(str == null || "".equals(str)){
nullColNum ++ ;
}
}
if(nullColNum == sheet.getRows()){
nullCol.add(j);
column--;
}
}
for(int i=0;i<sheet.getRows();i++){
int nullRowNum = 0;
for(int j=0;j<sheet.getColumns();j++){
Cell cell=sheet.getCell(j, i);
String str = cell.getContents();
if(str == null || "".equals(str)){
nullRowNum ++ ;
}
}
if(nullRowNum == sheet.getColumns()){
nullRow.add(i);
}
}
PdfPTable table=new PdfPTable(column);
Range[] ranges = sheet.getMergedCells();
PdfPCell cell1=new PdfPCell();
for(int i=0;i<sheet.getRows();i++){
if(nullRow.contains(i)){ //如果这一行是空行,这跳过这一行
continue;
}
for(int j=0;j<sheet.getColumns();j++){
if(nullCol.contains(j)){ //如果这一列是空列,则跳过这一列
continue;
}
boolean flag = true;
Cell cell=sheet.getCell(j, i);
String str = cell.getContents();
for(Range range : ranges){ //合并的单元格判断和处理
if(j >= range.getTopLeft().getColumn() && j <= range.getBottomRight().getColumn()
&& i >= range.getTopLeft().getRow() && i <= range.getBottomRight().getRow()){
if(str == null || "".equals(str)){
flag = false;
break;
}
rowNum = range.getBottomRight().getRow() - range.getTopLeft().getRow()+1;
colNum = range.getBottomRight().getColumn() - range.getTopLeft().getColumn()+1;
if(rowNum > colNum){
cell1 = mergeRow(str, font, rowNum);
cell1.setColspan(colNum);
table.addCell(cell1);
}else {
cell1 = mergeCol(str, font, colNum);
cell1.setRowspan(rowNum);
table.addCell(cell1);
}
//System.out.println(num1 + " " + num2);
flag = false;
break;
}
}
if(flag){
table.addCell(getPDFCell(str,font));
}
}
}
workbook.close();
document.open();
document.add(table);
document.close();
writer.close();
os.close();
} catch (Exception e) {
System.out.println(e.toString()+"=================");
e.printStackTrace();
}
return pdfFile;
}
//合并行的静态函数
public static PdfPCell mergeRow(String str,Font font,int i) {
//创建单元格对象,将内容及字体传入
PdfPCell cell=new PdfPCell(new Paragraph(str,font));
//设置单元格内容居中
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
//将该单元格所在列包括该单元格在内的i行单元格合并为一个单元格
cell.setRowspan(i);
return cell;
}
//合并列的静态函数
public static PdfPCell mergeCol(String str,Font font,int i) {
PdfPCell cell=new PdfPCell(new Paragraph(str,font));
cell.setMinimumHeight(15);
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
//将该单元格所在行包括该单元格在内的i列单元格合并为一个单元格
cell.setColspan(i);
return cell;
}
//获取指定内容与字体的单元格
public static PdfPCell getPDFCell(String string, Font font)
{
//创建单元格对象,将内容与字体放入段落中作为单元格内容
PdfPCell cell=new PdfPCell(new Paragraph(string,font));
cell.setHorizontalAlignment(Element.ALIGN_CENTER);
cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
//设置最小单元格高度
cell.setMinimumHeight(15);
return cell;
}
}
public void createFile(HttpServletResponse response) throws Exception { List<ArbitratorsBean> arbitratorsToExport = userMapper.findExportData(); if(CollectionUtil.isBlank(arbitratorsToExport)){ throw new BusinessException(ErrorEnum.ERROR_SYSTEM,"数据异常,请核实数据"); } for(ArbitratorsBean arbitratorsBean : arbitratorsToExport){ if(arbitratorsBean.getSex() != null ){ if("1".equals(arbitratorsBean.getSex())){ arbitratorsBean.setSex("男"); }else{ arbitratorsBean.setSex("女"); } } } String fristName = "用户名册"; String[] headers = {"姓名","性别(男、女)","专业(可填写多个,以、隔开)","联系方式","居住地","简介"}; String fileName = "用户名册"; ExportExcel exportExcel = new ExportExcel(); HSSFWorkbook workBook = exportExcel.exportDataToExcel(headers, arbitratorsToExport, fileName, response,fristName); exportExcel.getExportedFile(workBook,fileName,response); }
public String createFilePdf(HttpServletRequest request ,HttpServletResponse response) throws BusinessException { String result = "0"; List<ArbitratorsBean> arbitratorsToExport = userMapper.findExportData(); if(CollectionUtil.isBlank(arbitratorsToExport)){ throw new BusinessException(ErrorEnum.ERROR_SYSTEM,"数据异常,请核实数据"); } for(ArbitratorsBean arbitratorsBean : arbitratorsToExport){ if(arbitratorsBean.getSex() != null ){ if("1".equals(arbitratorsBean.getSex())){ arbitratorsBean.setSex("男"); }else{ arbitratorsBean.setSex("女"); } } } String fristName = "用户名册"; String[] headers = {"用户姓名","性别(男、女)","专业(可填写多个,以、隔开)","联系方式","居住地","简介"}; String fileName = "用户名册"; ExportExcel exportExcel = new ExportExcel(); HSSFWorkbook workBook = exportExcel.exportDataToExcel(headers, arbitratorsToExport, fileName, response,fristName); String tempfileDir = ZipUtils.getLocalTempfileDir(request); // 将生成的excel存放到此临时路径下 File file = new File(tempfileDir+"/"+fileName+System.currentTimeMillis()+".xls"); String excelfilePath = file.getPath(); FileOutputStream ous = null; if(!file.exists()){ try { file.createNewFile(); ous = new FileOutputStream(file); ous.close(); workBook.write(file); } catch (IOException e) { e.printStackTrace(); } } // 生成pdf临时文件 String pdfFilePath = tempfileDir+"/"+fileName+".pdf"; try { File pdfFile = exportExcel.excelToPdf(excelfilePath, pdfFilePath); byte[] fileByte = toByteArray(pdfFile); String uploadFileId = fileService.uploadFile(BusinessTypeConstant.BUSINESS_TYPE_REGISTER, new FileObject(pdfFile.getName(),fileByte)); if(StringUtils.isEmpty(uploadFileId)){ logger.info("调用生成用户名册异常,上传文件异常"); throw new BusinessException(ErrorEnum.ERROR_PARAM,"生成仲裁员名册异常"); } List<CaseDocument> arbitratorsFileData = caseDocumentMapper.findArbitratorsFileData("102"); if(CollectionUtil.isBlank(arbitratorsFileData)){ CaseDocument caseDocument = new CaseDocument(); caseDocument.setState((byte) 1); caseDocument.setCreatetime(new Date()); caseDocument.setType((byte)102); caseDocument.setFileid(Integer.valueOf(uploadFileId)); caseDocument.setSource(1); int num = caseDocumentMapper.insertSelective(caseDocument); if(num > 0){ result ="1"; } }else{ CaseDocument caseDocument = arbitratorsFileData.get(0); caseDocument.setFileid(Integer.valueOf(uploadFileId)); int num = caseDocumentMapper.updateByApprove(caseDocument); if(num >0 ){ result = "1"; } } file.delete(); pdfFile.delete(); // 删除临时文件 } catch (Exception e) { e.printStackTrace(); } return result; }
public static String getLocalTempfileDir(HttpServletRequest request) { //在根目录下创建一个tempfileDir的临时文件夹 String contextpath = request.getContextPath()+"/tempfileDir"; File f = new File(contextpath); if(!f.exists()){ f.mkdirs(); } return contextpath; }