package cn.enn.edata.util; import java.awt.image.BufferedImage; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.net.HttpURLConnection; import java.net.URL; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Calendar; import java.util.Collection; import java.util.GregorianCalendar; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.regex.Matcher; import java.util.regex.Pattern; import java.util.zip.ZipEntry; import java.util.zip.ZipOutputStream; import javax.imageio.ImageIO; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import org.apache.commons.io.FileUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFRichTextString; 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.HSSFColor; import cn.enn.edata.entity.Pipeprocostqz; public class ExportExcels<T> { public void exportExcel(Collection<T> dataset, OutputStream out, String sheetname, List<String> fieldlist, Map map, List<String> wxgshlist) { exportExcel(sheetname, null, dataset, out, "yyyy-MM-dd", fieldlist, map, wxgshlist); } public void exportExcel(String[] headers, Collection<T> dataset, OutputStream out, String sheetname, List<String> fieldlist, Map map, List<String> wxgshlist) { exportExcel(sheetname, headers, dataset, out, "yyyy-MM-dd", fieldlist, map, wxgshlist); } public void exportExcel(String[] headers, Collection<T> dataset, OutputStream out, String pattern, String sheetname, List<String> fieldlist, Map map, List<String> wxgshlist) { exportExcel(sheetname, headers, dataset, out, pattern, fieldlist, map, wxgshlist); } /** * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * * @param title * 表格标题名 * @param headers * 表格属性列名数组 * @param dataset * 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) * @param out * 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据,设定输出格式。默认为"yyy-MM-dd" * @return */ @SuppressWarnings("unchecked") public void exportExcel(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern, List<String> fieldlist, Map map, List<String> wxgshlist) { String returnstr = ""; // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); try { // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); sheet.createFreezePane(0, 1, 0, 1);// 冻结表头 // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); sheet.setColumnWidth(0, 60 * 256);// 设置第一列宽度 // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_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.VIOLET.index); font.setFontHeightInPoints((short) 10);// 字体高度 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 // HSSFComment comment = patriarch.createComment(new // HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 // comment.setString(new HSSFRichTextString("注释!")); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. // comment.setAuthor(""); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; HSSFFont font3 = workbook.createFont(); while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); String result = ""; if (t instanceof Map) { if (t != null) { Iterator<Map.Entry<String, String>> itor = ((Map<String, String>) t).entrySet().iterator(); while (itor.hasNext()) { Entry<String, String> entry = itor.next(); System.out.println(entry.getKey().toLowerCase()); if (fieldlist.contains(entry.getKey().toLowerCase())) { int idf = fieldlist.indexOf(entry.getKey().toLowerCase()); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); cell.setCellValue(entry.getValue()); result += entry.getKey().toLowerCase() + ","; } } } } else { // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); for (short i = 0; i < fields.length; i++) { Field field = fields[i]; String fieldName = field.getName(); if (fieldlist.contains(fieldName)) { String res = ""; int idf = fieldlist.indexOf(fieldName); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Integer) { int intValue = (Integer) value; textValue = intValue + ""; cell.setCellValue(textValue); } else if (value instanceof Float) { float fValue = (Float) value; textValue = fValue + ""; cell.setCellValue(textValue); } else if (value instanceof Double) { double dValue = (Double) value; textValue = dValue + ""; cell.setCellValue(textValue); } else if (value instanceof Long) { long longValue = (Long) value; cell.setCellValue(longValue); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); res = GeneratedValue(fieldName, textValue, map); if (res.contains("图片url")) { String fileurl = res.substring(6, res.length()); File jpgfile = new File(fileurl);// barName // 为统计图片在服务器上的路径 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();// 字节输出流,用来写二进制文件 BufferedImage bufferImg = ImageIO.read(jpgfile); ImageIO.write(bufferImg, "png", byteArrayOut); // 有图片时,设置行高为60px; row.setHeightInPoints(30); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (35.7 * 80)); // sheet.autoSizeColumn(i); // HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下: // dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离; // dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离; // dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离; // dy2:终止单元s格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离; // col1:起始单元格列序号,从0开始计算; // row1:起始单元格行序号,从0开始计算,如例子中col1=0,row1=0就表示起始单元格为A1; // col2:终止单元格列序号,从0开始计算; // row2:终止单元格行序号,从0开始计算,如例子中col2=2,row2=2就表示起始单元格为C3; HSSFClientAnchor anchor = new HSSFClientAnchor(350, 50, 800, 230, (short) idf, index, (short) idf, index); // anchor.setAnchorType(2); patriarch.createPicture(anchor, workbook.addPicture( byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));// 将统计图片添加到Excel文件中 } else if (res.contains("imageid") || res.contains("origin_photos")) { textValue = "http://10.37.47.49:8080/ImageUpload/imageinfo/" + textValue; cell.setCellValue(textValue); } else { cell.setCellValue(res); } } else if (value == null) { textValue = ""; cell.setCellValue(textValue); } } // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else if (!res.contains("图片url")) { HSSFRichTextString richString = new HSSFRichTextString(textValue); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } result += fieldName + ","; } } setValue(fieldlist, result, wxgshlist, row, style2); } try { workbook.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } } catch (Exception e) { } } /** * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * * @param headers * 表格属性列名数组 * @param dataset * 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) * @param out * 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据,设定输出格式。默认为"yyy-MM-dd" * @return */ @SuppressWarnings("unchecked") public void exportExcelZIP(String title,String[] headers, Collection<T> dataset, OutputStream out, String pattern, List<String> fieldlist, Map map, List<String> wxgshlist,HttpServletRequest request) { String path=request.getSession().getServletContext().getRealPath(""); Calendar c = Calendar.getInstance();//可以对每个时间域单独修改 int year = c.get(Calendar.YEAR); int month = c.get(Calendar.MONTH); int date = c.get(Calendar.DATE); int hour = c.get(Calendar.HOUR_OF_DAY); int minute = c.get(Calendar.MINUTE); int second = c.get(Calendar.SECOND); path=path+"/down/similay/"+year + month + date +hour +minute + second+"/"; String path1=path; String returnstr = ""; List<File> fileList = new ArrayList<File>(); // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); try { // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); sheet.createFreezePane(0, 1, 0, 1);// 冻结表头 // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); sheet.setColumnWidth(0, 60 * 256);// 设置第一列宽度 // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_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.VIOLET.index); font.setFontHeightInPoints((short) 10);// 字体高度 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 // HSSFComment comment = patriarch.createComment(new // HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 // comment.setString(new HSSFRichTextString("注释!")); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. // comment.setAuthor(""); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; HSSFFont font3 = workbook.createFont(); while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); String result = ""; if (t instanceof Map) { if (t != null) { Iterator<Map.Entry<String, String>> itor = ((Map<String, String>) t).entrySet().iterator(); while (itor.hasNext()) { Entry<String, String> entry = itor.next(); System.out.println(entry.getKey().toLowerCase()); if (fieldlist.contains(entry.getKey().toLowerCase())) { int idf = fieldlist.indexOf(entry.getKey().toLowerCase()); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); cell.setCellValue(entry.getValue()); result += entry.getKey().toLowerCase() + ","; } } } } else { // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); for (short i = 0; i < fields.length; i++) { Field field = fields[i]; String fieldName = field.getName(); if (fieldlist.contains(fieldName)) { String res = ""; int idf = fieldlist.indexOf(fieldName); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Integer) { int intValue = (Integer) value; textValue = intValue + ""; cell.setCellValue(textValue); } else if (value instanceof Float) { float fValue = (Float) value; textValue = fValue + ""; cell.setCellValue(textValue); } else if (value instanceof Double) { double dValue = (Double) value; textValue = dValue + ""; cell.setCellValue(textValue); } else if (value instanceof Long) { long longValue = (Long) value; cell.setCellValue(longValue); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); res = GeneratedValue(fieldName, textValue, map); if (res.contains("图片url")) { String fileurl = res.substring(6, res.length()); File jpgfile = new File(fileurl);// barName // 为统计图片在服务器上的路径 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();// 字节输出流,用来写二进制文件 BufferedImage bufferImg = ImageIO.read(jpgfile); ImageIO.write(bufferImg, "png", byteArrayOut); // 有图片时,设置行高为60px; row.setHeightInPoints(30); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (35.7 * 80)); // sheet.autoSizeColumn(i); // HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下: // dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离; // dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离; // dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离; // dy2:终止单元s格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离; // col1:起始单元格列序号,从0开始计算; // row1:起始单元格行序号,从0开始计算,如例子中col1=0,row1=0就表示起始单元格为A1; // col2:终止单元格列序号,从0开始计算; // row2:终止单元格行序号,从0开始计算,如例子中col2=2,row2=2就表示起始单元格为C3; HSSFClientAnchor anchor = new HSSFClientAnchor(350, 50, 800, 230, (short) idf, index, (short) idf, index); // anchor.setAnchorType(2); patriarch.createPicture(anchor, workbook.addPicture( byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));// 将统计图片添加到Excel文件中 } else if (res.contains("imageid") || res.contains("origin_photos")) { String textValue_bak = textValue; textValue = "http://10.37.47.49:8080/ImageUpload/imageinfo/" + textValue; cell.setCellValue(textValue); String[] tp=textValue_bak.split("\\."); path=path1+getValues(tCls,t,"getCreateuser"); downUrlImage(textValue_bak,textValue,path,fileList,getValues(tCls,t,"getCreateuser"),path1); //fileList.add(new File(path)); }else if (res.contains("filename")) { String textValue_bak = textValue; textValue = "http://10.37.47.49:8080/ImageUpload/allimage/" + textValue; cell.setCellValue(textValue); String[] tp=textValue_bak.split("\\."); path=path1+getValues(tCls,t,"getGxname"); downUrlImage(textValue_bak,textValue,path,fileList,getValues(tCls,t,"getGxname"),path1); //fileList.add(new File(path)); }else { cell.setCellValue(res); } } else if (value == null) { textValue = ""; cell.setCellValue(textValue); } } // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else if (!res.contains("图片url")) { HSSFRichTextString richString = new HSSFRichTextString(textValue); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } result += fieldName + ","; } } setValue(fieldlist, result, wxgshlist, row, style2); } try { // workbook.write(out); FileOutputStream outf = null; // File file = File.createTempFile("工程质量相似图片列表信息", ".xls"); File fileexcel = new File(path1+"工程质量相似图片列表信息.xls"); outf = FileUtils.openOutputStream(fileexcel); workbook.write(outf);//将数据写到指定文件 // fileList.add(fileexcel); outf.close(); CompressBook z = new CompressBook(); //z.zip(path1, path1+"工程质量相似图片列表信息.zip"); // File[] files = new File(path1).listFiles(); // for(int x=0;x<files.length;x++){ // fileList.add(files[x]); // } File fileL=new File(path1); CompressBook.deleteAllFilesOfDir(fileL); File[] filezip = fileL.listFiles(); for(int x=0;x<filezip.length;x++){ fileList.add(filezip[x]); } toZip(fileList,out); out.close(); CompressBook.deleteAllFilesOfDir2(fileL); } catch (IOException e) { e.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } } public void excelimg(String title,String[] headers, Collection<T> dataset, OutputStream out, String pattern, List<String> fieldlist, Map map, List<String> wxgshlist,HttpServletRequest request){ String path=request.getSession().getServletContext().getRealPath(""); Calendar c = Calendar.getInstance();//可以对每个时间域单独修改 int year = c.get(Calendar.YEAR); int month = c.get(Calendar.MONTH); int date = c.get(Calendar.DATE); int hour = c.get(Calendar.HOUR_OF_DAY); int minute = c.get(Calendar.MINUTE); int second = c.get(Calendar.SECOND); path=path+"/down/similay/"+year + month + date +hour +minute + second+"/"; String path1=path; String returnstr = ""; List<File> fileList = new ArrayList<File>(); // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); try { // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); sheet.createFreezePane(0, 1, 0, 1);// 冻结表头 // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); sheet.setColumnWidth(0, 60 * 256);// 设置第一列宽度 // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.SKY_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.VIOLET.index); font.setFontHeightInPoints((short) 10);// 字体高度 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 // HSSFComment comment = patriarch.createComment(new // HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 // comment.setString(new HSSFRichTextString("注释!")); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. // comment.setAuthor(""); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; HSSFFont font3 = workbook.createFont(); while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); String result = ""; if (t instanceof Map) { if (t != null) { Iterator<Map.Entry<String, String>> itor = ((Map<String, String>) t).entrySet().iterator(); while (itor.hasNext()) { Entry<String, String> entry = itor.next(); System.out.println(entry.getKey().toLowerCase()); if (fieldlist.contains(entry.getKey().toLowerCase())) { int idf = fieldlist.indexOf(entry.getKey().toLowerCase()); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); cell.setCellValue(entry.getValue()); result += entry.getKey().toLowerCase() + ","; } } } } else { // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); for (short i = 0; i < fields.length; i++) { Field field = fields[i]; String fieldName = field.getName(); if (fieldlist.contains(fieldName)) { String res = ""; int idf = fieldlist.indexOf(fieldName); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; if (value instanceof Integer) { int intValue = (Integer) value; textValue = intValue + ""; cell.setCellValue(textValue); } else if (value instanceof Float) { float fValue = (Float) value; textValue = fValue + ""; cell.setCellValue(textValue); } else if (value instanceof Double) { double dValue = (Double) value; textValue = dValue + ""; cell.setCellValue(textValue); } else if (value instanceof Long) { long longValue = (Long) value; cell.setCellValue(longValue); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); res = GeneratedValue(fieldName, textValue, map); if (res.contains("图片url")) { String fileurl = res.substring(6, res.length()); File jpgfile = new File(fileurl);// barName // 为统计图片在服务器上的路径 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();// 字节输出流,用来写二进制文件 BufferedImage bufferImg = ImageIO.read(jpgfile); ImageIO.write(bufferImg, "png", byteArrayOut); // 有图片时,设置行高为60px; row.setHeightInPoints(30); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, (short) (35.7 * 80)); // sheet.autoSizeColumn(i); // HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数,有必要在这里说明一下: // dx1:起始单元格的x偏移量,如例子中的255表示直线起始位置距A1单元格左侧的距离; // dy1:起始单元格的y偏移量,如例子中的125表示直线起始位置距A1单元格上侧的距离; // dx2:终止单元格的x偏移量,如例子中的1023表示直线起始位置距C3单元格左侧的距离; // dy2:终止单元s格的y偏移量,如例子中的150表示直线起始位置距C3单元格上侧的距离; // col1:起始单元格列序号,从0开始计算; // row1:起始单元格行序号,从0开始计算,如例子中col1=0,row1=0就表示起始单元格为A1; // col2:终止单元格列序号,从0开始计算; // row2:终止单元格行序号,从0开始计算,如例子中col2=2,row2=2就表示起始单元格为C3; HSSFClientAnchor anchor = new HSSFClientAnchor(350, 50, 800, 230, (short) idf, index, (short) idf, index); // anchor.setAnchorType(2); patriarch.createPicture(anchor, workbook.addPicture( byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));// 将统计图片添加到Excel文件中 } else if (res.contains("filename") || res.contains("origin_photos")) { String textValue_bak = textValue; textValue = "http://10.37.47.49:8080/ImageUpload/allimage/" + textValue; cell.setCellValue(textValue); String[] tp=textValue_bak.split("\\."); path=path1+getValues(tCls,t,"getGxname"); downUrlImage(textValue_bak,textValue,path,fileList,getValues(tCls,t,"getGxname"),path1); //fileList.add(new File(path)); } else { cell.setCellValue(res); } } else if (value == null) { textValue = ""; cell.setCellValue(textValue); } } // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else if (!res.contains("图片url")) { HSSFRichTextString richString = new HSSFRichTextString(textValue); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } result += fieldName + ","; } } setValue(fieldlist, result, wxgshlist, row, style2); } try { // workbook.write(out); FileOutputStream outf = null; // File file = File.createTempFile("工程质量相似图片列表信息", ".xls"); File fileexcel = new File(path1+"图片分析列表信息.xls"); outf = FileUtils.openOutputStream(fileexcel); workbook.write(outf);//将数据写到指定文件 // fileList.add(fileexcel); outf.close(); CompressBook z = new CompressBook(); //z.zip(path1, path1+"工程质量相似图片列表信息.zip"); // File[] files = new File(path1).listFiles(); // for(int x=0;x<files.length;x++){ // fileList.add(files[x]); // } File fileL=new File(path1); CompressBook.deleteAllFilesOfDir(fileL); File[] filezip = fileL.listFiles(); for(int x=0;x<filezip.length;x++){ fileList.add(filezip[x]); } toZip(fileList,out); out.close(); CompressBook.deleteAllFilesOfDir2(fileL); } catch (IOException e) { e.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); } } public String getValues(Class tCls,T t ,String name){ String ret=""; try { Method getMethod = tCls.getMethod(name, new Class[]{}); Object value = getMethod.invoke(t, new Object[]{}); if(value!=null) ret=value.toString(); } catch (Exception e) { e.printStackTrace(); } return ret; } public void downUrlImage(String fileName,String fileUrl,String downPath,List<File> fileList,String username,String path){ File savePath = new File(downPath); if (!savePath.exists()) { savePath.mkdirs(); } String[] urlname = fileUrl.split("/"); int len = urlname.length-1; String uname = urlname[len];//获取文件名 try { File file = new File(savePath+"/"+uname);//创建新文件 if(file!=null && !file.exists()){ file.createNewFile(); } OutputStream oputstream = new FileOutputStream(file); URL url = new URL(fileUrl); HttpURLConnection uc = (HttpURLConnection) url.openConnection(); uc.setDoInput(true);//设置是否要从 URL 连接读取数据,默认为true uc.connect(); InputStream iputstream = uc.getInputStream(); System.out.println("file size is:"+uc.getContentLength());//打印文件长度 byte[] buffer = new byte[4*1024]; int byteRead = -1; while((byteRead=(iputstream.read(buffer)))!= -1){ oputstream.write(buffer, 0, byteRead); } oputstream.flush(); iputstream.close(); oputstream.close(); new CompressBook().zip(savePath,path+"\\"+username+".zip"); //fileList.add(file); } catch (Exception e) { System.out.println("读取失败!"); e.printStackTrace(); } System.out.println("生成文件路径:"+downPath+fileName); } public static OutputStream getout(HttpServletResponse response, String filename) { OutputStream out = null; try { try { filename = new String((filename + ".xls").getBytes(), "iso-8859-1"); } catch (UnsupportedEncodingException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } // 中文文件名必须使用此句话 response.setContentType("application/octet-stream"); response.setContentType("application/OCTET-STREAM;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + filename); out = new BufferedOutputStream(response.getOutputStream()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return out; } public static OutputStream getoutZIP(HttpServletResponse response, String filename) { OutputStream out = null; try { try { filename = new String((filename + ".zip").getBytes(), "iso-8859-1"); } catch (UnsupportedEncodingException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } // 中文文件名必须使用此句话 response.setContentType("application/octet-stream"); response.setContentType("application/OCTET-STREAM;charset=UTF-8"); response.setHeader("Content-Disposition", "attachment;filename=" + filename); out = new BufferedOutputStream(response.getOutputStream()); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return out; } private void toZip(List<File> fileList,OutputStream out) throws IOException{ if(null != fileList && !fileList.isEmpty()){ byte[] buf = new byte[1024]; ZipOutputStream zipOut = new ZipOutputStream(out); for(File file : fileList){ FileInputStream in = new FileInputStream(file); zipOut.putNextEntry(new ZipEntry(file.getName())); int len; while((len = in.read(buf)) > 0){ zipOut.write(buf, 0, len); } zipOut.closeEntry(); in.close(); file.delete();//清除临时文档 } zipOut.close(); } } public String GeneratedValue(String fieldName, String value, Map map) { String retval = ""; // -----------------------------------值类型转换比如数据库中存的是1页面显示“是”------------------------------------------------ if (map != null) { Iterator<Map.Entry<String, Map>> itor = map.entrySet().iterator(); while (itor.hasNext()) { Entry<String, Map> entry = itor.next(); if (fieldName.equals(entry.getKey().toString())) { Map<String, String> secd = entry.getValue(); Iterator<Map.Entry<String, String>> secditor = secd.entrySet().iterator(); while (secditor.hasNext()) { Entry<String, String> secdentry = secditor.next(); if (value != null) { if (secdentry.getKey().equals(value)) { retval = secdentry.getValue(); } if (retval.equals("") && !secdentry.getKey().equals(value)) { retval = secdentry.getKey(); } } } } } } else { retval = value; } // ----------------------------------------------------------------------------------- return retval; } // -------------------------start-概览页面五小工商户中有字段不在实体中,是计算出来的需额外处理---------------------------------- public void setValue(List<String> fieldlist, String result, List<String> wxgshlist, HSSFRow row, HSSFCellStyle style2) { if (wxgshlist.size() > 0) { for (int j = 0; j < fieldlist.size(); j++) { if (!result.contains(fieldlist.get(j))) { try { if (wxgshlist.contains(fieldlist.get(j))) { HSSFCell cell = row.createCell(5); cell.setCellStyle(style2); String star = row.getCell(3).getStringCellValue(); String endstr = row.getCell(4).getStringCellValue(); if (!star.equals("") && !endstr.equals("")) { star = star.replaceAll("-", "."); endstr = endstr.replaceAll("-", "."); cell.setCellValue(getday(star, endstr) + ""); } } else { int idf = fieldlist.indexOf(fieldlist.get(j)); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); } } catch (Exception e) { } } } } else { for (int j = 0; j < fieldlist.size(); j++) { if (!result.contains(fieldlist.get(j))) { try { if (!wxgshlist.contains(fieldlist.get(j))) { int idf = fieldlist.indexOf(fieldlist.get(j)); HSSFCell cell = row.createCell(idf); cell.setCellStyle(style2); } } catch (Exception e) { } } } } } public static int getday(String str, String end) { int day = 0; Calendar calendar = new GregorianCalendar(); Calendar calendar2 = Calendar.getInstance(); // 通过SimpleDateFormat将字符串解析为Date类型 SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd"); try { sdf.parse(str); sdf.parse(end); // 将Date类型放入Calendar calendar.setTime(sdf.parse(str)); calendar2.setTime(sdf.parse(end)); while (calendar2.compareTo(calendar) > 0) { // Calendar类型中的日期+1 calendar.add(Calendar.DATE, 1); day++; } } catch (ParseException e) { // TODO Auto-generated catch block e.printStackTrace(); } return day; } /** * 把Null转换成"-" * @param dataset * @param clas * @return * @throws InstantiationException * @throws NoSuchFieldException * @throws InvocationTargetException * @throws NoSuchMethodException */ public List<T> toNullRod(Collection<T> dataset, Class clas){ List<T> list = (List<T>) dataset; try { Iterator<T> it = dataset.iterator(); List<String> fieldlsit = new ArrayList<String>(); while (it.hasNext()) { T t = (T) it.next(); Class tCls = t.getClass(); Field[] fields = t.getClass().getDeclaredFields(); Object obj = null; for (short i = 0; i < fields.length; i++) { Field field = fields[i]; String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); Method getMethod = tCls.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(t, new Object[] {}); if ((value != null && value.equals("null")) || value == null) { fieldlsit.add(fieldName); } } if(fieldlsit.size()>0){ for (int i = 0; i < fieldlsit.size(); i++) { String setAttributeMethodName = "set" + fieldlsit.get(i).substring(0, 1).toUpperCase() + fieldlsit.get(i).substring(1); Method setAttributeMethod = clas.getDeclaredMethod(setAttributeMethodName, String.class); setAttributeMethod.invoke(t, "-"); } } } } catch (Exception e) { } return list; } public static void main(String[] args) throws InstantiationException, InvocationTargetException, NoSuchMethodException { List<Pipeprocostqz> as = new ArrayList(); ExportExcels<Pipeprocostqz> ex = new ExportExcels<Pipeprocostqz>(); Pipeprocostqz qz = new Pipeprocostqz(); qz.setCcode("null"); as.add(qz); List<Pipeprocostqz> ts = ex.toNullRod(as, Pipeprocostqz.class); for (int i = 0; i < ts.size(); i++) { Pipeprocostqz ss = ts.get(i); System.out.println(ss.getCcode()); } } }
controller实现类:
/** * 项目部列表导出 * * @return */ @RequestMapping(value = "/selectXmblistExport", method = {RequestMethod.POST, RequestMethod.GET}) @ResponseBody public void selectXmblistExport(HttpServletResponse response, String lev, String aqname, String name, String desc, String pagenow, String year, String month, String jddate, String xmbcode, String bzcode) { try { year = URLDecoder.decode(URLDecoder.decode(year==null?"":year,"UTF-8")); month = URLDecoder.decode(URLDecoder.decode(month==null?"":month,"UTF-8")); jddate = URLDecoder.decode(URLDecoder.decode(jddate==null?"":jddate,"UTF-8")); xmbcode = URLDecoder.decode(URLDecoder.decode(xmbcode==null?"":xmbcode,"UTF-8")); name = URLDecoder.decode(URLDecoder.decode(name==null?"":name,"UTF-8")); desc = URLDecoder.decode(URLDecoder.decode(desc==null?"":desc,"UTF-8")); lev = URLDecoder.decode(URLDecoder.decode(lev==null?"":lev,"UTF-8")); aqname = URLDecoder.decode(URLDecoder.decode(aqname==null?"":aqname,"UTF-8")); bzcode = URLDecoder.decode(URLDecoder.decode(bzcode==null?"":bzcode,"UTF-8")); } catch (UnsupportedEncodingException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } Map map = new HashMap(); int pagesize = 10; int nowpage = Integer.parseInt(pagenow); int scol = 0; int ecol = pagesize * nowpage; map.put("scol",scol); map.put("ecol",ecol); map.put("lev",lev); map.put("aqname",aqname); map.put("name",name); map.put("desc",desc); if (!year.equals("") && month.equals("") && jddate.equals("")) { map.put("flg", "1"); map.put("dates", year); } if (!year.equals("") && month.equals("") && !jddate.equals("")) { map.put("flg", "2"); map.put("dates", year + "-" + jddate); } if (!year.equals("") && !month.equals("") && jddate.equals("")) { map.put("flg", "3"); map.put("dates", year + "-" + month); } map.put("xmbcode", xmbcode); map.put("bzcode", bzcode); JSONObject result = new JSONObject(); List<GcgsQuality> selectxmblist = gcgsqualservice.selectXmblist(map); GcgsExportExcel<GcgsQuality> ex = new GcgsExportExcel<GcgsQuality>(); String[] headers = { "项目名称", "项目类型", "整改负责人", "完成整改时间", "类型级别", "问题类型", "问题描述"}; List<String> fieldlist = new ArrayList<>(); fieldlist.add("name"); fieldlist.add("typeid"); fieldlist.add("gcjl"); fieldlist.add("dates"); fieldlist.add("lev"); fieldlist.add("aqname"); fieldlist.add("desccription"); Map map_key = null; List<String> noexistlist = new ArrayList<>(); OutputStream out = GcgsExportExcel.getout(response, "质量列表信息"); ex.exportExcel(headers, selectxmblist, out, "质量列表信息", fieldlist, map_key,noexistlist); // title是excel表中底部显示的表格名,如Sheet }