1、前提:在HttpServlet中的service()等方法中使用。
2、
- package com.isoftstone.runbat.util;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.OutputStream;
- import java.math.BigDecimal;
- import java.sql.Date;
- import java.sql.ResultSetMetaData;
- import java.util.zip.ZipEntry;
- import java.util.zip.ZipOutputStream;
- import javax.sql.RowSet;
- import org.apache.poi.hssf.usermodel.HSSFCell;
- import org.apache.poi.hssf.usermodel.HSSFCellStyle;
- import org.apache.poi.hssf.usermodel.HSSFFont;
- import org.apache.poi.hssf.usermodel.HSSFRow;
- import org.apache.poi.hssf.usermodel.HSSFSheet;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import org.slf4j.Logger;
- import org.slf4j.LoggerFactory;
- import oracle.sql.TIMESTAMP;
- import com.isoftstone.fwk.util.SpringUtils;
- public class ExportExcelUtil {
- private static Logger logger = LoggerFactory.getLogger(ExportExcelUtil.class);
- /**
- * @param sql
- * 查询数据的sql
- * @param outputStream
- * 输出流
- * @param sheetName
- * 工作薄中工作表的名字
- */
- public void exportExcel(String sql, OutputStream outputStream,String sheetName) {
- HSSFWorkbook wb = new HSSFWorkbook();// 声明一个工作薄
- HSSFCellStyle style = wb.createCellStyle(); // 一个样式
- style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置垂直居中
- style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
- HSSFFont headerFont = (HSSFFont) wb.createFont(); // 创建字体样式
- headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 字体加粗
- headerFont.setFontName("Times New Roman"); // 设置字体类型
- headerFont.setFontHeightInPoints((short) 8); // 设置字体大小
- style.setFont(headerFont); // 为标题样式设置字体样式
- /*
- * style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
- * style.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
- * style.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
- * style.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
- */
- style.setWrapText(true);
- HSSFSheet sheet = wb.createSheet(sheetName);// 声明工作薄里中一个工作表的名字
- sheet.setDefaultColumnWidth(30);// 给工作一个默认长度
- HSSFRow row = sheet.createRow(0); // 创建第一行(也可以称为表头)
- try {
- // 通过传过来的sql获得数据
- RowSet rowSet = SpringUtils.getCommonDao().queryNativeSQL(sql,null, false);
- ResultSetMetaData columnNames = rowSet.getMetaData();
- int columnValues = columnNames.getColumnCount();
- for (int i = 0; i < columnValues; i++) {
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style);
- cell.setCellValue(columnNames.getColumnName(i + 1));
- }
- int rowNo = 1;
- int m = 1;
- /*
- * HSSFFont contextFont = (HSSFFont) wb.createFont(); //创建字体样式
- * contextFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//字体加粗
- * style.setFont(contextFont);
- */
- while (rowSet.next()) {
- row = sheet.createRow(rowNo);
- for (int columnNo = 0; columnNo < columnValues; columnNo++) {
- HSSFCell cell = row.createCell(columnNo);
- // cell.setCellStyle(style);
- Object columnValue = rowSet.getObject(columnNo + 1);
- if (columnValue == null) {
- continue;
- }
- if (columnValue instanceof String) {
- cell.setCellValue(rowSet.getString(columnNo + 1));
- } else if (columnValue instanceof Integer) {
- cell.setCellValue(rowSet.getInt(columnNo + 1));
- } else if (columnValue instanceof Double) {
- cell.setCellValue(rowSet.getDouble(columnNo + 1));
- } else if (columnValue instanceof Date) {
- cell.setCellValue(rowSet.getDate(columnNo + 1));
- } else if (columnValue instanceof TIMESTAMP) {
- cell.setCellValue(rowSet.getTimestamp(columnNo + 1));
- } else if (columnValue instanceof BigDecimal) {
- cell.setCellValue(rowSet.getBigDecimal(columnNo + 1).doubleValue());
- }
- }
- if (rowNo % 60000 == 0) {
- m++;
- sheet = wb.createSheet(sheetName + m);// 声明工作薄里中一个工作表的名字
- sheet.setDefaultColumnWidth(30);// 给工作一个默认长度
- row = sheet.createRow(0); // 创建第一行(也可以称为表头)
- for (int i = 0; i < columnValues; i++) {
- HSSFCell cell = row.createCell(i);
- cell.setCellStyle(style);
- cell.setCellValue(columnNames.getColumnName(i + 1));
- }
- rowNo = 0;
- }
- rowNo++;
- }
- String name = "batch_excel";
- File fileBatch = new File(this.getPath()+"batch_excel");
- if(!fileBatch.exists()){
- fileBatch.mkdir();
- }
- //默认导出到D盘下
- FileOutputStream out = new FileOutputStream(fileBatch+"/"+name+".xls");
- wb.write(out);
- out.flush();
- out.close();// 关闭流
- logger.info("导出"+sheetName+"成功");
- this.putExcelToZip(sheetName,name,outputStream);
- } catch (Exception e) {
- e.printStackTrace();
- logger.error("导出"+sheetName+"失败");
- }
- }
- public void putExcelToZip(String sheetName,String name,OutputStream outputStream) throws IOException{
- File file = new File(this.getPath()+"batch_excel/"+name+".xls");
- //File zipFile = new File(this.getPath()+"batch_excel/"+sheetName+".zip");
- InputStream input = new FileInputStream(file);
- ZipOutputStream zipOut = new ZipOutputStream(outputStream);
- ZipEntry zipEntry = new ZipEntry(file.getName());
- zipOut.putNextEntry(zipEntry);
- // 设置注释
- zipOut.setComment("hello");
- int temp = 0;
- while ((temp = input.read()) != -1) {
- zipOut.write(temp);
- }
- if(file.isFile()){
- file.delete();
- logger.info("删除excel文件");
- }
- input.close();
- zipOut.close();
- logger.info("保存zip文件");
-
- }
- public String getPath(){
- String path = ExportExcelUtil.class.getResource("/").getPath();
- path = path.substring(0, path.indexOf("WEB-INF"));
- return path;
- }
- }