使用poi3.9版本使用的示例
参考http://poi.apache.org/spreadsheet/quick-guide.html
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFHeader;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Header;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.RichTextString;
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.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//http://poi.apache.org/spreadsheet/quick-guide.html
public class ExcelParseUtil {
public static void test() throws IOException {
Workbook wb = new HSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream(
"E:\\logs\\workbook.xls");
Sheet sheet1 = wb.createSheet("new sheet");
Sheet sheet2 = wb.createSheet("second sheet");
// 设置sheet的标题
Header header = sheet1.getHeader();
header.setCenter("Center Header");
header.setLeft("Left Header");
header.setRight(HSSFHeader.font("Stencil-Normal", "Italic")
+ HSSFHeader.fontSize((short) 16)
+ "Right w/ Stencil-Normal Italic font and size 16");
// 合并单元格
sheet1.groupRow(5, 14);
sheet1.groupRow(7, 14);
sheet1.groupRow(16, 19);
sheet1.groupColumn((short) 4, (short) 7);
sheet1.groupColumn((short) 9, (short) 12);
sheet1.groupColumn((short) 10, (short) 11);
sheet1.addMergedRegion(new CellRangeAddress(1, // first row (0-based)
1, // last row (0-based)
1, // first column (0-based)
2 // last column (0-based)
));
// 设置图片
// Create the drawing patriarch. This is the top level container for all
// shapes.
// Drawing drawing = sheet1.createDrawingPatriarch();
//
// //add a picture shape
// ClientAnchor anchor = helper.createClientAnchor();
// //set top-left corner of the picture,
// //subsequent call of Picture#resize() will operate relative to it
// anchor.setCol1(3);
// anchor.setRow1(2);
// Picture pict = drawing.createPicture(anchor, pictureIdx);
//
// //auto-size picture relative to its top-left corner
// pict.resize();
String sname = "TestSheet", cname = "TestName", cvalue = "TestVal";
Name namedCell = wb.createName();
namedCell.setNameName(cname);
String reference = sname + "!A1:A1"; // area reference
namedCell.setRefersToFormula(reference);
// 2. create named range for a single cell using cellreference
// Name namedCel2 = wb.createName();
// namedCel2.setNameName(cname);
// reference = sname+"!A1"; // cell reference
// namedCel2.setRefersToFormula(reference);
//
// // 3. create named range for an area using AreaReference
// Name namedCel3 = wb.createName();
// namedCel3.setNameName(cname);
// reference = sname+"!A1:C5"; // area reference
// namedCel3.setRefersToFormula(reference);
//
// // 4. create named formula
// Name namedCel4 = wb.createName();
// namedCel4.setNameName("my_sum");
// namedCel4.setRefersToFormula("SUM(sname+!$I$2:$I$6)");
// Note that sheet name is Excel must not exceed 31 characters
// and must not contain any of the any of the following characters:
// 0x0000
// 0x0003
// colon (:)
// backslash (\)
// asterisk (*)
// question mark (?)
// forward slash (/)
// opening square bracket ([)
// closing square bracket (])
// You can use
// org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String
// nameProposal)}
// for a safe way to create valid names, this utility replaces invalid
// characters with a space (' ')"
//设置单元格值
// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet1.createRow((short) 0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);
// Or do it on one line.
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue("This is a string");
row.createCell(3).setCellValue(true);
Row row2 = sheet2.createRow(0);
// Create a cell and put a date value in it. The first cell is not
// styled
// as a date.
Cell cell2 = row2.createCell(0);
cell2.setCellValue(new Date());
// we style the second cell as a date (and time). It is important to
// create a new cell style from the workbook otherwise you can end up
// modifying the built in style and effecting not only this cell but
// other cells.
//设置单元格日期格式
CellStyle cellStyle = wb.createCellStyle();
DataFormat format = wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat("yyyy/mm/dd"));
// style.setDataFormat(format.getFormat("#,##0.0000"));
//设置单元格字体
Font font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
cellStyle.setWrapText(true);
cell2 = row2.createCell(1);
cell2.setCellValue(new Date());
cell2.setCellStyle(cellStyle);
// you can also set date as java.util.Calendar
cell2 = row2.createCell(2);
cell2.setCellValue(Calendar.getInstance());
cell2.setCellStyle(cellStyle);
// 註釋
// Create the comment and set the text+author
CreationHelper factory = wb.getCreationHelper();
// When the comment box is visible, have it show in a 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 3);
Drawing drawing = sheet1.createDrawingPatriarch();
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString("Hello, World!");
comment.setString(str);
comment.setAuthor("Apache POI");
// Assign the comment to the cell
cell.setCellComment(comment);
// Hyperlink link = createHelper.createHyperlink(Hyperlink.LINK_URL);
// link.setAddress("http://poi.apache.org/");
// cell.setHyperlink(link);
// cell.setCellStyle(cellStyle);
Row row3 = sheet2.createRow((short) 2);
row3.createCell(0).setCellValue(1.1);
row3.createCell(1).setCellValue(new Date());
row3.createCell(2).setCellValue(Calendar.getInstance());
row3.createCell(3).setCellValue("a string");
row3.createCell(4).setCellValue(true);
row3.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);
//获取sheet的每个单元格的值
Sheet sheet = wb.getSheetAt(0);
for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
Row row4 = rit.next();
for (Iterator<Cell> cit = row4.cellIterator(); cit.hasNext();) {
Cell cell4 = cit.next();
// do something here
}
}
Sheet sheet5 = wb.getSheetAt(1);
for (Row row5 : sheet5) {
for (Cell cell5 : row5) {
CellReference cellRef = new CellReference(row5.getRowNum(),
cell5.getColumnIndex());
System.out.print(cellRef.formatAsString());
System.out.print(" - ");
switch (cell5.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell5.getRichStringCellValue()
.getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell5)) {
System.out.println(cell5.getDateCellValue());
} else {
System.out.println(cell5.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell5.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell5.getCellFormula());
break;
default:
System.out.println();
}
}
}
wb.write(fileOut);
fileOut.close();
}
public void test2() throws IOException {
Workbook wb = new XSSFWorkbook();
FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");
wb.write(fileOut);
fileOut.close();
}
public static void main(String[] args) throws IOException {
test();
}
}
输出:
A1 - 41258.65454309028
B1 - Sat Dec 15 15:42:32 CST 2012
C1 - Sat Dec 15 15:42:32 CST 2012
A3 - 1.1
B3 - 41258.65454322917
C3 - 41258.65454322917
D3 - a string
E3 - true
F3 -