POI读取EXCEL

最新推荐文章于 2024-09-01 01:12:54 发布

fly2749

最新推荐文章于 2024-09-01 01:12:54 发布

阅读量1.6k

点赞数

分类专栏： J2SE 文章标签： jfreechart excel exception 报表作业 import

J2SE 专栏收录该内容

31 篇文章 0 订阅

订阅专栏

publicclass ReadModelDemo {

publicstaticvoid createExcelFromTemplate()

{

// 读取模板Excel

HSSFWorkbook workBook = null;

try {

workBook = new HSSFWorkbook(new FileInputStream("C://model.xls"));

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

//得到这个workbook模版后,就可以插入数据了

//......

workBook.createSheet().createRow(6).createCell((short)2).setCellValue(15);

//......

try {// 新建一输出文件流

FileOutputStream ōut = new FileOutputStream("C://test.xls");

// 把相应的Excel 工作簿存盘

workBook.write(out);

out.flush();

out.close();

} catch (Exception e) {

e.printStackTrace();

}

二报表结构不确定且复杂（带分析图）这类报表建议用poi/javascrīpt写数据部分，分析图可以用jfreechart来画，画完后插入到excel中（poi 3.0以后支持图片插入）以下示例为POI写数据和格式的部分，jfreechart 生成图片及插入到Excel的代码略：

import java.io.FileOutputStream;

import org.apache.poi.hssf.record.ChartRecord;

import org.apache.poi.hssf.record.FormulaRecord;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFChart;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;

import org.apache.poi.hssf.usermodel.HSSFFont;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;

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.Region;

publicclass CreateExcelDemo {

/**

*@paramargs

publicstaticvoid main(String[] args) {

HSSFWorkbook workbook = new HSSFWorkbook();

HSSFSheet sheet = workbook.createSheet();

HSSFRow row = sheet.createRow(0);

HSSFCell cell = row.createCell((short)0);

//表名样式

HSSFCellStyle titlecellstyle = workbook.createCellStyle();

titlecellstyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);//左对齐

HSSFFont titleFont = workbook.createFont();

titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体

titleFont.setUnderline(HSSFFont.U_SINGLE); //单下画线

titleFont.setFontName("Arial"); //字体

titleFont.setFontHeightInPoints((short)14); //大小

titlecellstyle.setFont(titleFont);

//表头样式

HSSFCellStyle formTitleStyle = workbook.createCellStyle();

formTitleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

formTitleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

formTitleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

formTitleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

formTitleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

HSSFFont formTitleFont = workbook.createFont();

formTitleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

formTitleFont.setFontName("Arial");

titleFont.setFontHeightInPoints((short)10);

formTitleStyle.setFont(formTitleFont);

//表内容样式

HSSFCellStyle contentStyle = workbook.createCellStyle();

contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFFont contentFont = workbook.createFont();

contentFont.setFontName("Arial");

contentFont.setFontHeightInPoints((short)12);

contentStyle.setFont(contentFont);

//百分数显示样式

HSSFCellStyle percentStyle = workbook.createCellStyle();

percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

percentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);

percentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);

percentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

percentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

percentStyle.setDataFormat((short)9);

percentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

HSSFFont percentFont = workbook.createFont();

percentFont.setFontName("Arial");

percentStyle.setFont(percentFont);

//写入内容

cell.setCellStyle(titlecellstyle);

cell.setCellValue("PII-ENG采购申请流程控制分析报告");

row = sheet.createRow((short)1);

row = sheet.createRow((short)2);

cell = row.createCell((short)0);

cell.setCellValue("报告日期：2007-10-25 （月报）");

row = sheet.createRow((short)3);

HSSFRichTextString str1 = new HSSFRichTextString("作业标准：(1) HKD0-10万(14日)；" +

"(2) HKD10万-50万(14日)；(3) >=HKD50万(37日)； (4) 豁免申请(37日)");

HSSFRichTextString str2 = new HSSFRichTextString("指标：作业标准内完成数量/总数量>=80%");

row.createCell((short)0).setCellValue(str1);

sheet.createRow((short)4).createCell((short)0).setCellValue(str2);

row=sheet.createRow((short)5);

cell = row.createCell((short)0);