poi 操作excel 2007 示例

//拿来挨个案例运行一下，就明白创建和读取的基本过程了

package cn.poi.readexcel;

import java.io.FileInputStream;
import java.io.FileOutputStream;


import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * Excel 2007 poi api 基本 操作大全
 * cn.poi.readexcel.PlayExcel.java
 * @author ffr
 * created at 2012-3-12
 * 需要更多，请参见：http://poi.apache.org/spreadsheet/quick-guide.html
 * 操作excel2007，使用到的包：
 * poi-3.8-beta5\poi-3.8-beta5-20111217.jar
 * poi-3.8-beta5\poi-ooxml-3.8-beta5-20111217.jar
 * poi-3.8-beta5\poi-ooxml-schemas-3.8-beta5-20111217.jar
 * poi-3.8-beta5\ooxml-lib\dom4j-1.6.1.jar
 * poi-3.8-beta5\ooxml-lib\stax-api-1.0.1.jar
 * poi-3.8-beta5\ooxml-lib\xmlbeans-2.3.0.jar
 * 可以通过网址：http://poi.apache.org/download.html进行下载，请勿使用迅雷下载资源，否则出现文件损坏提示
 */
public class PlayExcel {
public static void main(String[] args) throws Exception{
final String readpath = "d://test.xlsx";
final String writepath = "d://write.xlsx";
//Workbook createwb = new XSSFWorkbook();
FileOutputStream output = new FileOutputStream(writepath); 
//一个一个分别执行，否则提示上个操作没有保存Fail to save的异常
//PlayExcel.createWorkbook(createwb, output);
//PlayExcel.createSheet(createwb, output);
//PlayExcel.createCell(createwb, output);
//PlayExcel.createStyle(createwb, output);
//PlayExcel.createBorder(createwb, output);
output.close();

//下面是读取
FileInputStream input = new FileInputStream(readpath);
Workbook readwb = new XSSFWorkbook(input);
PlayExcel.readSheet(readwb);
input.close();


}


/**
* 创建excel表
* @param writepath
* @throws Exception
*/
public static void createWorkbook(Workbook wb, FileOutputStream output) throws Exception{
wb.write(output);
print("创建excel表成功");
}
/**
* 创建工作区间
* @param strings
*/
public static void createSheet(Workbook wb, FileOutputStream output) throws Exception{
Sheet sheet1 = wb.createSheet("sheet1");
Sheet sheet2 = wb.createSheet("sheet2");
//将工作区间的非法字符用空格代替
String safename = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]");
Sheet sheet3 = wb.createSheet(safename);
//写回
wb.write(output);
print("创建工作区间成功");
}
/**
* 创建单元格
* @param strings
*/
public static void createCell(Workbook wb, FileOutputStream output) throws Exception{
Sheet sheet = wb.createSheet("单元格创建");
//用来实现对文本框的编写，或者日期的编写等
CreationHelper createHelper = wb.getCreationHelper();
//从0行0列开始
Row row = sheet.createRow(0);
Cell cell = row.createCell(0);
cell.setCellValue(1);
row.createCell(1).setCellValue(1.2);
   row.createCell(2).setCellValue(
        createHelper.createRichTextString("This is a string"));
   row.createCell(3).setCellValue(true);
   //写回
   wb.write(output);
   print("创建单元格成功");
}
/**
* 使用不同的样式，两个方法实现，包括下面的私有方法
* @param strings
*/
public static void createStyle(Workbook wb, FileOutputStream output) throws Exception{
Sheet sheet = wb.createSheet();
        Row row = sheet.createRow((short) 2);
        row.setHeightInPoints(30);


        setCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM);
        setCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM);
        setCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER);
        setCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER);
        setCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY);
        setCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP);
        setCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP);
        
        wb.write(output);
        print("设置样式成功");
}
 /**
     * Creates a cell and aligns it a certain way.
     *
     * @param wb     the workbook
     * @param row    the row to create the cell in
     * @param column the column number to create the cell in
     * @param halign the horizontal alignment for the cell.
     */
    private static void setCell(Workbook wb, Row row, short column, short halign, short valign) {
        Cell cell = row.createCell(column);
        cell.setCellValue("Align It");
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setAlignment(halign);
        cellStyle.setVerticalAlignment(valign);
        cell.setCellStyle(cellStyle);
    }
    /**
     * 边框的设置
     * @param strings
     */
    public static void createBorder(Workbook wb, FileOutputStream output) throws Exception{
    Sheet sheet = wb.createSheet("边框的设置");
    //对第二行第二列进行设置
    Row row = sheet.createRow(1);
    Cell cell  = row.createCell(1);
    cell.setCellValue(1);
   
    CellStyle style = wb.createCellStyle();
    style.setBorderBottom(CellStyle.BORDER_THIN);
    style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.RED.getIndex());
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLUE.getIndex());
        style.setBorderTop(CellStyle.BORDER_MEDIUM_DASHED);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(style);
        wb.write(output);
        
        print("设置边框成功");
    }
    /**
     * 读取整个excel表
     * @param strings
     */
    public static void readSheet(Workbook wb){
    Sheet sheet = wb.getSheetAt(0);
    for(Row row : sheet){
    for(Cell cell : row){
//     CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
//                System.out.print(cellRef.formatAsString());
//                System.out.print(" - ");


                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getRichStringCellValue().getString()+" ");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            System.out.print(cell.getDateCellValue()+" ");
                        } else {
                            System.out.print(cell.getNumericCellValue()+" ");
                        }
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue()+" ");
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        System.out.print(cell.getCellFormula()+" ");
                        break;
                    default:
                        System.out.println();
                }
    }
    System.out.println();
    }
    }
    
    
private static void print(String ...strings ){
System.out.println("output beginning");
for(String str : strings){
System.out.print(str+" ");
}
System.out.println();
System.out.println("output the end");
}

}