• Apache PIO 操作Excel


    POI官方网址:http://poi.apache.org/
    POI 的功能实在很强大,而且是apache的子项目,它下面又包含一些Component,比如处理Excel XLS,PowerPoint PPT,Word DOC,Outlook MSG,Excel XLSX等,下面就简单讲下poi处理excel的一些内容。

    下面的jar包来源于当前最新的poi 3.6版本。

    1.poi来生成excel

    package com.test.poi;

    import java.io.FileOutputStream;
    import java.util.Date;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFDataFormat;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFHyperlink;
    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;
    import org.apache.poi.ss.util.CellRangeAddress;

    public class WriteExcel {

    public static void main(String[] args) throws Exception {
        // 创建Excel的工作书册 Workbook,对应到一个excel文档
        HSSFWorkbook wb = new HSSFWorkbook();

        // 创建Excel的工作sheet,对应到一个excel文档的tab
        HSSFSheet sheet = wb.createSheet("sheet1");

        // 设置excel每列宽度
        sheet.setColumnWidth(0, 4000);
        sheet.setColumnWidth(1, 3500);

        // 创建字体样式
        HSSFFont font = wb.createFont();
        font.setFontName("Verdana");
        font.setBoldweight((short) 100);
        font.setFontHeight((short) 300);
        font.setColor(HSSFColor.BLUE.index);

        // 创建单元格样式
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 设置边框
        style.setBottomBorderColor(HSSFColor.RED.index);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);

        style.setFont(font);// 设置字体

        // 创建Excel的sheet的一行
        HSSFRow row = sheet.createRow(0);
        row.setHeight((short) 500);// 设定行的高度
        // 创建一个Excel的单元格
        HSSFCell cell = row.createCell(0);

        // 合并单元格(startRow,endRow,startColumn,endColumn)
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

        // 给Excel的单元格设置样式和赋值
        cell.setCellStyle(style);
        cell.setCellValue("hello world");

        // 设置单元格内容格式
        HSSFCellStyle style1 = wb.createCellStyle();
        style1.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));

        style1.setWrapText(true);// 自动换行

        row = sheet.createRow(1);

        // 设置单元格的样式格式

        cell = row.createCell(0);
        cell.setCellStyle(style1);
        cell.setCellValue(new Date());

        // 创建超链接
        HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
        link.setAddress("http://www.baidu.com");
        cell = row.createCell(1);
        cell.setCellValue("百度");
        cell.setHyperlink(link);// 设定单元格的链接

        FileOutputStream os = new FileOutputStream("e:\workbook.xls");
        wb.write(os);
        os.close();
    }

    }

    2.poi读取excel
    package com.test.poi;

    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Iterator;

    import org.apache.poi.hssf.extractor.ExcelExtractor;
    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.poifs.filesystem.POIFSFileSystem;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;

    public class ReadExcel {

    public static void main(String[] args) throws Exception {
        HSSFWorkbook wb = null;
        POIFSFileSystem fs = null;
        try {
          fs = new POIFSFileSystem(new FileInputStream("e:\workbook.xls"));
          wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
          e.printStackTrace();
        }

        HSSFSheet sheet = wb.getSheetAt(0);
        HSSFRow row = sheet.getRow(0);
        HSSFCell cell = row.getCell(0);
        String msg = cell.getStringCellValue();
        System.out.println(msg);
    }
    public static void method2() throws Exception {

        InputStream is = new FileInputStream("e:\workbook.xls");
        HSSFWorkbook wb = new HSSFWorkbook(new POIFSFileSystem(is));

        ExcelExtractor extractor = new ExcelExtractor(wb);
        extractor.setIncludeSheetNames(false);
        extractor.setFormulasNotResults(false);
        extractor.setIncludeCellComments(true);

        String text = extractor.getText();
        System.out.println(text);
    }

    public static void method3() throws Exception {
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("e:\workbook.xls"));
        HSSFSheet sheet = wb.getSheetAt(0);

        for (Iterator<Row> iter = (Iterator<Row>) sheet.rowIterator(); iter.hasNext();) {
          Row row = iter.next();
          for (Iterator<Cell> iter2 = (Iterator<Cell>) row.cellIterator(); iter2.hasNext();) {
            Cell cell = iter2.next();
            String content = cell.getStringCellValue();// 除非是sring类型,否则这样迭代读取会有错误
            System.out.println(content);
          }
        }
    }
    }

    注:HSSFWorkbook,XSSFWorkbook的区别:前者是解析出来excel 2007 以前版本的,后缀名为xls的,后者是解析excel 2007 版的,后缀名为xlsx。

    在实际应用中,要对excel文件进行判断,该用哪个workbook来对其进行解析处理,而且,通常把这些方法都做了相应封装,使其更面向对象,上例只是main方法的简单示例而已,仅供参考!

  • 相关阅读:
    老树新芽,在ES6下使用Express
    Swift3翻天覆地的改变
    NodeJs回调操作Promise化
    Node的关系型数据库ORM库:bookshelf
    基于Node的PetShop,RESTful API以及认证
    基于Node的PetShop,oauth2认证RESTful API
    Mongoose轻松搞定MongoDB,不要回调!
    Thymeleaf常用语法:表达式语法之运算符
    Thymeleaf对象的使用:日期对象
    Thymeleaf对象的使用:字符串对象
  • 原文地址:https://www.cnblogs.com/yoyo24456/p/3665449.html
Copyright © 2020-2023  润新知