001 | package com.asiainfo.demo02; |
003 | import java.io.FileOutputStream; |
004 | import java.io.IOException; |
005 | import java.io.OutputStream; |
007 | import org.apache.poi.hssf.usermodel.HSSFCell; |
008 | import org.apache.poi.hssf.usermodel.HSSFDataFormat; |
009 | import org.apache.poi.hssf.usermodel.HSSFFont; |
010 | import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
011 | import org.apache.poi.ss.usermodel.Cell; |
012 | import org.apache.poi.ss.usermodel.CellStyle; |
013 | import org.apache.poi.ss.usermodel.Font; |
014 | import org.apache.poi.ss.usermodel.Row; |
015 | import org.apache.poi.ss.usermodel.Sheet; |
016 | import org.apache.poi.ss.usermodel.Workbook; |
017 | import org.apache.poi.ss.util.CellRangeAddress; |
019 | public class PoiExcelTest { |
021 | public static void main(String[] args) throws Exception { |
022 | OutputStream os = new FileOutputStream( "F:\\test.xls" ); |
023 | Workbook wb = new HSSFWorkbook(); |
024 | PoiExcelTest test = new PoiExcelTest(); |
025 | test.createFile(os, wb); |
028 | private void createFile(OutputStream os,Workbook wb) throws IOException{ |
031 | double trans_amt = 0.00 ; |
032 | double ref_amt = 0.00 ; |
033 | String[] refundLogs = new String[ 2 ]; |
034 | String str1 = "20110812|34234234242432|345.00|323.00" ; |
035 | String str2 = "20110504|45656464535345|231.34|231.34" ; |
036 | refundLogs[ 0 ] = str1; |
037 | refundLogs[ 1 ] = str2; |
038 | Sheet sheet = wb.createSheet( "T建行退款文件" ); |
039 | Row row = sheet.createRow( 0 ); |
047 | sheet.addMergedRegion( new CellRangeAddress( 0 , 3 , 0 , 3 )); |
049 | Font font = wb.createFont(); |
050 | font.setFontName( "黑体" ); |
051 | font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); |
053 | CellStyle cs1 = wb.createCellStyle(); |
054 | cs1.setAlignment(CellStyle.ALIGN_CENTER); |
055 | cs1.setDataFormat(wb.createDataFormat().getFormat( "yyyyMMdd" )); |
058 | CellStyle cs2 = wb.createCellStyle(); |
059 | cs2.setAlignment(CellStyle.ALIGN_CENTER); |
060 | cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat( "0.00" )); |
063 | CellStyle cs3 = wb.createCellStyle(); |
064 | cs3.setAlignment(CellStyle.ALIGN_CENTER); |
067 | row = sheet.getRow( 0 ); |
068 | Cell cell = row.getCell( 0 ); |
069 | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
070 | cell.setCellValue( "建行运行中心:\n\t" + "现有" +refundLogs.length+ "表退款交易,请配合汇付天下公司进行审核" ); |
073 | row = sheet.createRow( 5 ); |
075 | cell = row.createCell( 0 ); |
076 | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
077 | cell.setCellValue( "商户编号:" ); |
078 | cell = row.createCell( 1 ); |
079 | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
080 | cell.setCellValue( "45433242" ); |
082 | row = sheet.createRow( 6 ); |
083 | cell = row.createCell( 0 ); |
084 | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
085 | cell.setCellValue( "交易明细:" ); |
087 | row = sheet.createRow( 7 ); |
088 | row.createCell( 0 ).setCellValue( "退款日期" ); |
089 | row.createCell( 1 ).setCellValue( "消费卡号" ); |
090 | row.createCell( 2 ).setCellValue( "消费金额" ); |
091 | row.createCell( 3 ).setCellValue( "退款金额" ); |
093 | row.getCell(i).setCellStyle(cs3); |
096 | for (i= 8 ;i<= 7 +refundLogs.length;i++) |
100 | sheet.getRow(i).createCell(j); |
102 | for (i= 0 ;i<refundLogs.length;i++){ |
103 | row = sheet.getRow( 8 +i); |
104 | String[] refundLog = refundLogs[i].split( "\\|" ); |
105 | cell = row.getCell( 0 ); |
106 | cell.setCellStyle(cs1); |
107 | cell.setCellValue(refundLog[ 0 ]); |
109 | cell = row.getCell( 1 ); |
110 | cell.setCellType(HSSFCell.CELL_TYPE_STRING); |
111 | cell.setCellStyle(cs3); |
112 | cell.setCellValue(refundLog[ 1 ]); |
114 | cell = row.getCell( 2 ); |
115 | cell.setCellStyle(cs2); |
116 | cell.setCellValue(refundLog[ 2 ]); |
117 | trans_amt += Double.parseDouble(refundLog[ 2 ]); |
119 | cell = row.getCell( 3 ); |
120 | cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); |
121 | cell.setCellStyle(cs2); |
122 | cell.setCellValue(refundLog[ 3 ]); |
123 | ref_amt += Double.parseDouble(refundLog[ 3 ]); |
126 | row = sheet.createRow( 9 +i); |
129 | row.getCell( 0 ).setCellValue( "总计:" ); |
130 | row.getCell( 2 ).setCellValue(trans_amt); |
131 | row.getCell( 3 ).setCellValue(ref_amt); |
133 | sheet.autoSizeColumn( 0 ); |
134 | sheet.autoSizeColumn( 1 ); |
135 | sheet.autoSizeColumn( 2 ); |
136 | sheet.autoSizeColumn( 3 ); |