POI中可能会用到一些需要设置EXCEL单元格格式的操作小结:
先获取工作薄对象:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle setBorder = wb.createCellStyle();
一、设置背景色:
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
二、设置边框:
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
三、设置居中:
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
四、设置字体:
HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);
setBorder.setFont(font);//选择需要用到的字体格式
五、设置列宽:
sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值
六、设置自动换行:
setBorder.setWrapText(true);//设置自动换行
七、合并单元格:
Region region1 = new Region(0, (short) 0, 0, (short) 6);
//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
sheet.addMergedRegion(region1);
八、加边框
HSSFCellStyle cellStyle= wookBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BorderBORDER_MEDIUM);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
另附:完整小例子一个
开发环境:IntelliJ IDEA 10.0.2
@ResponseBody
@RequestMapping(value = "/reportForms/joinStocktaking/exportStorage.api")
public AjaxResponse exportStorage(@RequestBody StorageModel model) throws Exception {
if (logger.isDebugEnabled())
logger.debug("tmpdir is, {}", System.getProperty(JAVA_IO_TMPDIR));
int row = 1;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet hssfSheet = workbook.createSheet();
HSSFCellStyle style = workbook.createCellStyle();
style.setFillBackgroundColor(HSSFCellStyle.LEAST_DOTS);
style.setFillPattern(HSSFCellStyle.LEAST_DOTS);
//设置Excel中的边框(表头的边框)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setTopBorderColor(HSSFColor.BLACK.index);
//设置字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 14); // 字体高度
font.setFontName(" 黑体 "); // 字体
style.setFont(font);
HSSFRow firstRow = hssfSheet.createRow((short) 0);
HSSFCell firstCell = firstRow.createCell(0);
firstRow.setHeight((short) 400);
//设置Excel中的背景
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
firstCell.setCellValue(new HSSFRichTextString("库房"));
firstCell.setCellStyle(style);
HSSFCell secondCell = firstRow.createCell(1);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
secondCell.setCellValue(new HSSFRichTextString("库区"));
secondCell.setCellStyle(style);
HSSFCell threeCell = firstRow.createCell(2);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
threeCell.setCellValue(new HSSFRichTextString("物料编号"));
threeCell.setCellStyle(style);
HSSFCell fourCell = firstRow.createCell(3);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
fourCell.setCellValue(new HSSFRichTextString("物料名称"));
fourCell.setCellStyle(style);
HSSFCell fiveCell = firstRow.createCell(4);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
fiveCell.setCellValue(new HSSFRichTextString("在库数量"));
fiveCell.setCellStyle(style);
HSSFCell sixCell = firstRow.createCell(5);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
sixCell.setCellValue(new HSSFRichTextString("锁定数量"));
sixCell.setCellStyle(style);
//设置列宽
hssfSheet.setColumnWidth(0, 7000);
hssfSheet.setColumnWidth(1, 8000);
hssfSheet.setColumnWidth(2, 4000);
hssfSheet.setColumnWidth(3, 6000);
hssfSheet.setColumnWidth(4, 4000);
hssfSheet.setColumnWidth(5, 4000);
List<?> list = joinStocktackingService.findjoinStorageByTerm(model.getWareHouse(), model.getStockArea(), model.getMaterialCode(), model.getMaterialName());
for (Object object : list) {
Object[] objects = (Object[]) object;
Storage storage = (Storage) objects[0];
Warehouse warehouse = (Warehouse) objects[1];
StockArea stockArea = (StockArea) objects[2];
Material material = (Material) objects[3];
//设置Excel中的边框
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
HSSFRow hssfRow = hssfSheet.createRow((short) row);
HSSFCell firstHssfCell = hssfRow.createCell(0);//库房
firstHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
firstHssfCell.setCellValue(new HSSFRichTextString(warehouse.getName()));
firstHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell secondHssfCell = hssfRow.createCell(1);
secondHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
secondHssfCell.setCellValue(new HSSFRichTextString(stockArea.getName()));
secondHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell threeHssfCell = hssfRow.createCell(2);
threeHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
threeHssfCell.setCellValue(new HSSFRichTextString(material.getCode()));
threeHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell fourHssfCell = hssfRow.createCell(3);
fourHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
fourHssfCell.setCellValue(new HSSFRichTextString(material.getName()));
fourHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell fiveHssfCell = hssfRow.createCell(4);
fiveHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
fiveHssfCell.setCellValue(new HSSFRichTextString(String.valueOf(storage.getQty())));
fiveHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell sixHssfCell = hssfRow.createCell(5);
sixHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
sixHssfCell.setCellValue(new HSSFRichTextString(String.valueOf(storage.getQtyLocked())));
sixHssfCell.setCellStyle(cellStyle);//设置单元格的样式
row++;
}
String newFileName = String.format("%s.%s", "joinStocktaking-" + (new Date()).getTime(), "xls");
String uploadPath = FileUtils.contractPath(System.getProperty(JAVA_IO_TMPDIR), newFileName);
FileOutputStream fOut = new FileOutputStream(uploadPath);
workbook.write(fOut);
fOut.flush();
fOut.close();
return AjaxResponse.createSuccess(newFileName);
}
再来张最终效果图:
示例1将演示
如何利用Jakarta POI API 创建Excel 文档。
2 import org.apache.poi.hssf.usermodel.HSSFSheet;
3 import org.apache.poi.hssf.usermodel.HSSFRow;
4 import org.apache.poi.hssf.usermodel.HSSFCell;
5 import java.io.FileOutputStream;
6 public class CreateXL {
7 /** Excel 文件要存放的位置,假定在D盘JTest目录下*/
8 public static String outputFile="D:/JTest/ gongye.xls";
9 public static void main(String argv[]){
10 try{
11 // 创建新的Excel 工作簿
12 HSSFWorkbook workbook = new HSSFWorkbook();
13 // 在Excel工作簿中建一工作表,其名为缺省值
14 // 如要新建一名为"效益指标"的工作表,其语句为:
15 // HSSFSheet sheet = workbook.createSheet("效益指标");
16 HSSFSheet sheet = workbook.createSheet();
17 // 在索引0的位置创建行(最顶端的行)
18 HSSFRow row = sheet.createRow((short)0);
19 //在索引0的位置创建单元格(左上端)
20 HSSFCell cell = row.createCell((short) 0);
21 // 定义单元格为字符串类型
22 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
23 // 在单元格中输入一些内容
24 cell.setCellValue("增加值");
25 // 新建一输出文件流
26 FileOutputStream fOut = new FileOutputStream(outputFile);
27 // 把相应的Excel 工作簿存盘
28 workbook.write(fOut);
29 fOut.flush();
30 // 操作结束,关闭文件
31 fOut.close();
32 System.out.println("文件生成");
33
34
35 }catch(Exception e) {
36 System.out.println("已运行 xlCreate() : " + e );
37 }
38 }
39}
读取Excel文档中的数据
示例2将演示如何读取Excel文档中的数据。假定在D盘JTest目录下有一个文件名为gongye.xls的Excel文件。
示例2程序如下:
2 import org.apache.poi.hssf.usermodel.HSSFSheet;
3 import org.apache.poi.hssf.usermodel.HSSFRow;
4 import org.apache.poi.hssf.usermodel.HSSFCell;
5 import java.io.FileInputStream;
6 public class ReadXL {
7 /** Excel文件的存放位置。注意是正斜线*/
8 public static String fileToBeRead="D:/JTest/ gongye.xls";
9 public static void main(String argv[]){
10 try{
11 // 创建对Excel工作簿文件的引用
12 HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
13 // 创建对工作表的引用。
14 // 本例是按名引用(让我们假定那张表有着缺省名"Sheet1")
15 HSSFSheet sheet = workbook.getSheet("Sheet1");
16 // 也可用getSheetAt(int index)按索引引用,
17 // 在Excel文档中,第一张工作表的缺省索引是0,
18 // 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
19 // 读取左上端单元
20 HSSFRow row = sheet.getRow(0);
21 HSSFCell cell = row.getCell((short)0);
22 // 输出单元内容,cell.getStringCellValue()就是取所在单元的值
23 System.out.println("左上端单元是: " + cell.getStringCellValue());
24 }catch(Exception e) {
25 System.out.println("已运行xlRead() : " + e );
26 }
27}
设置单元格格式
在这里,我们将只介绍一些和格式设置有关的语句,我们假定workbook就是对一个工作簿的引用。在Java中,第一步要做的就是创建和设置字体和单元格的格式,然后再应用这些格式:
1、创建字体,设置其为红色、粗体:
2 font.setColor(HSSFFont.COLOR_RED);
3 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
2、创建格式
2 cellStyle.setFont(font);
3、应用格式
2 cell.setCellStyle(cellStyle);
3 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
4 cell.setCellValue("标题 ");
总之,如本篇文章所演示的一样,Java程序员不必担心Excel工作表中的数据了,利用Jakarta POI API,我们就可以轻易的在程序中存取Excel文档。
POI中可能会用到一些需要设置EXCEL单元格格式的操作小结:
先获取工作薄对象:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFCellStyle setBorder = wb.createCellStyle();
一、设置背景色:
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
二、设置边框:
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
三、设置居中:
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
四、设置字体:
HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小
HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);
setBorder.setFont(font);//选择需要用到的字体格式
五、设置列宽:
sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值
六、设置自动换行:
setBorder.setWrapText(true);//设置自动换行
七、合并单元格:
Region region1 = new Region(0, (short) 0, 0, (short) 6);
//参数1:行号 参数2:起始列号 参数3:行号 参数4:终止列号
sheet.addMergedRegion(region1);
八、加边框
HSSFCellStyle cellStyle= wookBook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BorderBORDER_MEDIUM);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
另附:完整小例子一个
开发环境:IntelliJ IDEA 10.0.2
@ResponseBody
@RequestMapping(value = "/reportForms/joinStocktaking/exportStorage.api")
public AjaxResponse exportStorage(@RequestBody StorageModel model) throws Exception {
if (logger.isDebugEnabled())
logger.debug("tmpdir is, {}", System.getProperty(JAVA_IO_TMPDIR));
int row = 1;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet hssfSheet = workbook.createSheet();
HSSFCellStyle style = workbook.createCellStyle();
style.setFillBackgroundColor(HSSFCellStyle.LEAST_DOTS);
style.setFillPattern(HSSFCellStyle.LEAST_DOTS);
//设置Excel中的边框(表头的边框)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
style.setTopBorderColor(HSSFColor.BLACK.index);
//设置字体
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 14); // 字体高度
font.setFontName(" 黑体 "); // 字体
style.setFont(font);
HSSFRow firstRow = hssfSheet.createRow((short) 0);
HSSFCell firstCell = firstRow.createCell(0);
firstRow.setHeight((short) 400);
//设置Excel中的背景
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
firstCell.setCellValue(new HSSFRichTextString("库房"));
firstCell.setCellStyle(style);
HSSFCell secondCell = firstRow.createCell(1);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
secondCell.setCellValue(new HSSFRichTextString("库区"));
secondCell.setCellStyle(style);
HSSFCell threeCell = firstRow.createCell(2);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
threeCell.setCellValue(new HSSFRichTextString("物料编号"));
threeCell.setCellStyle(style);
HSSFCell fourCell = firstRow.createCell(3);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
fourCell.setCellValue(new HSSFRichTextString("物料名称"));
fourCell.setCellStyle(style);
HSSFCell fiveCell = firstRow.createCell(4);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
fiveCell.setCellValue(new HSSFRichTextString("在库数量"));
fiveCell.setCellStyle(style);
HSSFCell sixCell = firstRow.createCell(5);
firstRow.setHeight((short) 400);
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillBackgroundColor(HSSFColor.GREEN.index);
sixCell.setCellValue(new HSSFRichTextString("锁定数量"));
sixCell.setCellStyle(style);
//设置列宽
hssfSheet.setColumnWidth(0, 7000);
hssfSheet.setColumnWidth(1, 8000);
hssfSheet.setColumnWidth(2, 4000);
hssfSheet.setColumnWidth(3, 6000);
hssfSheet.setColumnWidth(4, 4000);
hssfSheet.setColumnWidth(5, 4000);
List<?> list = joinStocktackingService.findjoinStorageByTerm(model.getWareHouse(), model.getStockArea(), model.getMaterialCode(), model.getMaterialName());
for (Object object : list) {
Object[] objects = (Object[]) object;
Storage storage = (Storage) objects[0];
Warehouse warehouse = (Warehouse) objects[1];
StockArea stockArea = (StockArea) objects[2];
Material material = (Material) objects[3];
//设置Excel中的边框
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
HSSFRow hssfRow = hssfSheet.createRow((short) row);
HSSFCell firstHssfCell = hssfRow.createCell(0);//库房
firstHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
firstHssfCell.setCellValue(new HSSFRichTextString(warehouse.getName()));
firstHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell secondHssfCell = hssfRow.createCell(1);
secondHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
secondHssfCell.setCellValue(new HSSFRichTextString(stockArea.getName()));
secondHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell threeHssfCell = hssfRow.createCell(2);
threeHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
threeHssfCell.setCellValue(new HSSFRichTextString(material.getCode()));
threeHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell fourHssfCell = hssfRow.createCell(3);
fourHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
fourHssfCell.setCellValue(new HSSFRichTextString(material.getName()));
fourHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell fiveHssfCell = hssfRow.createCell(4);
fiveHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
fiveHssfCell.setCellValue(new HSSFRichTextString(String.valueOf(storage.getQty())));
fiveHssfCell.setCellStyle(cellStyle);//设置单元格的样式
HSSFCell sixHssfCell = hssfRow.createCell(5);
sixHssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
sixHssfCell.setCellValue(new HSSFRichTextString(String.valueOf(storage.getQtyLocked())));
sixHssfCell.setCellStyle(cellStyle);//设置单元格的样式
row++;
}
String newFileName = String.format("%s.%s", "joinStocktaking-" + (new Date()).getTime(), "xls");
String uploadPath = FileUtils.contractPath(System.getProperty(JAVA_IO_TMPDIR), newFileName);
FileOutputStream fOut = new FileOutputStream(uploadPath);
workbook.write(fOut);
fOut.flush();
fOut.close();
return AjaxResponse.createSuccess(newFileName);
}
再来张最终效果图:
示例1将演示
如何利用Jakarta POI API 创建Excel 文档。
2 import org.apache.poi.hssf.usermodel.HSSFSheet;
3 import org.apache.poi.hssf.usermodel.HSSFRow;
4 import org.apache.poi.hssf.usermodel.HSSFCell;
5 import java.io.FileOutputStream;
6 public class CreateXL {
7 /** Excel 文件要存放的位置,假定在D盘JTest目录下*/
8 public static String outputFile="D:/JTest/ gongye.xls";
9 public static void main(String argv[]){
10 try{
11 // 创建新的Excel 工作簿
12 HSSFWorkbook workbook = new HSSFWorkbook();
13 // 在Excel工作簿中建一工作表,其名为缺省值
14 // 如要新建一名为"效益指标"的工作表,其语句为:
15 // HSSFSheet sheet = workbook.createSheet("效益指标");
16 HSSFSheet sheet = workbook.createSheet();
17 // 在索引0的位置创建行(最顶端的行)
18 HSSFRow row = sheet.createRow((short)0);
19 //在索引0的位置创建单元格(左上端)
20 HSSFCell cell = row.createCell((short) 0);
21 // 定义单元格为字符串类型
22 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
23 // 在单元格中输入一些内容
24 cell.setCellValue("增加值");
25 // 新建一输出文件流
26 FileOutputStream fOut = new FileOutputStream(outputFile);
27 // 把相应的Excel 工作簿存盘
28 workbook.write(fOut);
29 fOut.flush();
30 // 操作结束,关闭文件
31 fOut.close();
32 System.out.println("文件生成");
33
34
35 }catch(Exception e) {
36 System.out.println("已运行 xlCreate() : " + e );
37 }
38 }
39}
读取Excel文档中的数据
示例2将演示如何读取Excel文档中的数据。假定在D盘JTest目录下有一个文件名为gongye.xls的Excel文件。
示例2程序如下:
2 import org.apache.poi.hssf.usermodel.HSSFSheet;
3 import org.apache.poi.hssf.usermodel.HSSFRow;
4 import org.apache.poi.hssf.usermodel.HSSFCell;
5 import java.io.FileInputStream;
6 public class ReadXL {
7 /** Excel文件的存放位置。注意是正斜线*/
8 public static String fileToBeRead="D:/JTest/ gongye.xls";
9 public static void main(String argv[]){
10 try{
11 // 创建对Excel工作簿文件的引用
12 HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));
13 // 创建对工作表的引用。
14 // 本例是按名引用(让我们假定那张表有着缺省名"Sheet1")
15 HSSFSheet sheet = workbook.getSheet("Sheet1");
16 // 也可用getSheetAt(int index)按索引引用,
17 // 在Excel文档中,第一张工作表的缺省索引是0,
18 // 其语句为:HSSFSheet sheet = workbook.getSheetAt(0);
19 // 读取左上端单元
20 HSSFRow row = sheet.getRow(0);
21 HSSFCell cell = row.getCell((short)0);
22 // 输出单元内容,cell.getStringCellValue()就是取所在单元的值
23 System.out.println("左上端单元是: " + cell.getStringCellValue());
24 }catch(Exception e) {
25 System.out.println("已运行xlRead() : " + e );
26 }
27}
设置单元格格式
在这里,我们将只介绍一些和格式设置有关的语句,我们假定workbook就是对一个工作簿的引用。在Java中,第一步要做的就是创建和设置字体和单元格的格式,然后再应用这些格式:
1、创建字体,设置其为红色、粗体:
2 font.setColor(HSSFFont.COLOR_RED);
3 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
2、创建格式
2 cellStyle.setFont(font);
3、应用格式
2 cell.setCellStyle(cellStyle);
3 cell.setCellType(HSSFCell.CELL_TYPE_STRING);
4 cell.setCellValue("标题 ");
总之,如本篇文章所演示的一样,Java程序员不必担心Excel工作表中的数据了,利用Jakarta POI API,我们就可以轻易的在程序中存取Excel文档。