java poi excel操作示例
[java]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 ([) 补充：软件开发 , Java ,
