java新建excel模板文件 java创建一个excel

转载

晨曦微露s 2023-08-23 15:56:00

文章标签 java新建excel模板文件 java apache System 文章分类 Java 后端开发

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;
 }

}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。