java創(chuàng)建excel入門(mén)

package poi;

import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;

import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.Font;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.xssf.usermodel.XSSFWorkbook;import org.junit.Test;

/** * @Description: poi實(shí)現(xiàn)輸出信息到excel文件 * @Author: nutony * @Date: 2013-12-14 */public class Test2XSSF { @Test public void rule() throws FileNotFoundException, IOException{ String xlsFile = "c:/poiXFFS.xlsx"; Workbook wb = new XSSFWorkbook(new FileInputStream(xlsFile)); System.out.println("共創(chuàng)建多少樣式\t"+wb.getNumCellStyles()); System.out.println("共創(chuàng)建多少字體\t"+wb.getNumberOfFonts()); Sheet sheet = wb.getSheetAt(0); System.out.println("共多少合并單元格\t"+sheet.getNumMergedRegions()); System.out.println("起始行數(shù)\t"+sheet.getFirstRowNum()); System.out.println("結(jié)束行數(shù)\t"+sheet.getLastRowNum()+1); } /* * dom4j-1.6.1.jar * poi-3.9-20121203.jar * poi-ooxml-3.9-20121203.jar * poi-ooxml-schemas-3.9-20121203.jar * stax-api-1.0.1.jar * xmlbeans-2.3.0.jar */ @Test public void print() throws Exception{ String xlsFile = "c:/clroleprice.xlsx"; //STEP 1:打開(kāi)excel文件 Workbook wb = new XSSFWorkbook(); //創(chuàng)建excel文件 //Workbook wb = new XSSFWorkbook(new FileInputStream(xlsFile)); //打開(kāi)已存在的excel文件

//STEP 2:打開(kāi)當(dāng)前工作簿 Sheet sheet = wb.createSheet("我的第一個(gè)工作簿"); //建立新的sheet對(duì)象 //Sheet sheet = wb.getSheetAt(0); //選擇第一個(gè)工作簿 //wb.setSheetName(0, "我的第一個(gè)工作簿"); //設(shè)置工作簿的名稱(chēng)

Row nRow = null; Cell nCell = null; //STEP 3:創(chuàng)建行對(duì)象 nRow = sheet.createRow((short)1); //第2行

//STEP 4:指定列 創(chuàng)建單元格對(duì)象 nCell = nRow.createCell((short)(2)); //第3列 //STEP 5:指定列 創(chuàng)建單元格對(duì)象 nCell.setCellValue("我是單元格傳智播客"); //STEP 6:設(shè)置樣式 nCell.setCellStyle(leftStyle(wb));

//STEP 7:關(guān)閉保存excel文件 FileOutputStream fOut = new FileOutputStream(xlsFile); wb.write(fOut); fOut.flush(); fOut.close();

} @Test public void testprint() throws Exception{ String xlsFile = "c:/clroleprice.xlsx"; Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("我的第一個(gè)工作簿"); Row nRow = null; Cell nCell = null; for(int i=0;i nCell = nRow.createCell(j); nCell.setCellValue("我是單元格傳智播客"); } } //STEP 6:設(shè)置樣式 nCell.setCellStyle(leftStyle(wb)); //STEP 7:關(guān)閉保存excel文件 FileOutputStream fOut = new FileOutputStream(xlsFile); wb.write(fOut); fOut.flush(); fOut.close(); } //設(shè)置單元格樣式 private CellStyle leftStyle(Workbook wb){ CellStyle curStyle = wb.createCellStyle(); Font curFont = wb.createFont(); //設(shè)置字體 //curFont.setFontName("Times New Roman"); //設(shè)置英文字體 curFont.setFontName("微軟雅黑"); //設(shè)置英文字體 curFont.setCharSet(Font.DEFAULT_CHARSET); //設(shè)置中文字體，那必須還要再對(duì)單元格進(jìn)行編碼設(shè)置 curFont.setFontHeightInPoints((short)10); //字體大小 curStyle.setFont(curFont); curStyle.setBorderTop(CellStyle.BORDER_THICK); //粗實(shí)線 curStyle.setBorderBottom(CellStyle.BORDER_THIN); //實(shí)線 curStyle.setBorderLeft(CellStyle.BORDER_MEDIUM); //比較粗實(shí)線 curStyle.setBorderRight(CellStyle.BORDER_THIN); //實(shí)線 curStyle.setWrapText(true); //換行 curStyle.setAlignment(CellStyle.ALIGN_RIGHT); //橫向具右對(duì)齊 curStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //單元格垂直居中 return curStyle; }}

?

?

package poi;

import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.junit.Test;

/** * @Description: poi瀹炵幇杈撳嚭淇℃伅鍒癳xcel鏂囦歡 * @Author: nutony * @Date: 2013-05-15 */public class Test1HFFS { @Test public void rule() throws FileNotFoundException, IOException{ String xlsFile = "c:/poiHFFS.xls"; HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(xlsFile)); System.out.println("鍏卞垱寤哄灝戞牱寮廫t"+wb.getNumCellStyles()); System.out.println("鍏卞垱寤哄灝戝瓧浣揬t"+wb.getNumberOfFonts()); HSSFSheet sheet = wb.getSheetAt(0); System.out.println("鍏卞灝戝悎騫跺崟鍏冩牸\t"+sheet.getNumMergedRegions()); System.out.println("璧峰琛屾暟\t"+sheet.getFirstRowNum()); System.out.println("緇撴潫琛屾暟\t"+sheet.getLastRowNum()+1); } @Test public void print() throws Exception{ String xlsFile = "c:/poiHFFS.xls"; //STEP 1:鎵撳紑excel鏂囦歡 HSSFWorkbook wb = new HSSFWorkbook(); //鍒涘緩excel鏂囦歡 //HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(xlsFile)); //鎵撳紑宸插瓨鍦ㄧ殑excel鏂囦歡

//STEP 2:鎵撳紑褰撳墠宸ヤ綔綈? HSSFSheet sheet = wb.createSheet("鎴戠殑絎竴涓伐浣滅翱"); //寤虹珛鏂扮殑sheet瀵硅薄 //HSSFSheet sheet = wb.getSheetAt(0); //閫夋嫨絎竴涓伐浣滅翱 //wb.setSheetName(0, "鎴戠殑絎竴涓伐浣滅翱"); //璁劇疆宸ヤ綔綈跨殑鍚嶇О

HSSFRow nRow = null; HSSFCell nCell = null; //STEP 3:鍒涘緩琛屽璞? nRow = sheet.createRow((short)1); //絎?琛?

//STEP 4:鎸囧畾鍒?鍒涘緩鍗曞厓鏍煎璞? nCell = nRow.createCell((short)(2)); //絎?鍒? //STEP 5:鎸囧畾鍒?鍒涘緩鍗曞厓鏍煎璞? nCell.setCellValue("鎴戞槸鍗曞厓鏍?); //STEP 6:璁劇疆鏍峰紡 nCell.setCellStyle(leftStyle(wb));

//STEP 7:鍏抽棴淇濆瓨excel鏂囦歡 FileOutputStream fOut = new FileOutputStream(xlsFile); wb.write(fOut); fOut.flush(); fOut.close();

System.out.println("finish."); } //璁劇疆鍗曞厓鏍兼牱寮? private HSSFCellStyle leftStyle(HSSFWorkbook wb){ HSSFCellStyle curStyle = wb.createCellStyle(); HSSFFont curFont = wb.createFont(); //璁劇疆瀛椾綋 //curFont.setFontName("Times New Roman"); //璁劇疆鑻辨枃瀛椾綋 curFont.setFontName("寰蔣闆呴粦"); //璁劇疆鑻辨枃瀛椾綋 curFont.setCharSet(HSSFFont.DEFAULT_CHARSET); //璁劇疆涓枃瀛椾綋錛岄偅蹇呴』榪樿鍐嶅鍗曞厓鏍艱繘琛岀紪鐮佽緗? curFont.setFontHeightInPoints((short)10); //瀛椾綋澶у皬 curStyle.setFont(curFont); curStyle.setBorderTop(HSSFCellStyle.BORDER_THICK); //綺楀疄綰? curStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //瀹炵嚎 curStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); //姣旇緝綺楀疄綰? curStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); //瀹炵嚎 curStyle.setWrapText(true); //鎹㈣ curStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //妯悜鍏峰彸瀵歸綈 curStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //鍗曞厓鏍煎瀭鐩村眳涓? return curStyle; }}