程序博客网 > 世界贸易进出口数据

Java POI EXCEL

来源：互联网 发布：世界贸易进出口数据 编辑：程序博客网 时间：2024/06/04 18:19

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);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("时段"));        cell = row.createCell((short)1);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("HKD0-10万"));        cell = row.createCell((short)4);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("HKD10万-50万"));        cell = row.createCell((short)7);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString(">HKD50万"));        cell = row.createCell((short)10);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("豁免申请"));        cell = row.createCell((short)13);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("当月合计"));        cell = row.createCell((short)17);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("指标"));        row = sheet.createRow((short)6);        cell = row.createCell((short)1);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("<=14日"));        cell = row.createCell((short)2);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString(">14日"));        cell = row.createCell((short)3);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("达标率"));        cell = row.createCell((short)4);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("<=14日"));        cell = row.createCell((short)5);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString(">14日"));        cell = row.createCell((short)6);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("达标率"));        cell = row.createCell((short)7);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("<=37日"));        cell = row.createCell((short)8);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString(">37日"));        cell = row.createCell((short)9);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("达标率"));        cell = row.createCell((short)10);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("<=37日"));        cell = row.createCell((short)11);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString(">37日"));        cell = row.createCell((short)12);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("达标率"));        cell = row.createCell((short)13);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("达标数"));        cell = row.createCell((short)14);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("不达标数"));        cell = row.createCell((short)15);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("合计"));        cell = row.createCell((short)16);        cell.setCellStyle(formTitleStyle);        cell.setCellValue(new HSSFRichTextString("合计达标率"));        //合并单元格        sheet.addMergedRegion(new Region(5,(short)0,6,(short)0));//(赵始行,起始列,结束行,结束列)        sheet.addMergedRegion(new Region(5,(short)1,5,(short)3));        sheet.addMergedRegion(new Region(5,(short)4,5,(short)6));        sheet.addMergedRegion(new Region(5,(short)7,5,(short)9));        sheet.addMergedRegion(new Region(5,(short)10,5,(short)12));        sheet.addMergedRegion(new Region(5,(short)13,5,(short)16));        sheet.addMergedRegion(new Region(5,(short)17,6,(short)17));        //加入数据，设置公式        row = sheet.createRow((short)7);        cell = row.createCell((short)0);        cell.setCellStyle(contentStyle);        cell.setCellValue(new HSSFRichTextString("1月"));        cell = row.createCell((short)1);        cell.setCellStyle(contentStyle);        cell.setCellValue(256);        try {// 新建一输出文件流            FileOutputStream out = new FileOutputStream("C:\\test.xls");            // 把相应的Excel 工作簿存盘            workbook.write(out);            out.flush();            out.close();        } catch (Exception e) {            e.printStackTrace();        }    }}

1、得到Excel常用对象
view plaincopy to clipboardprint?
01.POIFSFileSystem fs=newPOIFSFileSystem(newFileInputStream("d:/test.xls"));
02.//得到Excel工作簿对象
03.HSSFWorkbook wb = new HSSFWorkbook(fs);
04.//得到Excel工作表对象
05.HSSFSheet sheet = wb.getSheetAt(0);
06.//得到Excel工作表的行
07.HSSFRow row = sheet.getRow(i);
08.//得到Excel工作表指定行的单元格
09.HSSFCell cell = row.getCell((short) j);
10.cellStyle = cell.getCellStyle();//得到单元格样式
POIFSFileSystem fs=newPOIFSFileSystem(newFileInputStream("d:/test.xls"));
//得到Excel工作簿对象
HSSFWorkbook wb = new HSSFWorkbook(fs);
//得到Excel工作表对象
HSSFSheet sheet = wb.getSheetAt(0);
//得到Excel工作表的行
HSSFRow row = sheet.getRow(i);
//得到Excel工作表指定行的单元格
HSSFCell cell = row.getCell((short) j);
cellStyle = cell.getCellStyle();//得到单元格样式
2、建立Excel常用对象view plaincopy to clipboardprint?
01.HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象
02.HSSFSheet sheet = wb.createSheet("newsheet");//创建Excel工作表对象
03.HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行
04.cellStyle = wb.createCellStyle();//创建单元格样式
05.row.createCell((short)0).setCellStyle(cellStyle);//创建Excel工作表指定行的单元格
06.row.createCell((short)0).setCellValue(1); //设置Excel工作表的值
HSSFWorkbook wb = new HSSFWorkbook();//创建Excel工作簿对象
HSSFSheet sheet = wb.createSheet("new sheet");//创建Excel工作表对象
HSSFRow row = sheet.createRow((short)0); //创建Excel工作表的行
cellStyle = wb.createCellStyle();//创建单元格样式
row.createCell((short)0).setCellStyle(cellStyle);//创建Excel工作表指定行的单元格
row.createCell((short)0).setCellValue(1); //设置Excel工作表的值

3、设置sheet名称和单元格内容view plaincopy to clipboardprint?
01.wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);
02.cell.setEncoding((short) 1);
03.cell.setCellValue("单元格内容");
wb.setSheetName(1, "第一张工作表",HSSFCell.ENCODING_UTF_16);
cell.setEncoding((short) 1);
cell.setCellValue("单元格内容");
4、取得sheet的数目
view plaincopy to clipboardprint?
01.wb.getNumberOfSheets()
wb.getNumberOfSheets()
5、 根据index取得sheet对象view plaincopy to clipboardprint?
01.HSSFSheet sheet = wb.getSheetAt(0);
HSSFSheet sheet = wb.getSheetAt(0);
6、取得有效的行数view plaincopy to clipboardprint?
01.int rowcount = sheet.getLastRowNum();
int rowcount = sheet.getLastRowNum();
7、取得一行的有效单元格个数view plaincopy to clipboardprint?
01.row.getLastCellNum();
row.getLastCellNum();
8、单元格值类型读写

view plaincopy to clipboardprint?
01.cell.setCellType(HSSFCell.CELL_TYPE_STRING);//设置单元格为STRING类型
02.cell.getNumericCellValue();//读取为数值类型的单元格内容
cell.setCellType(HSSFCell.CELL_TYPE_STRING); //设置单元格为STRING类型
cell.getNumericCellValue();//读取为数值类型的单元格内容
9、设置列宽、行高

view plaincopy to clipboardprint?
01.sheet.setColumnWidth((short)column,(short)width);
02.row.setHeight((short)height);
sheet.setColumnWidth((short)column,(short)width);
row.setHeight((short)height);




10、添加区域，合并单元格

view plaincopy to clipboardprint?
01.Region region = newRegion((short)rowFrom,(short)columnFrom,(short)rowTo
02.,(short)columnTo);//合并从第rowFrom行columnFrom列
03.sheet.addMergedRegion(region);// 到rowTo行columnTo的区域
04.//得到所有区域
05.sheet.getNumMergedRegions()
Region region = newRegion((short)rowFrom,(short)columnFrom,(short)rowTo
,(short)columnTo);//合并从第rowFrom行columnFrom列
sheet.addMergedRegion(region);// 到rowTo行columnTo的区域
//得到所有区域
sheet.getNumMergedRegions()




11、保存Excel文件

view plaincopy to clipboardprint?
01.FileOutputStream fileOut = new FileOutputStream(path);
02.wb.write(fileOut);
FileOutputStream fileOut = new FileOutputStream(path);
wb.write(fileOut);




12、根据单元格不同属性返回字符串数值

view plaincopy to clipboardprint?
01.public String getCellStringValue(HSSFCell cell) {
02. String cellValue = "";
03. switch (cell.getCellType()) {
04. case HSSFCell.CELL_TYPE_STRING://字符串类型
05. cellValue = cell.getStringCellValue();
06.if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
07. cellValue=" ";
08. break;
09. case HSSFCell.CELL_TYPE_NUMERIC: //数值类型
10. cellValue = String.valueOf(cell.getNumericCellValue());
11. break;
12. case HSSFCell.CELL_TYPE_FORMULA: //公式
13. cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
14. cellValue = String.valueOf(cell.getNumericCellValue());
15. break;
16. case HSSFCell.CELL_TYPE_BLANK:
17. cellValue=" ";
18. break;
19. case HSSFCell.CELL_TYPE_BOOLEAN:
20. break;
21. case HSSFCell.CELL_TYPE_ERROR:
22. break;
23. default:
24. break;
25. }
26. return cellValue;
27. }
public String getCellStringValue(HSSFCell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_STRING://字符串类型
cellValue = cell.getStringCellValue();
if(cellValue.trim().equals("")||cellValue.trim().length()<=0)
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_NUMERIC: //数值类型
cellValue = String.valueOf(cell.getNumericCellValue());

break;
case HSSFCell.CELL_TYPE_FORMULA: //公式
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
cellValue=" ";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
break;
case HSSFCell.CELL_TYPE_ERROR:
break;
default:
break;
}
return cellValue;
}




13、常用单元格边框格式

view plaincopy to clipboardprint?
01.HSSFCellStyle style = wb.createCellStyle();
02.style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
03.style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
04.style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
05.style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_DOTTED);//下边框
style.setBorderLeft(HSSFCellStyle.BORDER_DOTTED);//左边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框




14、设置字体和内容位置

view plaincopy to clipboardprint?
01.HSSFFont f = wb.createFont();
02.f.setFontHeightInPoints((short) 11);//字号
03.f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
04.style.setFont(f);
05.style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
06.style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
07.style.setRotation(short rotation);//单元格内容的旋转的角度
08.HSSFDataFormat df = wb.createDataFormat();
09.style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式
10.cell.setCellFormula(string);//给单元格设公式
11.style.setRotation(short rotation);//单元格内容的旋转的角度
HSSFFont f = wb.createFont();
f.setFontHeightInPoints((short) 11);//字号
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗
style.setFont(f);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//左右居中
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//上下居中
style.setRotation(short rotation);//单元格内容的旋转的角度
HSSFDataFormat df = wb.createDataFormat();
style1.setDataFormat(df.getFormat("0.00%"));//设置单元格数据格式
cell.setCellFormula(string);//给单元格设公式
style.setRotation(short rotation);//单元格内容的旋转的角度




15、插入图片

view plaincopy to clipboardprint?
01.//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
02. ByteArrayOutputStream byteArrayOut = newByteArrayOutputStream();
03. BufferedImage bufferImg = ImageIO.read(newFile("ok.jpg"));
04. ImageIO.write(bufferImg,"jpg",byteArrayOut);

05.//读进一个excel模版
06.FileInputStream fos = newFileInputStream(filePathName+"/stencil.xlt");
07.fs = new POIFSFileSystem(fos);
08.//创建一个工作薄
09.HSSFWorkbook wb = new HSSFWorkbook(fs);
10.HSSFSheet sheet = wb.getSheetAt(0);
11.HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
12.HSSFClientAnchor anchor = newHSSFClientAnchor(0,0,1023,255,(short) 0,0,(short)10,10);
13.patriarch.createPicture(anchor ,wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));
//先把读进来的图片放到一个ByteArrayOutputStream中，以便产生ByteArray
ByteArrayOutputStream byteArrayOut = newByteArrayOutputStream();
BufferedImage bufferImg = ImageIO.read(new File("ok.jpg"));
ImageIO.write(bufferImg,"jpg",byteArrayOut);
//读进一个excel模版
FileInputStream fos = newFileInputStream(filePathName+"/stencil.xlt");
fs = new POIFSFileSystem(fos);
//创建一个工作薄
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,1023,255,(short)0,0,(short)10,10);
patriarch.createPicture(anchor ,wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG));




16、调整工作表位置

view plaincopy to clipboardprint?
01.HSSFWorkbook wb = new HSSFWorkbook();
02.HSSFSheet sheet = wb.createSheet("format sheet");
03.HSSFPrintSetup ps = sheet.getPrintSetup();
04.sheet.setAutobreaks(true);
05.ps.setFitHeight((short)1);
06.ps.setFitWidth((short)1);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFPrintSetup ps = sheet.getPrintSetup();
sheet.setAutobreaks(true);
ps.setFitHeight((short)1);
ps.setFitWidth((short)1);




17、设置打印区域

view plaincopy to clipboardprint?
01.HSSFSheet sheet = wb.createSheet("Sheet1");
02.wb.setPrintArea(0, "$A$1:$C$2");
HSSFSheet sheet = wb.createSheet("Sheet1");
wb.setPrintArea(0, "$A$1:$C$2");




18、标注脚注

view plaincopy to clipboardprint?
01.HSSFSheet sheet = wb.createSheet("format sheet");
02.HSSFFooter footer = sheet.getFooter()
03.footer.setRight( "Page " + HSSFFooter.page() + " of " +HSSFFooter.numPages() );
HSSFSheet sheet = wb.createSheet("format sheet");
HSSFFooter footer = sheet.getFooter()
footer.setRight( "Page " + HSSFFooter.page() + " of " +HSSFFooter.numPages() );




19、在工作单中清空行数据，调整行位置

view plaincopy to clipboardprint?
01.HSSFWorkbook wb = new HSSFWorkbook();
02.HSSFSheet sheet = wb.createSheet("row sheet");
03.// Create various cells and rows for spreadsheet.
04.// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 -5)

05.sheet.shiftRows(5, 10, -5);
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("row sheet");
// Create various cells and rows for spreadsheet.
// Shift rows 6 - 11 on the spreadsheet to the top (rows 0 -5)
sheet.shiftRows(5, 10, -5);




20、选中指定的工作表

view plaincopy to clipboardprint?
01.HSSFSheet sheet = wb.createSheet("row sheet");
02.heet.setSelected(true);
HSSFSheet sheet = wb.createSheet("row sheet");
heet.setSelected(true);




21、工作表的放大缩小


view plaincopy to clipboardprint?
01.HSSFSheet sheet1 = wb.createSheet("new sheet");
02.sheet1.setZoom(1,2); // 50 percent magnification
HSSFSheet sheet1 = wb.createSheet("new sheet");
sheet1.setZoom(1,2); // 50 percent magnification




22、头注和脚注

view plaincopy to clipboardprint?
01.HSSFSheet sheet = wb.createSheet("new sheet");
02.HSSFHeader header = sheet.getHeader();
03.header.setCenter("Center Header");
04.header.setLeft("Left Header");
05.header.setRight(HSSFHeader.font("Stencil-Normal", "Italic")+
06.HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-NormalItalic font and size 16");
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFHeader header = sheet.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic") +
HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italicfont and size 16");




23、自定义颜色

view plaincopy to clipboardprint?
01.HSSFCellStyle style = wb.createCellStyle();
02.style.setFillForegroundColor(HSSFColor.LIME.index);
03.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
04.HSSFFont font = wb.createFont();
05.font.setColor(HSSFColor.RED.index);
06.style.setFont(font);
07.cell.setCellStyle(style);
HSSFCellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIME.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.RED.index);
style.setFont(font);
cell.setCellStyle(style);



24、填充和颜色设置

view plaincopy to clipboardprint?
01.HSSFCellStyle style = wb.createCellStyle();
02.style.setFillBackgroundColor(HSSFColor.AQUA.index);
03.style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
04.HSSFCell cell = row.createCell((short) 1);
05.cell.setCellValue("X");
06.style = wb.createCellStyle();
07.style.setFillForegroundColor(HSSFColor.ORANGE.index);
08.style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
09.cell.setCellStyle(style);
HSSFCellStyle style = wb.createCellStyle();
style.setFillBackgroundColor(HSSFColor.AQUA.index);
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);

HSSFCellcell = row.createCell((short) 1);
cell.setCellValue("X");
style = wb.createCellStyle();
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(style);



25、强行刷新单元格公式

view plaincopy to clipboardprint?
01.HSSFFormulaEvaluator eval=newHSSFFormulaEvaluator((HSSFWorkbook) wb);
02.private static void updateFormula(Workbook wb,Sheet s,introw){
03. Row r=s.getRow(row);
04. Cell c=null;
05. FormulaEcaluator eval=null;
06. if(wb instanceof HSSFWorkbook)
07. eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
08. else if(wb instanceof XSSFWorkbook)
09. eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);
10. for(inti=r.getFirstCellNum();i<r.getLastCellNum();i++){
11. c=r.getCell(i);
12. if(c.getCellType()==Cell.CELL_TYPE_FORMULA)
13. eval.evaluateFormulaCell(c);
14. }
15. }
HSSFFormulaEvaluator eval=new HSSFFormulaEvaluator((HSSFWorkbook)wb);
private static void updateFormula(Workbook wb,Sheet s,introw){
Row r=s.getRow(row);
Cell c=null;
FormulaEcaluator eval=null;
if(wb instanceof HSSFWorkbook)
eval=new HSSFFormulaEvaluator((HSSFWorkbook) wb);
else if(wb instanceof XSSFWorkbook)
eval=new XSSFFormulaEvaluator((XSSFWorkbook) wb);
for(inti=r.getFirstCellNum();i<r.getLastCellNum();i++){
c=r.getCell(i);
if(c.getCellType()==Cell.CELL_TYPE_FORMULA)
eval.evaluateFormulaCell(c);
}
}



说明：FormulaEvaluator提供了evaluateFormulaCell(Cellcell)方法，计算公式保存结果，但不改变公式。而evaluateInCell(Cell cell)方法是计算公式，并将原公式替换为计算结果，也就是说该单元格的类型不在是Cell.CELL_TYPE_FORMULA而是Cell.CELL_TYPE_NUMBERIC。HSSFFormulaEvaluator提供了静态方法evaluateAllFormu

laCells(HSSFWorkbook wb) ，计算一个Excel文件的所有公式，用起来很方便。


转自: http://blog.sina.com.cn/s/blog_566ff1040100wcgv.html

世界贸易进出口数据 世界贸易进出口数据
原创粉丝点击
热门IT博客
中国网络诗歌网 投稿中国网络诗歌网 投稿
电信封80端口 投诉电信封80端口 投诉
中国还缺程序员吗
记经期的软件
怎么删除淘宝评价
广州数据分析培训课程
网络ping值不稳定
fsf知乎
图灵学院 java 下载
情感读本杂志软件
用scanf给二维数组赋值
扑克变牌衣淘宝女装
字符串数组赋值
pc6软件下载指南
伊宁seo搜索引擎
淘宝新店卖什么好
淘宝 原创棉麻女装
美工抠图总结
及时雨淘宝客软件骗局
云数据的云是什么意思
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 地球停转之日高清 地球停转之日 全集免费观看 地球停转之日免费 国语 地球停转之日中文版 地球停转之日2018免费观看 江湖不像话 不像话 像写一句话 娘子不像话妖孽来敲门 他漂亮的不像话 沧北 娘子不像话妖孽殿下来敲门 用像写一句话 爸爸像什么动物写一段话 仙女弹琴真像什么写一句话 生命像什么仿写一段话 生活像什么仿写一段话 用真像写一句话 如何回应乌鸦像写字台这句话 不也 不要回答 18岁不允许看1000在线看 实力不允许我炮灰 2018年不允许跨专业考研 缅甸不允许女孩嫁到中国吗 实力不允许 没有允许女儿不能流出来 徐增平不允许上辽宁舰 18岁不允许看1000部危险吗 南宁市电动车2019后不允许上牌 2019经济实用房不允许买卖 2019新政策为什么不允许去台湾 不入虎穴不得虎子 挿入大姨子二姨子b 不入虎穴焉得虎子主人公 带个系统入洪荒 木子一步 巨魔头 凝神入气穴 一个健气少侠堕入深渊的故事 不入流 引你入渊 流兮冉 带藏獒入洪荒 浮流西元 心软是一种不公平的善良

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里