java使用POI导入导出

最新推荐文章于 2022-11-30 22:40:03 发布

AnNong。

最新推荐文章于 2022-11-30 22:40:03 发布

阅读量540

点赞数 2

分类专栏： java基础文章标签： POI java excel导入导出

本文链接：https://blog.csdn.net/AdminC/article/details/90597473

版权

java基础专栏收录该内容

3 篇文章 2 订阅

订阅专栏

1.导入相关依赖

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

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

2.创建测试.exce

3.编码

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

    //先从桌面读取 然后输出
    List<Area> areaList = importXLS();
    exportExcel2(areaList);


}

//从excel导入数据
public static List<Area> importXLS() throws IOException {
    //创建文件输入流
    FileInputStream is = new FileInputStream("C:\\Users\\admin\\Desktop\\测试.xls");
    //创建excel文档对象
    HSSFWorkbook workbook = new HSSFWorkbook(is);
    //获取excel工作表对象
    HSSFSheet sheetAt = workbook.getSheetAt(0);
    ArrayList<Area> list = new ArrayList<>();

    for (Row row : sheetAt) {
        //跳过标题(首行)
        if (row.getRowNum() == 0) {
            continue;
        }
        //读取第一行的数据
        String num = row.getCell(0).getStringCellValue();
        String province = row.getCell(1).getStringCellValue();
        String city = row.getCell(2).getStringCellValue();
        String district = row.getCell(3).getStringCellValue();

        Area area = new Area();
        area.setNum(num);
        area.setProvince(province);
        area.setCity(city);
        area.setDistrict(district);
        list.add(area);
    }

    System.out.println(list);
    return list;
}

//导出数据到excel
public static void exportExcel(List<Area> areaList) throws IOException {
    //1.在内存中创建同一个excel文件
    HSSFWorkbook workbook = new HSSFWorkbook();
    //2.创建工作簿
    HSSFSheet sheet = workbook.createSheet();
   /* sheet.setDisplayGridlines(false);//去除网格线*/
    //3.创建标题行 第一行需要合并 第二行正常标题
    HSSFRow firstRow = sheet.createRow(0);
    firstRow.createCell(0).setCellValue("区域信息统计");
    CellRangeAddress cellAddresses = new CellRangeAddress(0, 0, 0, 3);
    sheet.addMergedRegion(cellAddresses);
    HSSFCellStyle style = workbook.createCellStyle();
    style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直
    style.setAlignment(HorizontalAlignment.CENTER);// 水平
    Font font = workbook.createFont();
    font.setBold(true);
    font.setFontName("宋体");
    font.setFontHeight((short) 350);
    style.setFont(font);

    HSSFRow titleRow = sheet.createRow(1);
    titleRow.createCell(0).setCellValue("序号");
    titleRow.createCell(1).setCellValue("省");
    titleRow.createCell(2).setCellValue("市");
    titleRow.createCell(3).setCellValue("区");
    //4.遍历数据,创建数据行
    for (Area area : areaList) {
        //获取最后一行的行号
        int lastRowNum = sheet.getLastRowNum();
        //总数
        HSSFRow dataRow = sheet.createRow(lastRowNum + 1);
        dataRow.createCell(0).setCellValue(area.getNum());
        dataRow.createCell(1).setCellValue(area.getProvince());
        dataRow.createCell(2).setCellValue(area.getCity());
        dataRow.createCell(3).setCellValue(area.getDistrict());
    }
    //5.创建文件名
    String fileName = "C:\\Users\\admin\\Desktop\\export.xls";
    File file = new File(fileName);
    //6.获取文件输出流
    FileOutputStream os = new FileOutputStream(file);
    workbook.write(os);
    //7.关闭流
    workbook.close();
    os.close();


}