java poi 操作EXCEl

最新推荐文章于 2023-05-08 16:06:11 发布

lostdboy

最新推荐文章于 2023-05-08 16:06:11 发布

阅读量90

点赞数

分类专栏： opi操作EXCEl

本文链接：https://blog.csdn.net/qq_36644198/article/details/115943126

版权

opi操作EXCEl 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <!-- apache POI for xlsx -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.17</version>
        </dependency>

import com.excel.pojo.User;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class ExcelTest {


    //导出Excel（xls）
    @Test
    public void importExcel() throws IOException {
        //创建新工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //新建工作表
        HSSFSheet sheet = workbook.createSheet("sheet1");
        //创建行,行号作为参数传递给createRow()方法,第一行从0开始计算
        HSSFRow row = sheet.createRow(0);
        //创建单元格,row已经确定了行号,列号作为参数传递给createCell(),第一列从0开始计算
        HSSFCell cell = row.createCell(2);
        //设置单元格的值,即C1的值(第一行,第三列)
        cell.setCellValue("hello sheet");
        //输出到磁盘中
        FileOutputStream fos = new FileOutputStream(new File("D:\\exportTest.xls"));
        workbook.write(fos);
        workbook.close();
        fos.close();
    }


    //读取Excel（xls）
    @Test
    public void importExcel1() throws IOException {
        //创建输入流
        FileInputStream fis = new FileInputStream(new File("D:\\user.xls"));
        //通过构造函数传参
        HSSFWorkbook workbook = new HSSFWorkbook(fis);
        //获取工作表
        HSSFSheet sheet = workbook.getSheetAt(0);
        //获取行,行号作为参数传递给getRow方法,第一行从0开始计算
        HSSFRow row = sheet.getRow(0);
        //获取单元格,row已经确定了行号,列号作为参数传递给getCell,第一列从0开始计算
        HSSFCell cell = row.getCell(2);
        //获取单元格的值,即C1的值(第一行,第三列)
        String cellValue = cell.getStringCellValue();
        System.out.println("第一行第三列的值是"+cellValue);
        workbook.close();
        fis.close();
    }


    //导出Excel（xlsx）
    @Test
    public void exportExcel() throws IOException {
        //创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();
        //新建工作表
        XSSFSheet sheet = workbook.createSheet("sheet1");
        //创建行,0表示第一行
        XSSFRow row = sheet.createRow(0);
        //创建单元格行号由row确定,列号作为参数传递给createCell;第一列从0开始计算
        XSSFCell cell = row.createCell(2);
        //给单元格赋值
        cell.setCellValue("hello sheet in Xlsx");
        //创建输出流
        FileOutputStream fos = new FileOutputStream(new File("D:\\user.xlsx"));
        workbook.write(fos);
        workbook.close();
        fos.close();
    }


    //读取Excel（xlsx）
    @Test
    public void importXlsx() throws IOException {
        //创建输入流
        FileInputStream fis = new FileInputStream(new File("D:\\user.xlsx"));
        //由输入流得到工作簿
        XSSFWorkbook workbook = new XSSFWorkbook(fis);
        //得到工作表
        XSSFSheet sheet = workbook.getSheet("sheet1");
        //得到行,0表示第一行
        XSSFRow row = sheet.getRow(0);
        //创建单元格行号由row确定,列号作为参数传递给createCell;第一列从0开始计算
        XSSFCell cell = row.getCell(2);
        //给单元格赋值
        String cellValue = cell.getStringCellValue();
        System.out.println("第一行第三列的值是"+cellValue);
        workbook.close();
        fis.close();
    }


    //通用方法处理xls和xlsx

    @Test
    public void importXlsOrXlsx() throws IOException{
        String filePath = "D://user.xlsx";
        if(filePath.matches("^.+\\.(?i)((xls)|(xlsx))$"))
        {
            FileInputStream fis = new FileInputStream(filePath);
            boolean is03Excell = filePath.matches("^.+\\.(?i)(xls)$")?true:false;
            Workbook workbook = is03Excell ? new HSSFWorkbook(fis):new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(0);
            Cell cell = row.getCell(2);
            System.out.println("第一行第一列的数据是:"+cell.getStringCellValue());
            workbook.close();
            fis.close();
        }
    }



    public List<User> userImport(String filePath) {
        List<User> userList = new ArrayList<User>();

        try {
            FileInputStream fio = new FileInputStream(filePath);
            HSSFWorkbook workbook = new HSSFWorkbook(fio);
            HSSFSheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                if (row.getRowNum() == 0) {// 首行表头不读取
                    continue;
                }
                User user = new User();
                user.setId(Integer.parseInt( row.getCell(0).getStringCellValue()));
                user.setUsername(row.getCell(1).getStringCellValue());
                user.setAge(row.getCell(2).getStringCellValue());
                userList.add(user);
            }

            workbook.close();
            fio.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return userList;
    }

    @Test
    public void userImport(){
        List<User> userList = new ExcelTest().userImport("D://user.xls");
        for (User user : userList) {
            System.out.println("用户信息为："+user.getId()+";"+user.getUsername()+";"+user.getAge()+";");
        }
    }

    public void userExport(String filePath, List<User> userList){
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        HSSFRow titleRow = sheet.createRow(0);
        titleRow.createCell(0).setCellValue("id");
        titleRow.createCell(1).setCellValue("username");
        titleRow.createCell(2).setCellValue("age");

        for (User user : userList) {
            int lastRowNum = sheet.getLastRowNum();//获取最后一行行号

            HSSFRow row = sheet.createRow(lastRowNum+1);
            row.createCell(0).setCellValue(user.getId());
            row.createCell(1).setCellValue(user.getUsername());
            row.createCell(2).setCellValue(user.getAge());
        }

        try {
            FileOutputStream fos = new FileOutputStream(filePath);
            workbook.write(fos);
            workbook.close();
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void userExport(){
        List<User> userList = new ArrayList<User>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setId(i);
            user.setUsername("ll"+1);
            user.setAge("2"+i);
            userList.add(user);
        }

        new ExcelTest().userExport("D://userList.xls", userList);
    }


}

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.6</version>
        </dependency>

package com.excel.controller;

import com.alibaba.fastjson.JSON;
import com.excel.pojo.User;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

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


@Controller
@ResponseBody
public class excelController {

    @RequestMapping("/hello")
    public String test(){
        return "hello";
    }

    public HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, HSSFWorkbook workbook) {
        // 创建一个HSSFWorkbook，对应一个Excel文件
        if (workbook == null) {
            workbook = new HSSFWorkbook();
        }
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 在sheet中添加表头第0行
        HSSFRow row = sheet.createRow(0);
        // 创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 声明列对象
        HSSFCell cell = null;
        // 创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(cellStyle);
        }
        // 创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                // 将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return workbook;

    }

    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = new String(fileName.getBytes(), "ISO8859-1");
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @GetMapping("/excel")
    public void exportExcel(HttpServletResponse response, User user) {
        String s1 = user.toString();
        System.out.println(s1);
        String s = JSON.toJSONString(user);
        System.out.println(s);

        List<User> monthReportModels = new ArrayList<>();

        monthReportModels.add(user);
        monthReportModels.add(new User(1,"asd","111"));

        Map map = JSON.parseObject(JSON.toJSONString(user), Map.class);
        System.out.println(map);
        // Excel标题
        String[] title = {"id", "username", "age"};
        // Excel文件名
        String fileName =  "user.xls";
        // sheet名
        String sheetName = "user";
        // 将数据放到数组中
        String[][] content = new String[monthReportModels.size()][title.length];
        for (int i = 0; i < monthReportModels.size(); i++) {
            User monthReportModel = monthReportModels.get(i);
            content[i][0] = String.valueOf(monthReportModel.getId());
            content[i][1] = monthReportModel.getUsername();
            content[i][2] = monthReportModel.getAge();
        }
        // 导出Excel
        try {
            HSSFWorkbook hssfWorkbook = getHSSFWorkbook(sheetName, title, content, null);
            setResponseHeader(response, fileName);
            OutputStream outputStream = response.getOutputStream();
            hssfWorkbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

操作excel，Workbook->Sheet->Row->Cell，得到对应对象通常都是get方法，建立对应对象通常是create方法
设置sheet名称

HSSFSheet sheet = workbook.createSheet("测试名称");

//OR

workbook.setSheetName(0, "testname");

//设置单元格内容
cell.setCellValue("单元格内容");

//获取sheet数目
workbook.getNumberOfSheets()

//根据index取得sheet对象
HSSFSheet sheet = wb.getSheetAt(0);

//取得有效的行数
int rowCount = sheet.getLastRowNum();

//取得一行的有效单元格个数
row.getLastCellNum();

//设置单元格格式
cell.setCellType(HSSFCell.CELL_TYPE_STRING);

//读取数值类型的单元格内容
cell.getNumericCellValue();

//设置列宽、行高
sheet.setColumnWidth((short)column,(short)width); 
row.setHeight((short)height);

//添加区域，合并单元格
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 3);
sheet.addMergedRegion(cellRangeAddress);
HSSFRow mergedRow = sheet.createRow(0);
HSSFCell mergedCell = mergedRow.createCell(0);
mergedCell.setCellValue("表格标题行");

//根据单元格不同属性返回字符串数值
public String getCellStringValue(HSSFCell cell) { 
　　String cellValue = ""; 
　　switch (cell.getCellType()) { 
　　　　case HSSFCell.CELL_TYPE_STRING://字符串类型 
　　　　　　　　cellValue = cell.getStringCellValue(); 
　　　　　　　　if(cellValue.trim().equals("")||cellValue.trim().length()<=0) 
　　　　　　　　　　cellValue=" "; 
　　　　　　　　break; 
　　　　case HSSFCell.CELL_TYPE_NUMERIC: //数值类型 
　　　　　　　　cellValue = String.valueOf(cell.getNumericCellValue()); 
　　　　　　　　break; 
　　　　case HSSFCell.CELL_TYPE_FORMULA: //公式 
　　　　　　　　cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); 
　　　　　　　　cellValue = String.valueOf(cell.getNumericCellValue()); 
　　　　　　　　break; 
　　　　case HSSFCell.CELL_TYPE_BLANK: 
　　　　　　　　cellValue=" "; 
　　　　　　　　break; 
　　　　case HSSFCell.CELL_TYPE_BOOLEAN: 
　　　　　　　　break; 
　　　　case HSSFCell.CELL_TYPE_ERROR: 
　　　　　　　　break; 
　　　　default: 
　　　　　　　　break; 
　　} 
　　return cellValue; 
}

设置样式

HSSFCellStyle style = wb.createCellStyle(); 
style.set*

设置字体样式的对象有HSSFCellStyle, HSSFFont, HSSFDataFormat等

lostdboy

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录