java 读取/生成excel

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>

一、读取excel

 

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;



public class Excel {
    public static void main(String[] args) throws InvalidFormatException, IOException {
        String filePath = "C:\\Users\\admin\\Desktop\\xxx.xls";
        //如果是xls，使用HSSFWorkbook；如果是xlsx，使用XSSFWorkbook
        File file=new File(filePath);
        if (!file.exists()) {
            System.out.println("文件不存在");
            return;
        }
        //得到指定的Exel
        Workbook wb;
        String fileName = file.getName();
        String suffer = fileName.substring(fileName.lastIndexOf('.') + 1);
        if ("xls".equals(suffer)) {
            // 2003Excel
            wb = new HSSFWorkbook(new FileInputStream(file));
        } else if ("xlsx".equals(suffer)) {
            // 2007Excel
            wb = new XSSFWorkbook(OPCPackage.openOrCreate(file));
        }else {
            System.out.println("文件类型不对");
            return;
        }
        Map<Object,Object> map= new HashMap<>();
        //得到该Exel中的第一张表
        Sheet sh=wb.getSheetAt(0);
        //System.out.println(sh.getSheetName());
        //得到行数
        int rows=sh.getLastRowNum();
        //循环得到每一行
        for(int i=0;i<=rows;i++){
            //得到的当前行
            Row r=sh.getRow(i);
            int cols=r.getLastCellNum();//得到总共有多少列
//            System.out.println(cols+"列");
            System.out.println(getCellValue(r.getCell(0))+ "," + getCellValue(r.getCell(1)));
            //循环得到每行中的指定列，下面是得到第一列和第二列
//            map.put(r.getCell(0),r.getCell(1));
        }
//
//        System.out.println("行数："+map.size());
//        for (Map.Entry<Object,Object> entry : map.entrySet()) {
//            System.out.println("表名： " + entry.getKey() + "，主键：" + entry.getValue());
//        }

    }

    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (null != cell) {
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case NUMERIC: // 数字
                    DecimalFormat df = new DecimalFormat("0");
                    cellValue = df.format(cell.getNumericCellValue());
                    break;
                case STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case FORMULA: // 公式
                    cellValue = cell.getCellFormula() + "";
                    break;
                case BLANK: // 空值
                    cellValue = "";
                    break;
                case _NONE: // 故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
        }
        return cellValue;
    }
}

二、生成excel

1.普通多个sheet

package com.cwh.springbootMybatis.util.test2;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
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.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;


/**
 * Created by qiaojun on 2018-10-8 0008.
 */
public class test {
    public static void main(String[] args) throws Exception {
        OutputStream out = new FileOutputStream("E:\\test2.xls");
        List<List<String>> data = new ArrayList<List<String>>();
        for (int i = 1; i < 5; i++) {
            List rowData = new ArrayList();
            rowData.add(String.valueOf(i));
            rowData.add("东霖柏鸿");
            data.add(rowData);
        }
        String[] headers = {"ID", "用户名"};
        test eeu = new test();
        HSSFWorkbook workbook = new HSSFWorkbook();
        eeu.exportExcel(workbook, 0, "上海", headers, data);
        eeu.exportExcel(workbook, 1, "深圳", headers, data);
        eeu.exportExcel(workbook, 2, "广州", headers, data);
        //原理就是将所有的数据一起写入，然后再关闭输入流。
        workbook.write(out);
        out.close();
        out.flush();
    }
        /**
         * @Title: exportExcel
         * @Description: 导出Excel的方法
         * @author: evan @ 2014-01-09
         * @param workbook
         * @param sheetNum (sheet的位置，0表示第一个表格中的第一个sheet)
         * @param sheetTitle  （sheet的名称）
         * @param headers    （表格的标题）
         * @param result   （表格的数据）
         * @throws Exception
         */
        public void exportExcel(HSSFWorkbook workbook,int sheetNum, String sheetTitle, String[] headers, List<List<String>> result)
        throws Exception {
            // 生成一个表格
            HSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(sheetNum, sheetTitle);
            // 设置表格默认列宽度为20个字节
            sheet.setDefaultColumnWidth(20);
            // 生成一个样式
            HSSFCellStyle style = workbook.createCellStyle();
            // 设置这些样式
            style.setFillForegroundColor(HSSFColor.PALE_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.BLACK.index);
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // 把字体应用到当前的样式
            style.setFont(font);
            // 指定当单元格内容显示不下时自动换行
            style.setWrapText(true);
            // 产生表格标题行
            HSSFRow row = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);

                cell.setCellStyle(style);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text.toString());
            }
            // 遍历集合数据，产生数据行
            if (result != null) {
                int index = 1;
                for (List<String> m : result) {
                    row = sheet.createRow(index);
                    int cellIndex = 0;
                    for (String str : m) {
                        HSSFCell cell = row.createCell(cellIndex);
                        cell.setCellValue(str.toString());
                        cellIndex++;
                    }
                    index++;
                }
            }
        }

}

2.特殊多个sheet

package com.cwh.springbootMybatis.util;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author qiaojun
 * @date 2018-10-8 0008
 */
public class batchSheet {

    public static void main(String[] args) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //  OutputStream out = response.getOutputStream();
        FileOutputStream out = new FileOutputStream("E:/test.xls");
//        List<Member> list = new ArrayList<Member>();
//        SimpleDateFormat df = new SimpleDateFormat("yyyy-mm-dd");
//        Member user1 = new Member(1, "熊大", 24, df.parse("1993-08-28"));
//        Member user2 = new Member(2, "熊二", 23, df.parse("1994-08-19"));
//        Member user3 = new Member(3, "熊熊", 24, df.parse("1983-11-22"));
//
//        list.add(user1);
//        list.add(user2);
//        list.add(user3);
        for (int j = 0; j < 5; j++) {

            //接下来循环list放到Excel表中
            //文件标题
            SimpleDateFormat formatter1 = new SimpleDateFormat("yyyy-MM-dd");
            String nowdate = formatter1.format(new Date());
            String sheettitle = "sheet表名"+j;
            //设置表格标题行
            String oneheaders = "首行标题";
            String dateheaders = nowdate;
            String[] headers = new String[]{"列1", "列2", "列3", "列4"};
            List<Object[]> dataList = new ArrayList<Object[]>();
            Object[] objs = null;
            for (int i = 0; i < 3; i++) {  //循环每一条数据
                objs = new Object[headers.length];
                objs[1] = "张三";   //姓名
                objs[2] = "3";  //序号
                objs[3] = "4";  //序号
                // 数据添加到excel表格
                dataList.add(objs);
            }
            //使用流将数据导出
            // 防止中文乱码
            //                String headStr = "attachment; filename=\"" + new String(title.getBytes("gb2312"), "ISO8859-1") + "\"";
            //                response.setContentType("octets/stream");
            //                response.setContentType("APPLICATION/OCTET-STREAM");
            //                response.setHeader("Content-Disposition", headStr);
            ExportExcelDownFee ex = new ExportExcelDownFee(sheettitle, oneheaders, dateheaders, headers, dataList);//没有标题
            ex.export(workbook);
        }
        workbook.write(out);  //循环生成多个sheet之后在导出Excel
        out.close();  //关闭流
        out.flush();
    }
}

package com.cwh.springbootMybatis.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author qiaojun
 * @date 2018-10-8 0008
 */
public class ExportExcelDownFee {
    //导出表的列名

    private String[] rowName;

    //导出表的小标题

    private String oneheaders;
    //导出表的日期

    private String dateheaders;

    //sheet表表名

    private String sheettitle;

    private List<Object[]> dataList = new ArrayList<Object[]>();

    HttpServletResponse response;

    //构造方法2，传入要导出的数据

    public ExportExcelDownFee(String sheettitle, String oneheaders, String dateheaders, String[] rowName, List<Object[]> dataList) {
        this.dataList = dataList;
        this.oneheaders = oneheaders;
        this.dateheaders = dateheaders;
        this.rowName = rowName;
        this.sheettitle = sheettitle;

    }
       /*
      * 导出数据
      * */

    public void export(HSSFWorkbook workbook) throws Exception {
        try {
            HSSFSheet sheet = workbook.createSheet(sheettitle);
            // 创建工作表
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);

            //单元格样式对象
            //第一行
            HSSFRow rowfirstName = sheet.createRow(0);
            HSSFCell oneCellRowName = rowfirstName.createCell(0);
            //创建列头对应个数的单元格
            oneCellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
            //设置列头单元格的数据类型
            HSSFRichTextString onetext = new HSSFRichTextString(oneheaders);
            oneCellRowName.setCellValue(onetext);
            //设置列头单元格的值
            //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
            oneCellRowName.setCellStyle(columnTopStyle);
            //设置列头单元格样式
            //第二行
            HSSFRow rowDateName = sheet.createRow(1);
            HSSFCell DateCellRowName = rowDateName.createCell(3);
            DateCellRowName.setCellValue(dateheaders);
            DateCellRowName.setCellStyle(columnTopStyle);

            // 定义所需列数
            int columnNum = rowName.length;
            HSSFRow rowRowName = sheet.createRow(2);
            // 在索引2的位置创建行(最顶端的行开始的第二行)
            // 将列头设置到sheet的单元格中
            for (int n = 0; n < columnNum; n++) {
                HSSFCell cellRowName = rowRowName.createCell(n);
                //创建列头对应个数的单元格
                cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING);
                //设置列头单元格的数据类型
                HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
                cellRowName.setCellValue(text);
                //设置列头单元格的值
                cellRowName.setCellStyle(style);
                //设置列头单元格样式
            }

            //将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {
                Object[] obj = dataList.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(i + 3);//创建所需的行数（从第二行开始写数据）
                for (int j = 0; j < obj.length; j++) {
                    HSSFCell cell = null;   //设置单元格的数据类型
                    if (j == 0) {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_NUMERIC);
                        cell.setCellValue(i + 1);
                    } else {
                        cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
                        if (!"".equals(obj[j]) && obj[j] != null) {
                            cell.setCellValue(obj[j].toString());
                            //设置单元格的值
                        }
                    }
                    cell.setCellStyle(style);
                    //设置单元格样式
                }
            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }

                    if (currentRow.getCell(colNum) != null) {

                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                            int length = 0;

                            try {
                                length = currentCell.getStringCellValue().getBytes().length;
                            } catch (Exception e) {
                                e.printStackTrace();
                            }

                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }

                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 256);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /*
    * 列头单元格样式
    */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();

        //设置字体大小
        font.setFontHeightInPoints((short) 11);

        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();

        //在样式用应用设置的字体;
        style.setFont(font);

        //设置自动换行;
        style.setWrapText(false);

        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();

        //设置字体名字
        font.setFontName("宋体");

        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();

        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);

        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);

        //设置左边框颜色;

        style.setLeftBorderColor(HSSFColor.BLACK.index);

        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);

        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);

        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);

        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);

        //在样式用应用设置的字体;
        style.setFont(font);

        //设置自动换行;
        style.setWrapText(false);

        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;

    }

}