eclipse中POI操作EXCEL简述 博客分类： exceleclipse eclipse excelPOI
2.1创建workbook
HSSFWorkbook wb = new HSSFWorkbook();
//使用默认的构造方法创建workbook
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
//指定文件名
wb.write(fileOut);
//输出到文件
fileOut.close();
2.2创建一个sheet
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet("new sheet");
//workbook创建sheet
HSSFSheet sheet2 = wb.createSheet("second sheet");
//workbook创建另外的sheet
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
2.3创建cells
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
//注意以下的代码很多方法的参数是short 而不是int 所以需要做一次类型转换
HSSFRow row = sheet.createRow((short)0);
//sheet 创建一行
HSSFCell cell = row.createCell((short)0);
//行创建一个单元格
cell.setCellValue(1);
//设定单元格的值
//值的类型参数有多中double ,String ,boolean,
row.createCell((short)1).setCellValue(1.2);
row.createCell((short)2).setCellValue("This is a string");
row.createCell((short)3).setCellValue(true);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
2.4创建日期cells
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("new sheet");
HSSFRow row = sheet.createRow((short)0);
HSSFCell cell = row.createCell((short)0);
//设定值为日期
cell.setCellValue(new Date());
HSSFCellStyle cellStyle = wb.createCellStyle();
//指定日期显示格式
cellStyle.setDataFormat(HSSFDataFormat.getFormat("m/d/yy h:mm"));
cell = row.createCell((short)1);
cell.setCellValue(new Date());
//设定单元格日期显示格式
cell.setCellStyle(cellStyle);
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();
2.5设定单元格格式
单元格格式的设定有很多形式包括单元格的对齐方式，内容的字体设置，单元格的背景色等，因为形式比较多，只举一些例子.以下的例子在POI1.5中可能会有所改变具体查看API.
// Aqua background
HSSFCellStyle style = wb.createCellStyle();
//创建一个样式
style.setFillBackgroundColor(HSSFCellStyle.AQUA);
//设定此样式的的背景颜色填充
style.setFillPattern(HSSFCellStyle.BIG_SPOTS);
//样式的填充类型。
//有多种式样如:
//HSSFCellStyle.BIG_SPOTS
//HSSFCellStyle.FINE_DOTS
//HSSFCellStyle.SPARSE_DOTS等
style.setAlignment(HSSFCellStyle.ALIGN_CENTER );
//居中对齐
style.setFillBackgroundColor(HSSFColor.GREEN.index);
//设定单元个背景颜色
style.setFillForegroundColor(HSSFColor.RED.index);
//设置单元格显示颜色
HSSFCell cell = row.createCell((short) 1);
cell.setCellValue("X");
cell.setCellStyle(style);
目的：要在excel中画一条线。
public void drawMyLine()
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet(“excel文件名”);
// 创建HSSFPatriarch对象
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
drawLine(srcCol, srcRow, dstCol, dstRow, srcX, srcY, dstX, dstY, patriarch);
saveExcelFile(workbook,”e:\aa.xlsx”,3);
}
/**
* 从一个坐标点到另一个坐标点画一根线,内含偏移量修正代码。
* @param srcCol 起点单元格列
* @param srcRow 起点单元格行
* @param dstCol 终点单元格列
* @param dstRow 终点单元格行
* @param srcX 起点单元格内X偏移量
* @param srcY 起点单元格内Y偏移量
* @param dstX 终点单元格内X偏移量
* @param dstY 终点单元格内Y偏移量
* @param patriarch 画线需要的对象
*/
privatevoid drawLine(int srcCol, int srcRow, int dstCol, int dstRow, int srcX, int srcY, int dstX, int dstY, HSSFPatriarch patriarch)
{
HSSFClientAnchor anchorPA = new HSSFClientAnchor(srcX, srcY, dstX, dstY, (short)srcCol, srcRow, (short)dstCol, dstRow);
// 该画线函数存在严重问题，从同一点向其他方向画线会出现单元格内偏移量出现交错，因此写了如下代码进行修正。
if (dstCol > srcCol && dstRow > srcRow)
{
// 保持原状
}
elseif (dstCol > srcCol && dstRow < srcRow)
{
anchorPA.setDy1(dstY);
anchorPA.setDy2(srcY);
}
elseif (dstCol < srcCol && dstRow < srcRow)
{
anchorPA.setDx1(dstX);
anchorPA.setDx2(srcX);
anchorPA.setDy1(dstY);
anchorPA.setDy2(srcY);
}
elseif (dstCol < srcCol && dstRow > srcRow)
{
anchorPA.setDx1(dstX);
anchorPA.setDx2(srcX);
}
// 偏移量修正完毕。
// 设置图形不随单元格改变
anchorPA.setAnchorType(2);
// 设置线段样式并画线
HSSFSimpleShape line = patriarch.createSimpleShape(anchorPA);
line.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
line.setLineStyle(HSSFShape.LINESTYLE_SOLID);
line.setLineWidth(6350);
}
/**
* 保存带有模板的Excel文件, 保存后需要将模板sheet页删除.
* @param workbook 工作表
* @param fileName 文件名
* @param templateSheetNum 模版sheet的个数，保存excel前要移除这些模版
* @throws Exception
*/
publicstaticvoid saveExcelFile(HSSFWorkbook workbook, String fileName, int templateSheetNum) throws Exception
{
FileOutputStream output = null;
try
{
// 输出流, 将PPT写到磁盘中
output = new FileOutputStream(fileName);
// 删除模板页
for (int i = 0; i < templateSheetNum; i++)
{
workbook.removeSheetAt(0);
}
workbook.write(output);
output.flush();
output.close();
}
catch (Exception e)
{
throw e;
}
finally
{
try
{
// 关闭输出流
if (output != null)
{
output.close();
}
}
catch (IOException e)
{
e.printStackTrace();
}
}
}
目的：在excel中嵌入一个图片
public void drawPicture()
{
HSSFClientAnchor anchorPA = new HSSFClientAnchor();
// 图形不随单元格改变
anchorPA.setAnchorType(2);
// 设置图形左上角的锚点,x = 20,y = 20处画图形
anchorPA.setCol1(20);
anchorPA.setRow1(20);
InputStream stream = this.getClass().getResourceAsStream(file);
byte[] data = newbyte[stream.available()];
stream.read(data);
stream.close();
// 画图形
patriarch.createPicture(anchorPA,workbook.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG)).resize();
FileOutputStream output = new FileOutputStream(fileName);
workbook.write(output);
output.close();
}
目的：在指定的单元格写东西
// 设置站点名称以及单元格格式
HSSFSheet sheet = workbook.getSheetAt(0);
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
// 站点名单元格偏移
int cellOffset = 2;
if (sheet.getRow(row + cellOffset) == null)
{
HSSFCell cell = sheet.createRow(row + cellOffset).createCell(col);
cell.setCellValue(new HSSFRichTextString(neModel.getName()));
cell.setCellStyle(style);
}
else
{
HSSFCell cell = sheet.getRow(row + cellOffset).createCell(col);
cell.setCellValue(new HSSFRichTextString(neModel.getName()));
cell.setCellStyle(style);
}