一個簡單的例子叮盘，實現(xiàn)一個沒有內容的workbook.xls文件
利用Maven引入相關的jar包
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
public class NewWorkbook{
publicstaticvoid main(String[] args)throws IOException{
HSSFWorkbook wb = new HSSFWorkbook();//建立新HSSFWorkbook對象
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);//把Workbook對象輸出到文件workbook.xls中
fileOut.close();
}
}
HSSF提供給用戶使用的對象在org.apache.poi.hssf.usermodel包中,主要部分包括Excell對象耿战，樣式和格式衅谷，還有輔助操作。有以下幾種對象：
HSSFWorkbook excell的文檔對象
HSSFSheet excell的表單
HSSFRow excell的行
HSSFCell excell的格子單元
HSSFFont excell字體
HSSFName 名稱
HSSFDataFormat 日期格式
在該基礎上對上面的文件進行設置樣式及添加內容
package jakarta;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class Excel {
private HSSFWorkbook workbook;
private HSSFSheet sheet;
private String bDate;
private int year;
/**
* 創(chuàng)建行元素
* @param style 樣式
* @param height 行高
* @param value 行顯示的內容
* @param row1 起始行
* @param row2 結束行
* @param col1 起始列
* @param col2 結束列
*/
private void createRow(HSSFCellStyle style, int height, String value, int row1, int row2, int col1, int col2){
sheet.addMergedRegion(new CellRangeAddress(row1, row2, col1, col2)); //設置從第row1行合并到第row2行矿微，第col1列合并到col2列
HSSFRow rows = sheet.createRow(row1); //設置第幾行
rows.setHeight((short) height); //設置行高
HSSFCell cell = rows.createCell(col1); //設置內容開始的列
cell.setCellStyle(style); //設置樣式
cell.setCellValue(value); //設置該行的值
}
/**
* 創(chuàng)建樣式
* @param fontSize 字體大小
* @param align 水平位置 左右居中2 居右3 默認居左 垂直均為居中
* @param bold 是否加粗
* @return
*/
private HSSFCellStyle getStyle(int fontSize,int align,boolean bold,boolean border){
HSSFFont font = workbook.createFont();
font.setFontName("宋體");
font.setFontHeightInPoints((short) fontSize);// 字體大小
if (bold){
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
HSSFCellStyle style = workbook.createCellStyle();
style.setFont(font); //設置字體
style.setAlignment((short) align); // 左右居中2 居右3 默認居左
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中1
if (border){
style.setBorderRight((short) 2);
style.setBorderLeft((short) 2);
style.setBorderBottom((short) 2);
style.setBorderTop((short) 2);
style.setLocked(true);
}
return style;
}
/**
* 根據(jù)數(shù)據(jù)集生成Excel座慰，并返回Excel文件流
* @param data 數(shù)據(jù)集
* @param sheetName Excel中sheet單元名稱
* @param headNames 列表頭名稱數(shù)組
* @param colKeys 列key,數(shù)據(jù)集根據(jù)該key進行按順序取值
* @return
* @throws IOException
*/
private InputStream getExcelFile(List<Map> data, String sheetName, String[] headNames,
String[] colKeys, int colWidths[], String bDate) throws IOException {
this.bDate = bDate;
workbook = new HSSFWorkbook();
sheet = workbook.createSheet(sheetName);
// 創(chuàng)建表頭 startRow代表表體開始的行
int startRow = createHeadCell( headNames, colWidths);
// 創(chuàng)建表體數(shù)據(jù)
HSSFCellStyle cellStyle = getStyle(14,2,false,true); // 建立新的cell樣式
setCellData(data, cellStyle, startRow, colKeys);
//創(chuàng)建表尾
createTailCell(data.size()+4,headNames.length);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
byte[] ba = baos.toByteArray();
ByteArrayInputStream bais = new ByteArrayInputStream(ba);
return bais;
}
/**
* 創(chuàng)建表頭
*
* @param headNames
* @param colWidths
*/
private int createHeadCell( String[] headNames, int colWidths[]) {
// 表頭標題
HSSFCellStyle titleStyle = getStyle(18,2,true,false);//樣式
createRow(titleStyle,0x549,"****學院統(tǒng)計報表",0,0,0,headNames.length-1);
//第二行
HSSFCellStyle unitStyle = getStyle(12,1,true,false);
createRow(unitStyle,0x190,"制作單位: ****學院ThoughtWorks工作室",1,1,0,headNames.length-1);
//第三行左邊部分
year = Integer.parseInt(bDate.substring(0,4));
String month = bDate.substring(4,6);
int m = Integer.parseInt(month)-1;
Calendar cal = Calendar.getInstance();
cal.set(Calendar.YEAR,year);
cal.set(Calendar.MONTH,m);//從0開始 0代表一月 11代表12月
int maxDate = cal.getActualMaximum(Calendar.DATE);
sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, 1));
HSSFRow row = sheet.createRow(2);
row.setHeight((short) 0x190);
HSSFCell cell = row.createCell(0);
cell.setCellStyle(getStyle(12,1,true,false));
cell.setCellValue("時間:"+year+"年"+month+"月"+"01日至"+year+"年"+month+"月"+maxDate+"日");
//第三行右邊部分
Date date = new Date();
SimpleDateFormat sdf=new SimpleDateFormat("yyyy年MM月dd日");
sheet.addMergedRegion(new CellRangeAddress(2, 2, 3, 5));
HSSFCell cell2 = row.createCell(3);
cell2.setCellStyle(getStyle(12,3,true,false));
cell2.setCellValue("制表時間: "+sdf.format(date));
//第四行表頭
boolean b = headNames.length > 0;
if (b) {
HSSFRow row2 = sheet.createRow(3);
row2.setHeight((short) 0x289);
HSSFCell fcell;
HSSFCellStyle cellStyle = getStyle(15,2,true,true); // 建立新的cell樣式
for (int i = 0; i < headNames.length; i++) {
fcell = row2.createCell(i);
fcell.setCellStyle(cellStyle);
fcell.setCellValue(headNames[i]);
if (colWidths != null && i < colWidths.length) {
sheet.setColumnWidth(i, 32 * colWidths[i]);
}
}
}
return b ? 4 : 3; //從哪一行開始渲染表體
}
/**
* 創(chuàng)建表體數(shù)據(jù)
* @param data 表體數(shù)據(jù)
* @param cellStyle 樣式
* @param startRow 開始行
* @param colKeys 值對應map的key
*/
private void setCellData(List<Map> data, HSSFCellStyle cellStyle, int startRow,
String[] colKeys) {
// 創(chuàng)建數(shù)據(jù)
HSSFRow row;
HSSFCell cell;
int i = startRow;
if (data != null && data.size() > 0) {
DecimalFormat df = new DecimalFormat("#0.00");
for (Map rowData : data) {
row = sheet.createRow(i);
row.setHeight((short) 0x279);
int j = 0;
for (String key : colKeys) {
Object colValue = rowData.get(key);
if (key.equalsIgnoreCase("CITYNAME")){
colValue = colValue+"XX科技有限公司";
}else if (key.equalsIgnoreCase("ORDERSUM")||key.equalsIgnoreCase("TRANSFEE")||key.equalsIgnoreCase("ORDREALSUM")){
colValue = df.format(colValue);
}
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
if (colValue != null) {
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(colValue.toString());
}
j++;
}
i++;
}
}
}
/**
* 創(chuàng)建表尾
* @param size
* @param length
*/
private void createTailCell(int size, int length) {
HSSFCellStyle remarkStyle1 = getStyle(11,1,false,false);
createRow(remarkStyle1,0x190,"經核對仑氛，確認以上數(shù)據(jù)真實無誤。",size,size,0,length-2);
HSSFCellStyle remarkStyle2 = getStyle(10,1,false,false);
createRow(remarkStyle2,0x160,"(聯(lián)系人：XXX搜贤；聯(lián)系電話：13XXXXXXXX谆沃；郵箱:123456789@qq.com)",size+1,size+1,0,length-2);
HSSFRow row3 = sheet.createRow(size+2);
row3.setHeight((short) 0x379);
sheet.addMergedRegion(new CellRangeAddress(size+3, size+3, 0, 1));
HSSFRow row4 = sheet.createRow(size+3);
row4.setHeight((short) 0x190);
HSSFCell cell4 = row4.createCell(0);
cell4.setCellStyle(getStyle(11,1,false,false));
cell4.setCellValue("單位核對人：");
sheet.addMergedRegion(new CellRangeAddress(size+3, size+3, 2, 4));
HSSFCell cell15 = row4.createCell(2);
cell15.setCellStyle(getStyle(11,1,false,false));
cell15.setCellValue("單位制表人：");
HSSFCellStyle dateStyle = getStyle(10,3,false,false);
createRow(dateStyle,0x150,"公司公章 ",size+8,size+8,0,length-2);
createRow(dateStyle,0x150,year+"年 月 日",size+9,size+9,0,length-2);
}
// 測試
public static void main(String[] args) throws IOException {
Excel excel = new Excel();
List<Map> data = new ArrayList<Map>();
LinkedHashMap<String, Object> e = new LinkedHashMap<String, Object>();
e.put("CITYNAME", "北京");
e.put("ORDERCOUNT", "65");
e.put("ORDERSUM", 930.38);
e.put("TRANSFEE", 2.28);
e.put("ORDREALSUM", 928.10);
e.put("REMARK", "通過1");
data.add(e);
e = new LinkedHashMap<String, Object>();
e.put("CITYNAME", "上海");
e.put("ORDERCOUNT", "50");
e.put("ORDERSUM", 850.34);
e.put("TRANSFEE", 2.08);
e.put("ORDREALSUM", 848.26);
e.put("REMARK", "通過2");
data.add(e);
e = new LinkedHashMap<String, Object>();
e.put("CITYNAME", "蘇州");
e.put("ORDERCOUNT", "10");
e.put("ORDERSUM", 112.20);
e.put("TRANSFEE", 2.20);
e.put("ORDREALSUM", 55.00);
e.put("REMARK", "通過3");
data.add(e);
e = new LinkedHashMap<String, Object>();
e.put("CITYNAME", "南京");
e.put("ORDERCOUNT", "26");
e.put("ORDERSUM", 210.12);
e.put("TRANSFEE", 0.51);
e.put("ORDREALSUM", 2409.61);
e.put("REMARK", "通過4");
data.add(e);
String[] headNames = { "單位名稱", "收入筆數(shù)", "收入金額", "手續(xù)費(2.45‰)", "實際金額","備注" };
String[] keys = { "CITYNAME", "ORDERCOUNT", "ORDERSUM","TRANSFEE","ORDREALSUM","REMARK"};
int colWidths[] = { 300, 200, 200, 200, 200,300 };
String bDate = "201708";
InputStream input = (excel.getExcelFile(data, "單位", headNames, keys, colWidths,bDate));
File f = new File("/home/admin-m/桌面/demo/demo.xls");
if (f.exists())
f.delete();
f.createNewFile();
FileOutputStream out = new FileOutputStream(f);
HSSFWorkbook book = new HSSFWorkbook(input);
book.write(out);
out.flush();
out.close();
}
}
