目前Java操作execl都采用poi,因为比较好用,下面就简单介绍一下:
所需jar包:
poi-3.8-20120326.jar
poi-excelant-3.8-20120326.jar
poi-ooxml-3.8-20120326.jar
poi-ooxml-schemas-3.8-20120326.jar
poi-scratchpad-3.8-20120326.jar
jar包下下载地址:http://download.csdn.net/download/qq_37791764/10173693
代码部分 主题:
int sheetNum=1;//工作薄sheet编号
String title ="数据";//excl标题
HSSFWorkbook workbook = new HSSFWorkbook();//创建excel
HSSFSheet sheet = workbook.createSheet(title+sheetNum);//创建一个工作薄
HSSFCellStyle style = workbook.createCellStyle();
String[] rowsName = NewExportExclUtil.getDetailHead() ;//获取明细列头
List<Object[]> dataList=NewExportExclUtil.getMasterData(billTitleInfo,rowsName);//获取主数据
NewExportExclUtil.getRowDetailHead(0, rowsName, sheet, NewExportExclUtil.getStyle(workbook,(short)11,HSSFFont.BOLDWEIGHT_BOLD));//放明细列头
NewExportExclUtil.getMaster(dataList,0,sheet,NewExportExclUtil.getMasterStyle(workbook,(short)12,HSSFFont.BOLDWEIGHT_BOLD));//放主数据
NewExportExclUtil.setRow(rowsName, sheet);
//放明细
NewExportExclUtil.getDetail(sheet, NewExportExclUtil.getStyle(workbook,(short)10,HSSFFont.BOLDWEIGHT_NORMAL),
goodsId, barcode,goodsName, cusChar2,purchaseCompany,pounits ,purchaseQuantity, purchasePrice,
purchaseCost, finishedGoodsQuantity, j,k);
workbook.write(new FileOutputStream("D:/TestNewExcelAll.xls"));
代码部分 工具类:
package com.maps.utils;
import java.util.ArrayList;
import java.util.List;
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.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import com.maps.main.entity.BillTitleInfo;
public class NewExportExclUtil {
public static final short EXCEL_COLUMN_WIDTH_FACTOR = 256;
public static final int UNIT_OFFSET_LENGTH = 7;
public static final int[] UNIT_OFFSET_MAP = new int[] { 0, 36, 73, 109, 146, 182, 219 };
/*
* 让列宽随着导出的列长自动适应
*/
public static void setRow(String[] rowName,HSSFSheet sheet){
for (int colNum = 0; colNum <=rowName.length; colNum++) {
for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
currentRow.setHeightInPoints(20);
}
if(colNum == 0){
sheet.setColumnWidth(colNum, pixel2WidthUnits(150));//设置宽度
}else{
sheet.setColumnWidth(colNum, pixel2WidthUnits(150));//设置宽度
}
}
}
/*
* 设置明细数据列头
*/
public static void getRowDetailHead(int row,String[] rowName,HSSFSheet sheet,HSSFCellStyle columnTopStyle){
int columnNum = rowName.length;
HSSFRow rowRowName = sheet.createRow(5+row);// 在索引2的位置创建行(最顶端的行开始的第二行)
for(int n=0;n<columnNum;n++){
HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
cellRowName.setCellValue(text);//设置列头单元格的值
cellRowName.setCellStyle(columnTopStyle);//设置列头单元格样式
}
}
// public static void getRowDetailHead(int row,String[] rowName,HSSFSheet sheet,HSSFCellStyle columnTopStyle){
// int columnNum = rowName.length;
// HSSFRow rowRowName = sheet.createRow(4+row);// 在索引2的位置创建行(最顶端的行开始的第二行)
// for(int n=0;n<columnNum;n++){
// HSSFCell cellRowName = rowRowName.createCell(n); //创建列头对应个数的单元格
// cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //设置列头单元格的数据类型
// HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
// cellRowName.setCellValue(text);//设置列头单元格的值
// cellRowName.setCellStyle(columnTopStyle);//设置列头单元格样式
// }
// }
/*
* 自适应宽度
*/
public static short pixel2WidthUnits(int pxs) {
short widthUnits = (short) (EXCEL_COLUMN_WIDTH_FACTOR * (pxs / UNIT_OFFSET_LENGTH));
widthUnits += UNIT_OFFSET_MAP[(pxs % UNIT_OFFSET_LENGTH)];
return widthUnits;
}
/*
* 把主数据放到excl里面
*/
public static void getMaster( List<Object[]> dataList1, int row1,HSSFSheet sheet,HSSFCellStyle Mastyerstyle){
//将查询出的主数据设置到sheet对应的单元格中
for(int i=0;i<dataList1.size();i++){
Object[] obj = dataList1.get(i);//遍历每个对象
HSSFRow row = sheet.createRow(i+row1);//创建所需的行数
for(int j=0; j<obj.length; j++){
HSSFCell cell = null; //设置单元格的数据类型
cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
if(!"".equals(obj[j]) && obj[j] != null){
cell.setCellValue(obj[j].toString());
}
cell.setCellStyle(Mastyerstyle); //设置单元格样式
}
}
CellMerger(row1,0, sheet);//合并单元格
}
/*
* 把明细数据放到excl里面
*/
public static void getDetail(HSSFSheet sheet,HSSFCellStyle style, String goodsId, String barcode, String goodsName,String cusChar2,
String purchaseCompany,String pounits,String purchaseQuantity ,String purchasePrice,String purchaseCost,
String finishedGoodsQuantity,int row1,int row2){
HSSFRow row = sheet.createRow(6+row1);//开始创建行数
HSSFCell cell = null; //设置单元格的数据类型
cell = row.createCell(0,HSSFCell.CELL_TYPE_NUMERIC);
cell.setCellValue(row2+1);//设置序号
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(1,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(goodsId);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(2,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(barcode);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(3,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(goodsName);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(4,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(cusChar2);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(5,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(purchaseCompany);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(6,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(pounits);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(7,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(purchaseQuantity);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(8,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(purchasePrice);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(9,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(purchaseCost);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
cell = row.createCell(10,HSSFCell.CELL_TYPE_STRING);
cell.setCellValue(finishedGoodsQuantity);//设置单元格的值
cell.setCellStyle(style); //设置单元格样式
}
/*
* 计算总和
*/
public static void setSum(HSSFSheet sheet,HSSFCellStyle style, double SumPurchaseCount ,double SumDeliveryCount,double SumCost ,int MaxCount,int maxRow){
HSSFRow Mrow = sheet.createRow(6+maxRow);//开始创建行数
HSSFCell Mcell = null; //设置单元格的数据类型
Mcell = Mrow.createCell(0,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue("计数:"+MaxCount);//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(7,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue("采购数量总计:"+SumPurchaseCount);//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(9,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue("采购金额总计:"+SumCost);//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(10,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue("已交货数量总计:"+SumDeliveryCount);//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
}
public static void setAllMoney(HSSFSheet sheet,HSSFCellStyle style, String vCountMoney ,String vTaxMoney,String vEndMoney ,int MaxCount,int maxRow){
HSSFRow Mrow = sheet.createRow(7+maxRow);//开始创建行数
HSSFCell Mcell = null; //设置单元格的数据类型
Mcell = Mrow.createCell(1,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue("含税总金额:");//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(2,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue(vCountMoney);//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(4,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue("总税额:");//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(5,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue(vTaxMoney);//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(8,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue("去税总金额:");//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
Mcell = Mrow.createCell(9,HSSFCell.CELL_TYPE_STRING);
Mcell.setCellValue(vEndMoney);//设置单元格的值
Mcell.setCellStyle(style); //设置单元格样式
}
/*
* 单元格样式
*/
public static HSSFCellStyle getStyle(HSSFWorkbook workbook ,short fontSize,short fontWeigth) {
//
// // 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints(fontSize);
//字体加粗
font.setBoldweight(fontWeigth);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐;
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
//设置单元格背景颜色
style.setFillBackgroundColor(HSSFColor.WHITE.index);
return style;
}
/*
* 设置主数据单元格样式
*/
public static HSSFCellStyle getMasterStyle(HSSFWorkbook workbook ,short fontSize,short fontWeigth) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints(fontSize);
//字体加粗
font.setBoldweight(fontWeigth);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框;
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色;
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框;
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色;
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框;
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色;
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框;
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色;
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体;
style.setFont(font);
//设置自动换行;
style.setWrapText(false);
//设置水平对齐的样式为居中对齐;
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// //设置垂直对齐的样式为居中对齐;
// style.setFillPattern(HSSFCellStyle.FINE_DOTS );
// style.setFillForegroundColor(new HSSFColor.BLUE().getIndex());
// style.setFillBackgroundColor(new HSSFColor.RED().getIndex());
// style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND );
// style.setFillForegroundColor(new HSSFColor.ROYAL_BLUE().getIndex());
//设置单元格背景颜色
style.setFillBackgroundColor(HSSFColor.WHITE.index);
return style;
}
/*
* 设置单元格合并:CellRangeAddress(起始行, 结束行, 起始列, 结束列)
*/
public static void CellMerger(int firstRow,int firstColumn,HSSFSheet sheet){
sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow, firstColumn, firstColumn+1));
sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow, firstColumn+2, firstColumn+4));
sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow, firstColumn+5, firstColumn+6));
sheet.addMergedRegion(new CellRangeAddress(firstRow, firstRow, firstColumn+7, firstColumn+10));
sheet.addMergedRegion(new CellRangeAddress(firstRow+1, firstRow+1, firstColumn, firstColumn+1));
sheet.addMergedRegion(new CellRangeAddress(firstRow+1, firstRow+1, firstColumn+2, firstColumn+4));
sheet.addMergedRegion(new CellRangeAddress(firstRow+1, firstRow+1, firstColumn+5, firstColumn+6));
sheet.addMergedRegion(new CellRangeAddress(firstRow+1, firstRow+1, firstColumn+7, firstColumn+10));
sheet.addMergedRegion(new CellRangeAddress(firstRow+2, firstRow+2, firstColumn, firstColumn+1));
sheet.addMergedRegion(new CellRangeAddress(firstRow+2, firstRow+2, firstColumn+2, firstColumn+4));
sheet.addMergedRegion(new CellRangeAddress(firstRow+2, firstRow+2, firstColumn+5, firstColumn+6));
sheet.addMergedRegion(new CellRangeAddress(firstRow+2, firstRow+2, firstColumn+7, firstColumn+10));
sheet.addMergedRegion(new CellRangeAddress(firstRow+3, firstRow+3, firstColumn, firstColumn+1));
sheet.addMergedRegion(new CellRangeAddress(firstRow+3, firstRow+3, firstColumn+2, firstColumn+4));
sheet.addMergedRegion(new CellRangeAddress(firstRow+3, firstRow+3, firstColumn+5, firstColumn+6));
sheet.addMergedRegion(new CellRangeAddress(firstRow+3, firstRow+3, firstColumn+7, firstColumn+10));
sheet.addMergedRegion(new CellRangeAddress(firstRow+4, firstRow+4, firstColumn, firstColumn+1));
sheet.addMergedRegion(new CellRangeAddress(firstRow+4, firstRow+4, firstColumn+2, firstColumn+10));
}
/*
* 明系列头 (自由定义)
*/
// public static String[] getDetailHead(){
// return new String[]{"序号","商品编号","商品名称","采购单位","采购数量","采购单价","采购金额","已交货数量"};//明细列头
// }
public static String[] getDetailHead(){
return new String[]{"序号","商品编号","条形码","商品名称","采购规格","采购单位","采购箱数","采购数量","采购单价","采购金额","已交货数量"};//明细列头
}
/*
* 获取主数据
*/
public static List<Object[]> getMasterData(BillTitleInfo billTitleInfo ,String[] rowsName){
List<Object[]> dataList1 = new ArrayList<Object[]>();//内容
Object[] objs1 = null;
Object[] objs2 = null;
Object[] objs3 = null;
Object[] objs4 = null;
Object[] objs5 = null;
objs1 = new Object[rowsName.length];
objs1[0] = "单据号:";
objs1[1] = " ";
objs1[2] =billTitleInfo.getPoId();
objs1[3] = " ";
objs1[4] = " ";
objs1[5] = "单据类型";
objs1[6] = " ";
objs1[7] = billTitleInfo.getBillType();
objs1[8] = " ";
objs1[9] = " ";
objs1[10]= " ";
dataList1.add(objs1);
objs2 = new Object[rowsName.length];
objs2[0] = "制单时间:";
objs2[1] = " ";
objs2[2] = billTitleInfo.getOrderDate();
objs2[3] = " ";
objs2[4] = " ";
objs2[5] = "期望收货日期:";
objs2[6] = " ";
objs2[7] = billTitleInfo.getExpectionDate();
objs2[8] = " ";
objs2[9] = " ";
objs2[10]= " ";
dataList1.add(objs2);
objs3 = new Object[rowsName.length];
objs3[0] = "送货地址:";
objs3[1] = " ";
objs3[2] = billTitleInfo.getDeliveryAddress();
objs3[3] = " ";
objs3[4] = " ";
objs3[5] = "联系电话:";
objs3[6] = " ";
objs3[7] = billTitleInfo.getTelephoneNum();
objs3[8] = " ";
objs3[9] = " ";
objs3[10]= " ";
dataList1.add(objs3);
objs4 = new Object[rowsName.length];
objs4[0] = "制单人:";
objs4[1] = " ";
objs4[2] = billTitleInfo.getvStaffName();
objs4[3] = " ";
objs4[4] = " ";
objs4[5] = "收货联系人:";
objs4[6] = " ";
objs4[7] = billTitleInfo.getvShopStaff();
objs4[8] = " ";
objs4[9] = " ";
objs4[10]= " ";
dataList1.add(objs4);
objs5 = new Object[rowsName.length];
objs5[0] = "备注:";
objs5[1] = " ";
objs5[2] = billTitleInfo.getIooMemo();
objs5[3] = " ";
objs5[4] = " ";
objs5[5] = " ";
objs5[6] = " ";
objs5[7] = " ";
objs5[8] = " ";
objs5[9] = " ";
objs5[10]= " ";
dataList1.add(objs5);
return dataList1;
}
}