ExcelUtil

package com.backstage.util;

import com.google.zxing.MultiFormatWriter;
import org.apache.poi.hssf.usermodel.;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.;

import javax.imageio.ImageIO;
import javax.imageio.stream.FileImageOutputStream;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.awt.geom.AffineTransform;
import java.awt.image.AffineTransformOp;
import java.awt.image.BufferedImage;
import java.io.*;
import java.sql.Blob;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/*

• poi导出工具类
  */
  public class ExcelUtil {

  public static void exportData(List<Map<String, Object>> volunteerMapList, HttpServletResponse response, HttpServletRequest request) throws Exception {
  String[] alias = {“二维码链接”, “礼品卡校验”, “礼品卡规格”};
  String[] keys = {“qRcode”, “giftCode”, “volumeType”};
  XSSFWorkbook workbook = new XSSFWorkbook();
  int sheetSize = volunteerMapList.size() + 50;
  double sheetNo = Math.ceil(volunteerMapList.size() / sheetSize);
  for (int index = 0; index <= sheetNo; index++) {
  XSSFSheet sheet = workbook.createSheet();
  workbook.setSheetName(index, “礼品券” + index);
  XSSFRow row = sheet.createRow(0);
  sheet.setColumnWidth(0, 2048);
  XSSFCell cell;
  XSSFCellStyle cellStyle = workbook.createCellStyle();
  XSSFFont font = workbook.createFont();
  font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
  // 居中
  cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
  // 加粗
  cellStyle.setFont(font);
  //创建标题
  for (int i = 0; i < alias.length; i++) {
  cell = row.createCell(i);
  cell.setCellValue(alias[i]);
  cell.setCellStyle(cellStyle);
  }
  int startNo = index * sheetSize;
  int endNo = Math.min(startNo + sheetSize, volunteerMapList.size());
  cellStyle = workbook.createCellStyle();
  // 居中
  cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
  cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
  // 写入各条记录,每条记录对应excel表中的一行
  for (int i = startNo; i < endNo; i++) {
  int rowNum = i + 1 - startNo;
  row = sheet.createRow(rowNum);
  Map<String, Object> map = (Map<String, Object>) volunteerMapList.get(i);
  for (int j = 0; j < keys.length; j++) {
  cell = row.createCell(j);
  String key = keys[j];
  // if (key.equals(“qRcode”)){
  // Object object = map.get(key);
  // byte[] blob = toByteArray(object);
  // String path = request.getSession().getServletContext().getRealPath("\image").replaceAll("\\","/");
  // sheet.addMergedRegion(new CellRangeAddress(i + 1,i + 1,i + 1,i + 1)) ;
  // // 头像
  // String imgPath = path + String.valueOf(i)+".png";
  // File photoFile = new File(imgPath) ;
  // if (photoFile.exists()){
  // BufferedImage bufferedImage = ImageIO.read(photoFile) ;
  // ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
  // ImageIO.write(bufferedImage, “png”, byteArrayOut);
  // byte[] data = byteArrayOut.toByteArray();
  // XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
  // XSSFClientAnchor anchor = new XSSFClientAnchor(480, 30, 700, 250, (short)0, i + 1, (short) 1, i + 2);
  // drawingPatriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
  // sheet.setColumnWidth((short)500, (short)500);
  // row.setHeight((short)500);
  // photoFile.delete();
  // } else {
  // cell.setCellType(XSSFCell.CELL_TYPE_STRING);
  // cell.setCellValue("");
  // }

// } else {
cell.setCellType(XSSFCell.CELL_TYPE_STRING);
Object value = map.get(key);
cell.setCellValue(value == null ? “” : value.toString());
cell.setCellStyle(cellStyle);
// }
}
}
// 设置列宽
for (int i = 1; i < alias.length; i++)
sheet.autoSizeColumn(i);
// 处理中文不能自动调整列宽的问题
setSizeColumn(sheet, alias.length);
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
workbook.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数
response.reset();
response.setContentType(“application/vnd.ms-excel;charset=utf-8”);
response.setHeader(“Content-Disposition”, “attachment;filename=” + new String((“礼品券” + “.xls”).getBytes(), “iso-8859-1”));
ServletOutputStream out = response.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (final IOException e) {
throw e;
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}

}

//导出活动礼品卡
public static void exportActiveCardData(List<Map<String, Object>> volunteerMapList, HttpServletResponse response, HttpServletRequest request) throws Exception {
    String[] alias = {"二维码"};
    String[] keys = {"qRcode"};
    XSSFWorkbook workbook = new XSSFWorkbook();
    int sheetSize = volunteerMapList.size() + 50;
    double sheetNo = Math.ceil(volunteerMapList.size() / sheetSize);
    for (int index = 0; index <= sheetNo; index++) {
        XSSFSheet sheet = workbook.createSheet();
        workbook.setSheetName(index, "活动礼品券" + index);
        XSSFRow row = sheet.createRow(0);
        sheet.setColumnWidth(0, 2048);
        XSSFCell cell;
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
        // 居中
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        // 加粗
        cellStyle.setFont(font);
        //创建标题
        for (int i = 0; i < alias.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(alias[i]);
            cell.setCellStyle(cellStyle);
        }
        int startNo = index * sheetSize;
        int endNo = Math.min(startNo + sheetSize, volunteerMapList.size());
        cellStyle = workbook.createCellStyle();
        // 居中
        cellStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
        // 写入各条记录,每条记录对应excel表中的一行
        for (int i = startNo; i < endNo; i++) {
            int rowNum = i + 1 - startNo;
            row = sheet.createRow(rowNum);
            Map<String, Object> map = (Map<String, Object>) volunteerMapList.get(i);
            for (int j = 0; j < keys.length; j++) {
                cell = row.createCell(j);
                String key = keys[j];
                if (key.equals("qRcode")) {
                    Object object = map.get(key);
                    byte[] blob = toByteArray(object);
                    String path = request.getSession().getServletContext().getRealPath("\\image").replaceAll("\\\\", "/");
                    sheet.addMergedRegion(new CellRangeAddress(i + 1, i + 1, i + 1, i + 1));
                    // 头像
                    String imgPath = path + String.valueOf(i) + ".png";
                    File photoFile = new File(imgPath);
                    if (photoFile.exists()) {
                        BufferedImage bufferedImage = ImageIO.read(photoFile);
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        ImageIO.write(bufferedImage, "png", byteArrayOut);
                        byte[] data = byteArrayOut.toByteArray();
                        XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
                        XSSFClientAnchor anchor = new XSSFClientAnchor(480, 30, 700, 250, (short) 0, i + 1, (short) 1, i + 2);
                        drawingPatriarch.createPicture(anchor, workbook.addPicture(data, XSSFWorkbook.PICTURE_TYPE_JPEG));
                        sheet.setColumnWidth((short) 500, (short) 500);
                        row.setHeight((short) 500);
                        photoFile.delete();
                    } else {
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue("");
                    }

                } else {
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    Object value = map.get(key);
                    cell.setCellValue(value == null ? "" : value.toString());
                    cell.setCellStyle(cellStyle);
                }
            }
        }
        // 设置列宽
        for (int i = 1; i < alias.length; i++)
            sheet.autoSizeColumn(i);
        // 处理中文不能自动调整列宽的问题
        setSizeColumn(sheet, alias.length);
    }
    ByteArrayOutputStream os = new ByteArrayOutputStream();
    workbook.write(os);
    byte[] content = os.toByteArray();
    InputStream is = new ByteArrayInputStream(content);
    // 设置response参数
    response.reset();
    response.setContentType("application/vnd.ms-excel;charset=utf-8");
    response.setHeader("Content-Disposition", "attachment;filename=" + new String(("礼品券" + ".xls").getBytes(), "iso-8859-1"));
    ServletOutputStream out = response.getOutputStream();
    BufferedInputStream bis = null;
    BufferedOutputStream bos = null;
    try {
        bis = new BufferedInputStream(is);
        bos = new BufferedOutputStream(out);
        byte[] buff = new byte[2048];
        int bytesRead;
        while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
            bos.write(buff, 0, bytesRead);
        }
    } catch (final IOException e) {
        throw e;
    } finally {
        if (bis != null)
            bis.close();
        if (bos != null)
            bos.close();
    }

}

// 自适应宽度(中文支持)
private static void setSizeColumn(XSSFSheet sheet, int size) {
    for (int columnNum = 0; columnNum < size; columnNum++) {
        int columnWidth = sheet.getColumnWidth(columnNum) / 256;
        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
            XSSFRow currentRow;
            //当前行未被使用过
            if (sheet.getRow(rowNum) == null) {
                currentRow = sheet.createRow(rowNum);
            } else {
                currentRow = sheet.getRow(rowNum);
            }
            if (currentRow.getCell(columnNum) != null) {
                XSSFCell currentCell = currentRow.getCell(columnNum);
                if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    int length = currentCell.getStringCellValue().getBytes().length;
                    if (columnWidth < length) columnWidth = length;
                }
            }
        }
        columnWidth = columnWidth * 256;
        sheet.setColumnWidth(columnNum, columnWidth >= 65280 ? 6000 : columnWidth);
    }
}

public static byte[] toByteArray(Object obj) {
    byte[] bytes = null;
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    try {
        ObjectOutputStream oos = new ObjectOutputStream(bos);
        oos.writeObject(obj);
        oos.flush();
        bytes = bos.toByteArray();
        oos.close();
        bos.close();
    } catch (IOException ex) {
        ex.printStackTrace();
    }
    return bytes;
}


public static void byte2image(byte[] data, String path, int i) {
    try {
        String paths = path + String.valueOf(i) + ".png";
        FileImageOutputStream imageOutput = new FileImageOutputStream(new File(paths));
        imageOutput.write(data, 0, data.length);
        imageOutput.close();

// System.out.println("Make Picture success,Please find image in " + path);
} catch (Exception ex) {
System.out.println("Exception: " + ex);
ex.printStackTrace();
}

}


public static byte[] ChangeImgSize(byte[] data, int nw, int nh) {
    byte[] newdata = null;
    try {
        BufferedImage bis = ImageIO.read(new ByteArrayInputStream(data));
        int w = bis.getWidth();
        int h = bis.getHeight();
        double sx = (double) nw / w;
        double sy = (double) nh / h;
        AffineTransform transform = new AffineTransform();
        transform.setToScale(sx, sy);
        AffineTransformOp ato = new AffineTransformOp(transform, null);
        //原始颜色
        BufferedImage bid = new BufferedImage(nw, nh, BufferedImage.TYPE_3BYTE_BGR);
        ato.filter(bis, bid);
        //转换成byte字节
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        ImageIO.write(bid, "jpeg", baos);
        newdata = baos.toByteArray();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return newdata;
}

}