[util]java poi excel util

刚整出来自己用的excel 的工具类，暂时还没啥花里胡哨的功能，等有需求了再补充吧

 

package com.wkrj.module.commons.utils;

import com.wkrj.module.commons.exception.BussException;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.net.URL;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel Util
 * @qq 1092843362
 * @author gd
 */
public class ExcelImportUtil {


    /**
     * 读取excel中数据 ,一行数据对应Map,key值 'cell1'表示 该行 第一个单元格数据 ，key 值 index 表示行数 从0开始
     * @param fileType xls OR xlsx
     * @param inputStream
     * @param sheet sheet num,begin 0 to xxx
     * @param row start row ,begin 0 to xxx
     * @return  List<Map<String,Object>>
     */
    public static List<Map<String,Object>> getSheetListInfo(String fileType, InputStream inputStream,int sheet,int row){
        List<Map<String,Object>> data=new LinkedList<>();
        try{
            if("xls".equals(fileType.toLowerCase())){
                HSSFWorkbook hssfWorkbook=new HSSFWorkbook(inputStream);
                // 创建对工作表的引用
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheet);
                for (int rowNum = row; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow == null) {continue;}
                    Map<String,Object> rowInfo= ExcelImportUtil.getRowInfos(hssfRow);
                    rowInfo.put("index",rowNum+"");
                    data.add(rowInfo);
                }

            }else if("xlsx".equals(fileType.toLowerCase())){
                XSSFWorkbook xssfWorkbook=new XSSFWorkbook(inputStream);
                XSSFSheet xssfSheet=xssfWorkbook.getSheetAt(sheet);
                for (int rowNum = row; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {

                    XSSFRow xssfRow =xssfSheet.getRow(rowNum);
                    if (xssfRow == null) {continue;}
                    Map<String,Object> rowInfo= ExcelImportUtil.getRowInfos(xssfRow);
                    rowInfo.put("index",(rowNum+1)+"");
                    data.add(rowInfo);
                }
            }
        }catch (Exception e){
            e.printStackTrace();
            //自定义异常类
            throw new BussException("文件读取失败");
        }
        return  data;
    }




    public static void main(String[] args) throws Exception {

        JdbcTemplate jdbcTemplate= JdbcTemplateUitl.getSysJdbcTemplate();

        String sql="SELECT project_name,concat('http://xx.x.x.xx/' ,project_logo) project_logo,project_summary FROM wkrj_bus_project";

        List<Map<String,Object>> data= jdbcTemplate.queryForList(sql);

        FileOutputStream fileOut = null;

        //先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray


        HSSFWorkbook wb = new HSSFWorkbook();
        HSSFSheet sheet1 = wb.createSheet("demo");
        sheet1.setDefaultColumnWidth((short)20);
        sheet1.setDefaultRowHeight((short)2000);
        //画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
        HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
        Row row = null;
        BufferedImage bufferImg=null;
        ByteArrayOutputStream byteArrayOut=null;
        for(int i =0;i<100;i++){
            try {

                row = sheet1.createRow(i);

                row.createCell(0).setCellValue(DataHandleUtil.setValue(data.get(i).get("project_name")));
                row.createCell(2).setCellValue(DataHandleUtil.setValue(data.get(i).get("project_summary")));

                String imgUrl = data.get(i).get("project_logo") + "";
                URL url = new URL(imgUrl);
                //获取文件后缀名
                String suffix = imgUrl.substring(imgUrl.lastIndexOf(".") + 1);
                bufferImg = ImageIO.read(url);
                byteArrayOut = new ByteArrayOutputStream();
                ImageIO.write(bufferImg, suffix, byteArrayOut);
                //anchor主要用于设置图片的属性
                HSSFClientAnchor anchor =
                        new HSSFClientAnchor
                                (0, //x缩放
                                        0, // y缩放
                                        1023, //最大1023
                                        255, //最大255
                                        (short) 1,  //于下下个参数进行定位 0开始
                                        i, //在第几行
                                        (short) 1, //宽度占几格 0开始
                                        i //第几列
                                );
//          anchor.setAnchorType(3);
                //插入图片
                patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
            }catch (Exception e){
                e.printStackTrace();
                continue;
            }
        }


        fileOut = new FileOutputStream("D:/测试Excel.xls");
        // 写入excel文件
        wb.write(fileOut);
        System.out.println("----Excel文件已生成------");
    }





    /**
     * 查询一行内单元格 数据
     * @param xssfRow
     * @return
     */
    public static Map<String, Object> getRowInfos(XSSFRow xssfRow) {
        Map<String,Object> data=new HashMap<>();
        int cellNum=xssfRow.getLastCellNum();
        for(int i=1;i<=cellNum;i++){
            data.put("cell"+i,getStringValueFromCell(xssfRow.getCell(i-1)).trim());
        }
        return data;
    }
    /**
     * 查询一行内单元格 数据
     * @param hssfRow
     * @return
     */
    public static Map<String, Object> getRowInfos(HSSFRow hssfRow) {
        Map<String,Object> data=new HashMap<>();
        int cellNum=hssfRow.getLastCellNum();
        for(int i=1;i<=cellNum;i++){
            data.put("cell"+i,getStringValueFromCell(hssfRow.getCell(i-1)).trim());
        }
        return data;
    }
    /**
     * 获取单元格String 值
     * @param cell
     * @return
     */
    public static String getStringValueFromCell(Cell cell) {
        SimpleDateFormat sFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        DecimalFormat decimalFormat = new DecimalFormat("#.##");
        String cellValue = "";
        if(cell == null) {
            return cellValue;
        }
        else if(cell.getCellType() == CellType.STRING) {
            cellValue = cell.getStringCellValue();
        }

        else if(cell.getCellType() == CellType.NUMERIC) {
            if(HSSFDateUtil.isCellDateFormatted(cell)) {
                double d = cell.getNumericCellValue();
                Date date = HSSFDateUtil.getJavaDate(d);
                cellValue = sFormat.format(date);
            }
            else {
                cellValue = decimalFormat.format((cell.getNumericCellValue()));
            }
        }
        else if(cell.getCellType() == CellType.BLANK) {
            cellValue = "";
        }
        else if(cell.getCellType() ==CellType.BOOLEAN) {
            cellValue = String.valueOf(cell.getBooleanCellValue());
        }
        else if(cell.getCellType() == CellType.ERROR) {
            cellValue = "";
        }
        else if(cell.getCellType() == CellType.FORMULA) {
            cellValue = cell.getCellFormula().toString();
        }
        return cellValue;
    }
}