eclipse中POI操作EXCEL简述

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);

 }