Excel工具类
来源：互联网 发布：lazada平台知乎 编辑：程序博客网 时间：2024/06/06 20:05
package Util;import java.awt.image.BufferedImage;import java.io.ByteArrayOutputStream;import java.io.IOException;import java.io.OutputStream;import java.lang.reflect.Method;import java.net.URL;import java.text.DateFormat;import java.text.SimpleDateFormat;import java.util.Date;import java.util.List;import java.util.Map;import java.util.Set;import javax.imageio.ImageIO;import javax.servlet.http.HttpServletResponse;import org.apache.commons.lang.StringUtils;import org.apache.poi.hssf.usermodel.HSSFClientAnchor;import org.apache.poi.hssf.usermodel.HSSFCreationHelper;import org.apache.poi.hssf.usermodel.HSSFPatriarch;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.util.IOUtils;public class ExcelUtil { public static final String EXPORT_PIC_SUFFIX = "PicUrl"; public static final String PIC_SUFFIX = "jpg"; public static OutputStream getOutputStreamForExcelExport( HttpServletResponse response, String fileType) { if (response == null) { return null; } OutputStream os = null; // HttpServletResponse response = ServletActionContext.getResponse(); try { os = response.getOutputStream(); } catch (IOException e) { // LogUtils.logException(e); } response.reset(); DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH_mm_ss"); String dateString = sdf.format(new Date()); response.setHeader("Content-disposition", "attachment; filename=\"" + fileType + dateString + ".xls\""); response.setContentType("application/msexcel"); return os; } /** * 根据反射针对list数据导出 * * @param maps * excel头部 * @param list * 需导出数据 * @param os * 输出流 * @param <T> * 数据类型 * @return 导出结果 */ @SuppressWarnings({ "unchecked", "RedundantArrayCreation" }) public static <T> boolean excelExport(Map<String, String> maps, List<T> list, OutputStream os) { try { HSSFWorkbook wb = new HSSFWorkbook(); HSSFCreationHelper createHelper = wb.getCreationHelper(); HSSFSheet sheet = wb.createSheet("sheet1"); // 只需申明一次，导出图片使用 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); Set<String> sets = maps.keySet(); Row row = sheet.createRow(0); int i = 0; // 定义表头 for (String key : sets) { Cell cell = row.createCell(i++); cell.setCellValue(createHelper.createRichTextString(maps .get(key))); } // 填充表单内容 float avg = list.size() / 20f; int count = 1; for (int j = 0; j < list.size(); j++) { T p = list.get(j); Class classType = p.getClass(); int index = 0; Row row1 = sheet.createRow(j + 1); for (String key : sets) { String firstLetter = key.substring(0, 1).toUpperCase(); String getMethodName = "get" + firstLetter + key.substring(1); Method getMethod = classType.getMethod(getMethodName, new Class[] {}); Object value = getMethod.invoke(p, new Object[] {}); if (StringUtils.isNotBlank(key) && key.endsWith(EXPORT_PIC_SUFFIX)) {// 导出图片 if (null != value && value instanceof String) { try { patriarch .createPicture( createClientAnchor( createHelper, j + 1, index), wb.addPicture( toByteImage( (String) value, PIC_SUFFIX), HSSFWorkbook.PICTURE_TYPE_JPEG)); } catch (Throwable e) { } } index++; } else { Cell cell = row1.createCell(index++); cell.setCellType(Cell.CELL_TYPE_STRING); if (null != value) { if (value instanceof String) { cell.setCellValue(createHelper .createRichTextString((String) value)); } else if (value instanceof Date) { /*cell.setCellValue(createHelper.createRichTextString(PmsDateTimeUtil .dateToString( (Date) value, PmsDateTimeUtil.DATA_FORMAT_yyyy_MM_dd_HH_mm_ss)));*/ } else { cell.setCellValue(createHelper .createRichTextString(String .valueOf(value))); } } } } if (j > avg * count) { count++; } if (count == 20) { count++; } } wb.write(os); os.close(); } catch (Exception e) { //PmsLogRecord.logException(e); System.out.println(e.getMessage()); return false; } return true; } public static byte[] toByteImage(String url, String suffix) throws IOException { ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new URL(url)); ImageIO.write(bufferImg, suffix, byteArrayOut); byte[] result = byteArrayOut.toByteArray(); byteArrayOut.close(); return result; } /** * 生成excel表格中图片位置anchor * * @param createHelper 工厂 * @param row 行 * @param col 列 * @return anchor */ private static HSSFClientAnchor createClientAnchor(HSSFCreationHelper createHelper, int row, int col) { HSSFClientAnchor anchor = createHelper.createClientAnchor(); anchor.setDx1(0); anchor.setDy1(0); anchor.setDx2(1023); anchor.setDy2(255); anchor.setCol1(col); anchor.setRow1(row); anchor.setCol2(col); anchor.setRow2(row); return anchor; }}<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>test</groupId> <artifactId>test</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>war</packaging> <name>test</name> <description /> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <spring-version>4.0.7.RELEASE</spring-version> </properties> <dependencies> <dependency> <groupId>org.glassfish</groupId> <artifactId>bean-validator</artifactId> <version>3.0-JBoss-4.0.2</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.enterprise.deploy</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.jms</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.management.j2ee</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.eclipse.persistence</groupId> <artifactId>javax.persistence</artifactId> <version>2.0.0</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.resource</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.security.auth.message</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.security.jacc</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.servlet</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.servlet.jsp</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish</groupId> <artifactId>javax.servlet.jsp.jstl</artifactId> <version>3.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.xml.bind</groupId> <artifactId>jaxb-api-osgi</artifactId> <version>2.2.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.ws.rs</groupId> <artifactId>jsr311-api</artifactId> <version>1.1.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish.web</groupId> <artifactId>jstl-impl</artifactId> <version>1.2</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.mail</groupId> <artifactId>mail</artifactId> <version>1.4.3</version> <scope>provided</scope> </dependency> <dependency> <groupId>javax.xml</groupId> <artifactId>webservices-api-osgi</artifactId> <version>2.0.1</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.jboss.weld</groupId> <artifactId>weld-osgi-bundle</artifactId> <version>1.0.1-SP3</version> <scope>provided</scope> </dependency> <dependency> <groupId>org.glassfish.web</groupId> <artifactId>javax.servlet.jsp.jstl</artifactId> <version>1.2.1</version> </dependency> <!-- spring 依赖配置 --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-web</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-beans</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aspects</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-orm</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-oxm</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jms</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${spring-version}</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> <version>2.4.0</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>2.4.0</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>4.3.3.Final</version> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-entitymanager</artifactId> <version>4.3.3.Final</version> </dependency> <dependency> <groupId>net.sf.json-lib</groupId> <artifactId>json-lib</artifactId> <version>2.4</version> <classifier>jdk15</classifier> </dependency> <!--excel --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> </dependencies> <build> <plugins> <plugin> <artifactId>maven-compiler-plugin</artifactId> <version>2.3.2</version> <configuration> <source>1.6</source> <target>1.6</target> </configuration> </plugin> <plugin> <artifactId>maven-war-plugin</artifactId> <version>2.2</version> <configuration> <version>3.0</version> <failOnMissingWebXml>false</failOnMissingWebXml> </configuration> </plugin> </plugins> </build></project> 0 0
- excel EXCEL报表工具类.
- 读取Excel工具类
- Jeecg Excel 工具类
- 解析Excel工具类
- Excel工具类
- java excel工具类
- POI Excel 工具类
- 导出Excel工具类
- Excel工具类 POIExcelUtil
- 读取excel 工具类
- ExcelUtil excel工具类
- excel工具类
- 导出Excel工具类
- Excel导出工具类
- 导出Excel工具类
- excel解析工具类
- Excel工具类
- Excel导出工具类.
- SNMP 服务启用方法
- 详解WMware Workstation的三种网络配置方式
- 最近做easy-ui要用到的formatter()
- linux驱动移植（nand，yaffs2） MTD分区
- 拉格朗日插值 python scipy
- Excel工具类
- ListView学习
- GitHub控件之BadgeView（数字提醒）
- 蓝桥杯 密文搜索
- BZOJ 1079 记忆化搜索
- 如何设计出一些优雅的API接口呢？
- Unity手动控制动画播放
- 如何查看某个查询用了多少TempDB空间
- 虚拟存储
