//拿来挨个案例运行一下,就明白创建和读取的基本过程了
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");
}
}