获取window的桌面的路径、apache poi 操作excel

 https://blog.csdn.net/vbirdbest/article/details/72870714

错误提示：

https://blog.csdn.net/ysughw/article/details/9288307--类型错误

 

 

  //获取桌面路径
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop + "/testoi.xls";

---

package com.xzhisoft;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.util.Date;

@RunWith(SpringRunner.class)
@SpringBootTest
public class HmmsApplicationTests {

    /**
     * easyExcel 写入测试
     *
     * @throws IOException
     */
 /*   @Test
    public  void writeExcel()throws IOException{
        OutputStream out=new FileOutputStream("D:\\easyExcel.xlsx");
        ExcelWriter writer1=new ExcelWriter(out, ExcelTypeEnum.XLSX,false);
        Sheet sheet1=new Sheet(1,0);
        sheet1.setSheetName("试验品");
        List<List<String>>  data=new ArrayList<>();

        for (int i=0;i<100;i++){
            List<String>  item=new ArrayList<>();
            item.add("第一列"+i);
            item.add("第二列"+i);
            item.add("第三列"+i);
            data.add(item);
        }
        writer1.write0(data,sheet1);
        writer1.finish();
    }*/


    /**
     * 往硬盘上输出一个excel表
     *
     * @throws IOException
     */
    @Test
    public void writeApachePoi() throws IOException {

        //获取桌面路径
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop + "/testoi.xls";

        File file = new File(filePath);
        OutputStream outputStream = new FileOutputStream(file);
        HSSFWorkbook workBook = new HSSFWorkbook();

        //创建一个表
        HSSFSheet sheet = workBook.createSheet("sheetTestapache");
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue("订单号");
        row.createCell(2).setCellValue("下单时间");
        row.createCell(3).setCellValue("个数");
        row.createCell(4).setCellValue("单价");
        row.createCell(5).setCellValue("订单金额");
        row.setHeightInPoints(30);

        HSSFRow row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("1");
        row1.createCell(1).setCellValue("nooo1");

        //日期格式化
        HSSFCellStyle cellStyle = workBook.createCellStyle();
        HSSFCreationHelper creationHelper = workBook.getCreationHelper();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        //设置某一列的宽度，字符个数*256； 时间格式的字符数是18，所以20 够用。
        sheet.setColumnWidth(2, 20 * 256);
        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellStyle(cellStyle);
        cell2.setCellValue(new Date());
        row1.createCell(3).setCellValue(2);

        //保留两位小数
        HSSFCellStyle cellSty3 = workBook.createCellStyle();
        cellSty3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellStyle(cellSty3);
        cell4.setCellValue(29.5);

        //货币格式化
        HSSFCellStyle cellStyle4 = workBook.createCellStyle();
        HSSFFont font = workBook.createFont();
        font.setFontName("华文行楷");
        font.setFontHeightInPoints((short) 15);
        font.setColor(HSSFColor.RED.index);
        cellStyle4.setFont(font);

        HSSFCell cells5 = row1.createCell(5);
        cells5.setCellFormula("D2*E2");

        //获取计算公式的值
        HSSFFormulaEvaluator ed = new HSSFFormulaEvaluator(workBook);
        cells5 = ed.evaluateInCell(cells5);
        System.out.println(cells5.getNumericCellValue());

        //设置打开的时候 默认选中的工作表
        workBook.setActiveSheet(0);
        //写入到输出流
        workBook.write(outputStream);
        outputStream.flush();
        //关闭输出流
        outputStream.close();

    }

    /**
     * 读取excel
     *
     * @throws IOException
     */
    @Test
    public void readExcel() throws IOException {

        /**
         * 根据桌面的路径获取文件
         */
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String deskTop = fsv.getHomeDirectory().getPath();
        String filePath1 = deskTop + "/testoi.xls";
//把文件路径放到输入流中。
        FileInputStream fileInputStream = new FileInputStream(filePath1);
        //把文件放到带有缓冲区的输入流中。
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
        //把输入流中的数据放入 poi的文件解析器
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
        //把解析后的数据放到工作簿中。
        HSSFWorkbook worbook = new HSSFWorkbook(fileSystem);
        //下面就是针对工作簿进行操作了，获取第一个工作表
        HSSFSheet sheet = worbook.getSheetAt(0);
        //获取工作表的最后一行，就是取得工作表的行数。
        int lastRowIndex = sheet.getLastRowNum()+1;

        System.out.println("\n"+"工作表的总行数（含有数据的）"+lastRowIndex);

        for (int i = 0; i < lastRowIndex; i++) {
            HSSFRow row1 = sheet.getRow(i);
            if (row1 == null) {
                break;
            }
//得到指定行的列数
            short lastcellnum = row1.getLastCellNum();

            for (int j = 0; j < lastcellnum; j++) {
                String cellValue1 = row1.getCell(j).getStringCellValue();
                System.out.println(cellValue1);
            }

        }

        bufferedInputStream.close();


    }


}