package com.hurricane.poi;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFFooter;
import org.apache.poi.hssf.usermodel.HSSFPalette;
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.HSSFColor.HSSFColorPredefined;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
/**
* Hello world!
*xls文件在另一文件打开的情况下,可以读取,但是不能写入,否则会报异常
*
*创建工作薄,工作单,行,单元格
*字符串,布尔,日期,数字的数据的插入与读取
*HSSFCellStyle的使用(前景色,背景色,填充模式,边框颜色,边框粗细,对齐方式,数据格式)
*HSSFPalette自定义颜色的使用
*HSSFDataFormat来格式化数字与日期,日期默认以数字形式存储,为了直观查看,日期大多需要格式化
*HSSFRichTextString更灵活的文本插入
*行高,列宽的设置,字体的设置,读取单元格内容前的校验
*页头的设置,工作单的缩放,工作单中移动行,调整行的上下位置,调整工作单位置,设置打印区域等没有在此给出示例代码,详见文档
*/
public class App
{
public static void main( String[] args ) throws Exception {
// protypeWrite();
readAndUpdate();
}
private static void readAndUpdate() throws Exception {
POIFSFileSystem fileSystem = new POIFSFileSystem(new File("E:/test.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
HSSFSheet sheet1 = workbook.getSheet("第一页");
HSSFRow row0 = sheet1.getRow(0);
HSSFCell cell2 = row0.getCell(2);
System.out.println(cell2.getCellTypeEnum().toString());
System.out.println(cell2.getDateCellValue());
System.out.println(cell2.getNumericCellValue());
HSSFCell cell4 = row0.getCell(4);
//使用这种方式做取值前的格式校验
if (cell4.getCellTypeEnum()==CellType.BOOLEAN) {
System.out.println(cell4.getBooleanCellValue());
}
//HSSFRichTextString可以向xls文件中插入一个字符串,其不同字符拥有不同font
HSSFRichTextString richTextString = new HSSFRichTextString("Hello World!!!");
Font font1 = workbook.createFont();
font1.setColor(HSSFColorPredefined.SKY_BLUE.getIndex());
Font font2 = workbook.createFont();
font2.setColor(HSSFColorPredefined.SEA_GREEN.getIndex());
richTextString.applyFont(0, 6, font1);
richTextString.applyFont(6, 13, font2);
row0.createCell(8).setCellValue(richTextString);
//HSSFDataFormat用于(不止)数字格式自定义
HSSFCellStyle style = workbook.createCellStyle();
HSSFDataFormat format = workbook.createDataFormat();
style.setDataFormat(format.getFormat("#,##0.0000"));//#,##0.0000代表的意义?
HSSFCell cell9 = row0.createCell(9);//如果该cell里面本来就有值,会覆盖掉
cell9.setCellValue(3.1415926);
cell9.setCellStyle(style);
//页脚的设置
HSSFFooter footer = sheet1.getFooter();
footer.setRight("Page "+HSSFFooter.page()+" of "+HSSFFooter.numPages());
OutputStream out = new FileOutputStream("E:/test.xls");
workbook.write(out);
workbook.close();
out.close();
}
private static void protypeWrite() throws Exception {
// TODO Auto-generated method stub
FileOutputStream outputStream = new FileOutputStream("E:/test.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet1 = workbook.createSheet("第一页");
//new CellRangeAddress(row1, row2, column1, column2)
sheet1.addMergedRegion(new CellRangeAddress(2, 4, 10, 12));
//sheet1.createFreezePane(column, row)
sheet1.createFreezePane(1, 2);
HSSFCellStyle style = workbook.createCellStyle();
//字体样式的设置
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 22);
font.setBold(true);
font.setColor(HSSFColorPredefined.BLUE.getIndex());
style.setFont(font);
//为什么有了前景色,背景色就看不到效果了,背景色与前景色在xls中的应用
//必须指定填充方式,否则不起作用
// style.setFillBackgroundColor(HSSFColorPredefined.BROWN.getIndex());
style.setFillForegroundColor(HSSFColorPredefined.RED.getIndex());
//设置单元格的背景色填充模式,各种之间的区别没去了解
style.setFillPattern(FillPatternType.FINE_DOTS);
//设置相应位置(此处为右边)的边框颜色
style.setRightBorderColor(HSSFColorPredefined.SKY_BLUE.getIndex());
//设置相应位置(此处为右边)的边框粗细
style.setBorderRight(BorderStyle.THICK);
//设置水平与垂直的居中方式
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
//自动换行开启,会使行高相应自动增大
style.setWrapText(true);
HSSFCellStyle dateStyle = workbook.createCellStyle();
dateStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));
//自定义颜色
HSSFPalette palette = workbook.getCustomPalette();
palette.setColorAtIndex(HSSFColorPredefined.BLACK.getIndex(), (byte)153, (byte)0, (byte)0);
dateStyle.setFillForegroundColor(HSSFColorPredefined.BLACK.getIndex());
dateStyle.setFillPattern(FillPatternType.FINE_DOTS);
for (int i = 0; i < 100; i++) {
insertDataToRow(sheet1.createRow(i),style,dateStyle);
}
//设置列宽
sheet1.setColumnWidth(0, 8000);
workbook.write(outputStream);
workbook.close();
outputStream.close();
}
private static void insertDataToRow(HSSFRow row0,HSSFCellStyle... style) {
if (row0.getRowNum()==0) {
//设置行高
row0.setHeight((short) 3000);
}
HSSFCell cell = row0.createCell(0);
//可以实现特殊字符的转意,如cell.setCellValue("字符串\n数据");
cell.setCellValue("字符串数据");
cell.setCellStyle(style[0]);
row0.createCell(1).setCellValue(3.1415926);
//这种方式直接赋值,是无法在xls文件中得到相应的日期的(格式是Numeric,是小数,但是若是读取还是能读到的),需要指定样式,像是cell7那样
row0.createCell(2).setCellValue(new Date());
Calendar calendar = Calendar.getInstance();
calendar.setTime(new Date());
//这种方式直接赋值,是无法在xls文件中得到相应的日期的(格式是Numeric,是小数,但是若是读取还是能读到的),需要指定样式,像是cell7那样
row0.createCell(3).setCellValue(calendar);
row0.createCell(4).setCellValue(true);
row0.createCell(5).setCellValue("中文_english");
row0.createCell(6).setCellType(CellType.ERROR);
//设置单元格内容为date的正确方式
HSSFCell cell7 = row0.createCell((short)7);
cell7.setCellValue(new Date());
cell7.setCellStyle(style[1]);
}
}