Excel(6) : xls 和 xlsx 的创建与读取

参考 :

JAVA POI的使用_林中静月下仙的博客-CSDN博客_poi

https://blog.csdn.net/a314774167/article/details/79300320

POI中设置Excel单元格格式样式（居中，字体，边框等）_spp_1987的专栏-CSDN博客_cellstyle设置边框

注 : 有些人的代码会报错可能是jar包版本引入错误

引入 maven 坐标:

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

Test.java

package com.aliyun.et.industry.device.biz.service.impl.test;

import com.aliyun.et.industry.device.common.util.OssClientUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.Iterator;

/**
 * @Auther: liyue
 * @Date: 2018/9/18 15:20
 * @Description:
 */
public class Tset {


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

    {

        String filePath = "/Users/leyili/Desktop/et-industry-device-xls-import-test.xls";//文件路径
        createXls(filePath);
        System.err.println(readXls(filePath));

    }

    public static void createXls(String path) throws Exception {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }
        HSSFWorkbook workbook = new HSSFWorkbook();//创建Excel文件(Workbook)

        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);

        HSSFSheet sheet = workbook.createSheet("Sheet1");//创建工作表(Sheet)

        // 设置列宽
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 20 * 256);
        sheet.setColumnWidth(2, 20 * 256);


        HSSFRow row = sheet.createRow(0);// 创建行,从0开始
        HSSFCell cell = row.createCell(0);// 创建行的单元格,也是从0开始

        cell.setCellValue("测点信息编码");// 设置单元格内容
        row.createCell(1).setCellValue("测点信息名称");// 设置单元格内容,重载
        row.createCell(2).setCellValue("测点值类型");// 设置单元格内容,重载

        for (int i = 1; i < 20; i++) {
            HSSFRow rows = sheet.createRow(i);
            rows.createCell(0).setCellValue("1_"+i+"_测点信息编码");
            rows.createCell(1).setCellValue("2_"+i+"_测点信息名称");
            rows.createCell(2).setCellValue("3_"+i+"_测点值类型");
        }

        HSSFCell cell1 = row.createCell(3);
        HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
        link.setAddress("https://github.com/550690513");
        cell1.setHyperlink(link);// 设置超链接
        cell1.setCellValue("Fork me on Github");
            
        HSSFCell cell2 = row.createCell(4);
        cell2.setCellValue(new HSSFRichTextString("hello\r\n world!"));// 换行

        HSSFCell cell3 = row.createCell(5);
        HSSFCellStyle cellStyle = cell3.getCellStyle();
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//靠右,注意!!!会使所有单元格靠右
        cell3.setCellValue("sdfsfs");

        FileOutputStream out = new FileOutputStream(path);
        workbook.write(out);//保存Excel文件
        out.close();//关闭文件流
        System.out.println("OK!");
    }

    public static String readXls(String path) {
        String text = "";
        try {
            FileInputStream is = new FileInputStream(path);
            HSSFWorkbook excel = new HSSFWorkbook(is);
            //获取第一个sheet
            HSSFSheet sheet0 = excel.getSheetAt(0);
            for (Iterator rowIterator = sheet0.iterator(); rowIterator.hasNext(); ) {
                HSSFRow row = (HSSFRow) rowIterator.next();
                for (Iterator iterator = row.cellIterator(); iterator.hasNext(); ) {
                    HSSFCell cell = (HSSFCell) iterator.next();
                    //根据单元的的类型 读取相应的结果
                    if (cell.getCellType() == CellType.STRING) text += cell.getStringCellValue() + "\t";
                    else if (cell.getCellType() == CellType.NUMERIC) text += cell.getNumericCellValue() + "\t";
                    else if (cell.getCellType() == CellType.FORMULA) text += cell.getCellFormula() + "\t";
                }
                text += "\n";

            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return text;
    }

    public static void createXlsx2(String filePath) throws Exception {

        File file = new File(filePath);
        if (file.exists()) {
            file.delete();
        }
        XSSFWorkbook workbook = new XSSFWorkbook();//创建Excel文件(Workbook)
        XSSFSheet sheet = workbook.createSheet("Sheet1");//创建工作表(Sheet)


        sheet.setColumnWidth(0, 200);

        XSSFRow row = sheet.createRow(0);// 创建行,从0开始
        XSSFCell cell = row.createCell(0);// 创建行的单元格,也是从0开始

        cell.setCellValue("测点信息编码");// 设置单元格内容
        row.createCell(1).setCellValue("测点信息名称");// 设置单元格内容,重载
        row.createCell(2).setCellValue("测点值类型");// 设置单元格内容,重载


        // sheet = workbook.createSheet("Test");//创建工作表(Sheet)
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);//保存Excel文件
        out.close();//关闭文件流
        System.out.println("OK!");
    }


    public static void createXlsx(String filePath) throws Exception {

        File file = new File(filePath);
        if (file.exists()) {
            file.delete();
        }
        XSSFWorkbook workbook = new XSSFWorkbook();//创建Excel文件(Workbook)
        XSSFSheet sheet = workbook.createSheet();//创建工作表(Sheet)
        // sheet = workbook.createSheet("Test");//创建工作表(Sheet)
        FileOutputStream out = new FileOutputStream(filePath);
        workbook.write(out);//保存Excel文件
        out.close();//关闭文件流
        System.out.println("OK!");
    }

    public static String readXlsx(String path) {
        String text = "";
        try {

            OPCPackage pkg = OPCPackage.open(path);
            XSSFWorkbook excel = new XSSFWorkbook(pkg);
            //获取第一个sheet
            XSSFSheet sheet0 = excel.getSheetAt(0);
            for (Iterator rowIterator = sheet0.iterator(); rowIterator.hasNext(); ) {
                XSSFRow row = (XSSFRow) rowIterator.next();
                for (Iterator iterator = row.cellIterator(); iterator.hasNext(); ) {
                    XSSFCell cell = (XSSFCell) iterator.next();
                    //根据单元的的类型 读取相应的结果
                    if (cell.getCellType() == CellType.STRING) text += cell.getStringCellValue() + "\t";
                    else if (cell.getCellType() == CellType.NUMERIC)
                        text += cell.getNumericCellValue() + "\t";
                    else if (cell.getCellType() == CellType.FORMULA) text += cell.getCellFormula() + "\t";

                }

                text += "\n";
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return text;
    }
}