1、简单创建单元格
/ * HFFSWorkbook读写xls格式
* XFFSWorkbook读写xlsx
* SXSSFWorkbook低内存占用,超过65536条数据使用
*/
//创建一个新的工作薄
HSSFWorkbook wb = new HSSFWorkbook();
FileOutputStream fos = new FileOutputStream(new File("1.xls"));
wb.write(fos);
fos.close();
System.out.println("create wb successfully");
//打开现有的工作薄
File file = new File("1.xls");
FileInputStream fis = new FileInputStream(file);
HSSFWorkbook hsf = new HSSFWorkbook(fis);
if(file.isFile() && file.exists()){
System.out.println("1.xls存在,并打开");
}else{
System.out.println("1.xl打开失败!");
}
2、插入数据
/*
* ID NAME 部门
* t1 Tom 01
* t2 Jim 02
* t3 Lock 03
*/
//创建一个sheet工作区
HSSFWorkbook hwb = new HSSFWorkbook();
HSSFSheet hs = hwb.createSheet("部门");
HSSFRow row;
Map<String,Object[]> map = new TreeMap<String,Object[]>();
map.put("1",new Object[]{"ID","Name","部门"});
map.put("2",new Object[]{"t1","Tom","01"});
map.put("3",new Object[]{"t2","Jim","02"});
map.put("4",new Object[]{"t3","Lock","03"});
Set<String> sets = map.keySet();
int rowId = 0;
for (String key : sets) {
row = hs.createRow(rowId++);
Object[] obj = map.get(key);
int cellId = 0;
for(Object o : obj){
Cell cell = row.createCell(cellId++);
cell.setCellValue((String)o);
}
}
FileOutputStream out = new FileOutputStream(new File("1.xls"));
hwb.write(out);
out.close();
System.out.println("successfully");
3、单元格设置
//单元格样式
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("cellStyle");
HSSFRow row = sheet.createRow((short)2);
row.setHeight((short)800);//设置高度 第2行 ,行高度800 ,下标1单元格添加“苏其昌”“黄志强” 合并区域1行4列
HSSFCell cell = row.createCell(1);
cell.setCellValue("昌哥");
cell.setCellValue("强哥");
sheet.addMergedRegion(new CellRangeAddress(
1,//first row
1,//last row
1,//first column
4 //last column
));
row = sheet.createRow(4); //第4行,下标0的单元格,高度100,宽度0-8000,左对齐,居上
cell = row.createCell(0);
row.setHeight((short)100);
sheet.setColumnWidth(0, 8000);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
cell.setCellValue("top--->left");
cell.setCellStyle(style);
//第6行 下标2的单元格 高度800,样式 居中、
row = sheet.createRow(6);
cell = row.createCell(1);
row.setHeight((short)800);
HSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cell.setCellStyle(style2);
cell.setCellValue("101");
row = sheet.createRow(10);
row.setHeight((short)800);
cell = row.createCell(2);
HSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
cell.setCellStyle(style3);
cell.setCellValue("sanqing");
row = sheet.createRow((short) 10);
row.setHeight((short) 800);
cell = row.createCell( 1);
cell.setCellValue("BORDER");
HSSFCellStyle style5 = wb.createCellStyle();
style5.setBorderBottom(HSSFCellStyle.BORDER_THICK);
style5.setBottomBorderColor(
IndexedColors.BLUE.getIndex());
style5.setBorderLeft(HSSFCellStyle.BORDER_DOUBLE);
style5.setLeftBorderColor(
IndexedColors.GREEN.getIndex());
style5.setBorderRight(HSSFCellStyle.BORDER_HAIR);
style5.setRightBorderColor(
IndexedColors.RED.getIndex());
style5.setBorderTop(HSSFCellStyle.BIG_SPOTS);
style5.setTopBorderColor(
IndexedColors.CORAL.getIndex());
cell.setCellStyle(style5);
FileOutputStream fos = new FileOutputStream("2.xls");
wb.write(fos);
fos.close();
4、字体设置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("s1");
HSSFRow row = sheet.createRow(10);
row.setHeight((short)1500);
HSSFCell cell = row.createCell(2);
cell.setCellValue("河南大学");
//设置字体样式
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.BRIGHT_GREEN.index);
font.setFontName("IMPACT");
font.setItalic(true);
font.setFontHeight((short)500);
//设置边框的样式
HSSFCellStyle s = wb.createCellStyle();
s.setBorderBottom((short)100);
s.setBorderTop(HSSFCellStyle.VERTICAL_TOP);
//旋转字体
s.setRotation((short)90);
//将字体样式加入,样式
s.setFont(font);
cell.setCellStyle(s);
FileOutputStream fos = new FileOutputStream("3.xls");
wb.write(fos);
fos.close();
5、函数计算
//1、创建一个HSSFWorkbook对象
HSSFWorkbook wb = new HSSFWorkbook();
//2、创建一个工作薄HSSFSheet
HSSFSheet sheet = wb.createSheet("我的工作薄");
//3、创建行第5行
HSSFRow row = sheet.createRow((short)5);
//4、创建工作单元格
HSSFCell cell = row.createCell(1);
cell.setCellValue("A=");
cell = row.createCell(2);
cell.setCellValue(2);
//创建行第6行
row = sheet.createRow((short)6);
cell = row.createCell(1);
cell.setCellValue("C=");
cell = row.createCell(2);
cell.setCellValue(2);
//创建行第七行
row = sheet.createRow((short)7);
cell = row.createCell(1);
cell.setCellValue("Total=");
cell = row.createCell(2);
//创建求和函数
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(c6:c7)");
cell.setCellValue(2);
//同理创建 power、max、pact、sqrt等
row = sheet.createRow(10);
cell = row.createCell(3);
//设置字体
HSSFFont font = wb.createFont();
font.setColor(HSSFColor.AQUA.index);
font.setBoldweight((short)500);
font.setFontHeight((short)500);
font.setFontName("微软雅黑");
//设置样式
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
style.setFillBackgroundColor(HSSFColor.YELLOW.index);
style.setLocked(true);
cell.setCellStyle(style);
cell.setCellValue("https://www.yiibai.com");
OutputStream os = new FileOutputStream("5.xls");
wb.write(os);
os.close();
6、打印设置
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("101");
wb.setPrintArea(0, 0,5,0,5);
sheet.getPrintSetup().setPageStart(HSSFPrintSetup.A4_PAPERSIZE);
//set display grid lines or not
sheet.setDisplayGridlines(true);
//set print grid lines or not
sheet.setPrintGridlines(true);
OutputStream os = new FileOutputStream("6.xls");
wb.write(os);
os.close();
//打印预览查看
7、总结:
1、先创建一个HSSFWorkbook对象
2、创建Sheet工作表
3、创建Row
4、创建单元格Cell
5、创建字体设置HSSFFont对象
6、创建HSSFCellStyle对象
7、将font加入style
8、创建一个OutputStream流对象
9、写入表
10、关闭流对象