java建立excel入門

標籤：nis   class   bottom   wrap   換行   cep   ber   order   存在   

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實現輸出資訊到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("共建立多少樣式\t"+wb.getNumCellStyles());
System.out.println("共建立多少字型\t"+wb.getNumberOfFonts());

Sheet sheet = wb.getSheetAt(0);

System.out.println("共多少合併儲存格\t"+sheet.getNumMergedRegions());
System.out.println("起始行數\t"+sheet.getFirstRowNum());
System.out.println("結束行數\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:開啟excel檔案
Workbook wb = new XSSFWorkbook();//建立excel檔案
//Workbook wb = new XSSFWorkbook(new FileInputStream(xlsFile));//開啟已存在的excel檔案

//STEP 2:開啟當前活頁簿
Sheet sheet = wb.createSheet("我的第一個活頁簿");//建立新的sheet對象
//Sheet sheet = wb.getSheetAt(0);//選擇第一個活頁簿
//wb.setSheetName(0, "我的第一個活頁簿");//設定活頁簿的名稱

Row nRow = null;
Cell nCell = null;

//STEP 3:建立行對象
nRow = sheet.createRow((short)1);//第2行

//STEP 4:指定列 建立儲存格對象
nCell = nRow.createCell((short)(2));//第3列

//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();

}

@Test
public void testprint() throws Exception{
String xlsFile = "c:/clroleprice.xlsx";

Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("我的第一個活頁簿");

Row nRow = null;
Cell nCell = null;


for(int i=0;i<100000;i++){
System.out.println(i);
nRow = sheet.createRow(i);

for(int j=0;j<20;j++){
nCell = nRow.createCell(j);
nCell.setCellValue("我是儲存格傳智播客");
}
}

//STEP 6:設定樣式
nCell.setCellStyle(leftStyle(wb));

//STEP 7:關閉儲存excel檔案
FileOutputStream fOut = new FileOutputStream(xlsFile);
wb.write(fOut);
fOut.flush();
fOut.close();

}



//設定儲存格樣式
private CellStyle leftStyle(Workbook wb){
CellStyle curStyle = wb.createCellStyle();
Font curFont = wb.createFont();//設定字型
//curFont.setFontName("Times New Roman");//設定英文字型
curFont.setFontName("微軟雅黑");//設定英文字型
curFont.setCharSet(Font.DEFAULT_CHARSET);//設定中文字型，那必須還要再對儲存格進行編碼設定
curFont.setFontHeightInPoints((short)10);//字型大小
curStyle.setFont(curFont);

curStyle.setBorderTop(CellStyle.BORDER_THICK);//粗實線
curStyle.setBorderBottom(CellStyle.BORDER_THIN);//實線
curStyle.setBorderLeft(CellStyle.BORDER_MEDIUM);//比較粗實線
curStyle.setBorderRight(CellStyle.BORDER_THIN);//實線

curStyle.setWrapText(true); //換行
curStyle.setAlignment(CellStyle.ALIGN_RIGHT);//橫向具靠右對齊
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;
}

}

java建立excel入門