通过java实现将数据写入到excel文件中

最新推荐文章于 2024-09-05 17:29:21 发布

zhz1605

最新推荐文章于 2024-09-05 17:29:21 发布

阅读量4.8k

点赞数 1

分类专栏： java 文章标签： java excel

本文链接：https://blog.csdn.net/zhz1605/article/details/104687784

版权

java 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

需求：实现将某写数据按照指定的格式写入到excel文件中

一、测试方法

public static void main(String[] args) {
        List<MbTranHist> mbTranHists = new ArrayList<>();
        for (int i = 0; i <1000 ; i++) {
            MbTranHist mbTranHist = new MbTranHist();
            mbTranHist.setAcctSeqNo(String.valueOf(i)+1);
            mbTranHist.setTranDate("20200305");
            if(i%2==0){
                mbTranHist.setTranDesc("收入");
            }else {
                mbTranHist.setTranDesc("支出");

            }
            mbTranHist.setBaseAcctNo("9191919191919199191");
            mbTranHist.setThrBaseAcctName("账户名称账户名称");
            mbTranHist.setNarrative("测试测试");
            mbTranHist.setTranAmt(new BigDecimal(Math.random()*9999999+1).setScale(2,BigDecimal.ROUND_HALF_UP));
            mbTranHist.setActualBalAmt(new BigDecimal(Math.random()*9999999+1).setScale(2,BigDecimal.ROUND_HALF_UP));
            mbTranHists.add(mbTranHist);
        }

        genertAccountDeatilSheet(mbTranHists);
    }

二、写数据到excel方法

public static void genertAccountDeatilSheet(List<MbTranHist> mbTranHists){

        //创建一个工作簿
        HSSFWorkbook wb = new HSSFWorkbook();

        //创建一个sheet
        HSSFSheet sheet = wb.createSheet("账户流水");
        sheet.setColumnWidth(0, 2000);
        sheet.setColumnWidth(1, 2500);
        sheet.setColumnWidth(2, 2500);
        sheet.setColumnWidth(3, 6000);
        sheet.setColumnWidth(4, 6000);
        sheet.setColumnWidth(5, 4000);
        sheet.setColumnWidth(6, 4000);
        sheet.setColumnWidth(7, 4000);

        //创建单元格标题
        CellRangeAddress regionTitle = new CellRangeAddress(0, 0, 0, 7);
        //户名
        CellRangeAddress regionAcctName = new CellRangeAddress(1,  1, 0, 3);
        //币种
        CellRangeAddress regionCcy = new CellRangeAddress( 1,  1, 4, 7);
        //账号
        CellRangeAddress regionBaseAcctNo = new CellRangeAddress( 2,  2, 0, 3);
        //账户序号
        CellRangeAddress regionAcctSeqNo = new CellRangeAddress( 2, 2, 4, 7);
        //起始日期
        CellRangeAddress regionStartDate = new CellRangeAddress( 3,  3, 0, 3);
        //结束日期
        CellRangeAddress regionEndDate = new CellRangeAddress( 3,  3, 4, 7);

        sheet.addMergedRegion(regionTitle);
        sheet.addMergedRegion(regionAcctName);
        sheet.addMergedRegion(regionCcy);
        sheet.addMergedRegion(regionBaseAcctNo);
        sheet.addMergedRegion(regionAcctSeqNo);
        sheet.addMergedRegion(regionStartDate);
        sheet.addMergedRegion(regionEndDate);


        //样式
        // 设置表头字体样式
        HSSFFont columnHeadFont = wb.createFont();
        columnHeadFont.setFontName("宋体");
        columnHeadFont.setFontHeightInPoints((short) 10);
        columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);


        HSSFCellStyle columnHeadStyle = wb.createCellStyle();
        columnHeadStyle.setFont(columnHeadFont);
        // 左右居中
//        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 上下居中
        columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //填充数据0-0
        HSSFRow row0 = sheet.createRow(0);
        HSSFCell cell0 = row0.createCell(0);         //第一个单元格
        cell0.setCellValue("XX明细清单");
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
        cell0.setCellStyle(columnHeadStyle);

        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell1 = row1.createCell(0);         //第一个单元格
        cell1.setCellValue("户名:XX有限公司");
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cell1.setCellStyle(columnHeadStyle);

        HSSFCell cell2 = row1.createCell(4);         //第一个单元格
        cell2.setCellValue("币种:人民币");
        cell2.setCellStyle(columnHeadStyle);

        HSSFRow row2 = sheet.createRow(2);
        HSSFCell cell21 = row2.createCell(0);         //第一个单元格
        cell21.setCellValue("账号：2222222222221");
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cell21.setCellStyle(columnHeadStyle);

        HSSFCell cell22 = row2.createCell(4);         //第一个单元格
        cell22.setCellValue("序号:0");
        cell22.setCellStyle(columnHeadStyle);

        HSSFRow row3 = sheet.createRow(3);
        HSSFCell cell31 = row3.createCell(0);         //第一个单元格
        cell31.setCellValue("起始日期：20171227");
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        cell31.setCellStyle(columnHeadStyle);

        HSSFCell cell32 = row3.createCell(4);         //第一个单元格
        cell32.setCellValue("结束日期: 20190621");
        cell32.setCellStyle(columnHeadStyle);

        HSSFRow row4 = sheet.createRow(4);
        HSSFCell cell4 = row4.createCell(0);         	//第一个单元格
        cell4.setCellValue("序号");                  			//设定值
        cell4 = row4.createCell(1);                   	//第二个单元格
        cell4.setCellValue("日期");
        cell4 = row4.createCell(2);                   	//第三个单元格
        cell4.setCellValue("收入/支出");
        cell4 = row4.createCell(3);                   	//第四个单元格
        cell4.setCellValue("对方账户");
        cell4 = row4.createCell(4);                  	//第五个单元格
        cell4.setCellValue("对方户名");
        cell4 = row4.createCell(5);                   	//第五个单元格
        cell4.setCellValue("摘要");
        cell4 = row4.createCell(6);
        cell4.setCellValue("交易金额");
        cell4 = row4.createCell(7);
        cell4.setCellValue("账户余额");
        cell4 = row4.createCell(8);

        HSSFRow rowData = null;
        for (int i = 0; i <mbTranHists.size() ; i++) {
            rowData = sheet.createRow(5+i);
            rowData.setRowStyle(columnHeadStyle);
            rowData.createCell(0).setCellValue(mbTranHists.get(i).getAcctSeqNo());
            rowData.createCell(1).setCellValue(mbTranHists.get(i).getTranDate());
            rowData.createCell(2).setCellValue(mbTranHists.get(i).getTranDesc());
            rowData.createCell(3).setCellValue(mbTranHists.get(i).getBaseAcctNo());
            rowData.createCell(4).setCellValue(mbTranHists.get(i).getThrBaseAcctName());
            rowData.createCell(5).setCellValue(mbTranHists.get(i).getNarrative());
            rowData.createCell(6).setCellValue(mbTranHists.get(i).getTranAmt().toString());
            rowData.createCell(7).setCellValue(mbTranHists.get(i).getActualBalAmt().toString());
        }

        try {
            FileOutputStream fout = new FileOutputStream("./zhlx.xls");
            wb.write(fout);
            fout.close();
            System.out.println("文件已生成");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }