package com.shlanbao.tzsc.utils.excel;
|
|
import java.io.File;
|
import java.io.FileNotFoundException;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.io.OutputStream;
|
import java.math.BigDecimal;
|
import java.text.SimpleDateFormat;
|
import java.util.Calendar;
|
import java.util.Date;
|
import java.util.List;
|
|
import org.apache.poi.hssf.usermodel.HSSFCell;
|
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
|
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
|
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.apache.poi.hssf.util.HSSFColor;
|
import org.apache.poi.hssf.util.Region;
|
|
import com.shlanbao.tzsc.utils.tools.MathUtil;
|
|
|
public class ExcelWriter{
|
|
// 设置cell编码解决中文高位字节截断
|
private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;
|
// 定制浮点数格式
|
private static String NUMBER_FORMAT = "#,##0.00";
|
// 定制日期格式
|
private static String DATE_FORMAT = "m/d/yy"; // "m/d/yy h:mm"
|
private OutputStream out = null;
|
private HSSFWorkbook workbook = new HSSFWorkbook();
|
private HSSFSheet sheet = workbook.createSheet();
|
private HSSFRow row = null;
|
private HSSFCellStyle style=null;
|
|
|
public ExcelWriter() {
|
}
|
|
//初始化excel
|
public ExcelWriter(OutputStream out) {
|
this.out = out;
|
//this.workbook = new HSSFWorkbook();
|
//this.sheet = workbook.createSheet();
|
|
//表格样式
|
this.style=workbook.createCellStyle();
|
|
//边框
|
style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
|
style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
|
style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
|
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
|
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 指定单元格居中对齐
|
|
//设置字体
|
HSSFFont f = workbook.createFont();
|
f.setFontHeightInPoints((short)12);
|
f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
|
style.setFont(f);
|
|
|
//设置列宽
|
sheet.setColumnWidth((short)0, (short)4000);
|
sheet.setColumnWidth((short)1, (short)3000);
|
sheet.setColumnWidth((short)2, (short)6000);
|
sheet.setColumnWidth((short)3, (short)6000);
|
sheet.setColumnWidth((short)4, (short)6000);
|
sheet.setColumnWidth((short)5, (short)6000);
|
//sheet.setColumnWidth((short)5, (short)6000);
|
|
// 表头表格样式
|
HSSFCellStyle style1=workbook.createCellStyle();
|
style1.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
|
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 指定单元格居中对齐
|
HSSFFont f1 = workbook.createFont();
|
f1.setFontHeightInPoints((short)6);
|
f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
|
style1.setFont(f1);// 设置字体
|
}
|
|
|
//导出Excel文件
|
public void export() throws FileNotFoundException, IOException {
|
try {
|
workbook.write(out);
|
out.flush();
|
out.close();
|
} catch (FileNotFoundException e) {
|
throw new IOException(" 生成导出Excel文件出错! ", e);
|
} catch (IOException e) {
|
throw new IOException(" 写入Excel文件出错! ", e);
|
}
|
}
|
|
//增加一行index表示行号
|
public void createRow(int index) {
|
this.row = this.sheet.createRow(index);
|
}
|
|
|
//获取单元格的值index列好
|
public String getCell(int index) {
|
HSSFCell cell = this.row.getCell((short) index);
|
String strExcelCell = "";
|
if (cell != null) { // add this condition
|
// judge
|
switch (cell.getCellType()) {
|
case HSSFCell.CELL_TYPE_FORMULA:
|
strExcelCell = "FORMULA ";
|
break;
|
case HSSFCell.CELL_TYPE_NUMERIC: {
|
strExcelCell = String.valueOf(cell.getNumericCellValue());
|
}
|
break;
|
case HSSFCell.CELL_TYPE_STRING:
|
strExcelCell = cell.getStringCellValue();
|
break;
|
case HSSFCell.CELL_TYPE_BLANK:
|
strExcelCell = "";
|
break;
|
default:
|
strExcelCell = "";
|
break;
|
}
|
}
|
return strExcelCell;
|
}
|
/**
|
* 设置单元格 index列号 value单元格填充值
|
* 日期类型
|
*
|
* */
|
public void setDate(int index,Date value){
|
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
|
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
|
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
|
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
|
|
HSSFCell cell = this.row.createCell((short) index);
|
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
|
if(value != null){
|
cell.setCellValue(simpleDateFormat.format(value));
|
}else{
|
cell.setCellValue("");
|
}
|
cell.setCellStyle(cellStyle);
|
}
|
/**
|
* 设置单元格 index列号 value单元格填充值
|
* int类型
|
* */
|
public void setCell(int index, int value) {
|
HSSFCell cell = this.row.createCell((short) index);
|
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
|
cell.setCellType(HSSFCellStyle.ALIGN_LEFT);
|
cell.setCellValue(value);
|
}
|
/**
|
* 设置单元格 index列号 value单元格填充值
|
* double类型
|
*
|
* */
|
public void setCell(int index, double value) {
|
HSSFCell cell = this.row.createCell((short) index);
|
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
|
cell.setCellValue(value);
|
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
|
HSSFDataFormat format = workbook.createDataFormat();
|
cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
|
cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
|
}
|
/**
|
* 设置单元格 index列号 value单元格填充值
|
* String类型
|
*
|
* */
|
public void setCell(int index, String value) {
|
HSSFCell cell = this.row.createCell((short) index);
|
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
|
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
|
cell.setCellValue(XLS_ENCODING);
|
cell.setCellValue(value);
|
}
|
|
public void setCell(int index, Calendar value) {
|
HSSFCell cell = this.row.createCell((short) index);
|
cell.setCellValue(XLS_ENCODING);
|
cell.setCellValue(value.getTime());
|
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
|
cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
|
cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
|
}
|
|
/**
|
* 第一列新样式标题
|
*
|
* */
|
//设置单元格 index列号 value单元格填充值
|
public void setCellTitle(int index, String value) {
|
sheet.addMergedRegion(new Region(0,(short)0,0,(short)5));
|
HSSFCell cell = this.row.createCell((short) index);
|
//生成一个字体
|
HSSFFont font1=workbook.createFont();
|
font1.setColor(HSSFColor.BLACK.index);
|
font1.setFontHeightInPoints((short)12);
|
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
|
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
|
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
|
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
|
//把字体应用到当前的样式
|
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
|
cellStyle.setFont(font1);
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(value);
|
}
|
|
/**
|
* 第一列新值
|
* 不能合并单元格
|
*
|
* */
|
//设置单元格 index列号 value单元格填充值
|
public void setCellFirst(int index, String value) {
|
//sheet.addMergedRegion(new Region(rowcoo,(short)cumcoo,rowspan,(short)cumspan));
|
HSSFCell cell = this.row.createCell((short) index);
|
//生成一个字体
|
HSSFFont font1=workbook.createFont();
|
font1.setColor(HSSFColor.BLACK.index);
|
font1.setFontHeightInPoints((short)10);
|
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
|
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
|
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
|
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
|
//把字体应用到当前的样式
|
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
|
cellStyle.setFont(font1);
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(value);
|
}
|
|
//循环遍历封装值
|
public void ExcelWriter(List<Object[]> dg,String np){
|
File f = new File(np);
|
ExcelWriter e = new ExcelWriter();
|
try {
|
e = new ExcelWriter(new FileOutputStream(f));
|
} catch (FileNotFoundException e1) {
|
e1.printStackTrace();
|
}
|
int y=0;
|
e.createRow(0);
|
e.setCellTitle(0,"设备有效作业率" );
|
e.createRow(1);
|
e.setCellFirst(0,"设备名称" );
|
e.setCellFirst(1, "班次");
|
e.setCellFirst(2, "总时间");
|
e.setCellFirst(3,"实际产量");
|
e.setCellFirst(4, "车间实际产量");
|
e.setCellFirst(5,"有效作业率" );
|
//e.setCellFirst(6, "实际有效作业率");
|
|
for(int i=0;i<dg.size();i++){
|
Object[] w=(Object[])dg.get(i);
|
e.createRow(i+2);
|
y++;
|
if(y>0){
|
y--;
|
try {
|
e.setCell(y, w[0].toString());
|
e.setCell(1, w[2].toString());
|
e.setCell(y+2, w[4].toString());
|
e.setCell(y+3, w[5].toString());
|
e.setCell(y+4, w[3].toString());
|
e.setCell(y+5, this.getEfic(w[3].toString(), w[4].toString()));
|
//e.setCell(y+6, "");
|
} catch (Exception e1) {
|
// TODO: handle exception
|
}
|
}
|
}
|
|
try {
|
e.export();
|
} catch (IOException ex) {
|
ex.printStackTrace();
|
}
|
|
}
|
|
|
private String getEfic(String qty,String r_qty){
|
if(Float.parseFloat(r_qty)==0){
|
return "0";
|
}else{
|
BigDecimal a1=new BigDecimal(Double.parseDouble(qty));
|
BigDecimal b1=new BigDecimal(Double.parseDouble(r_qty));
|
Double st=MathUtil.roundHalfUp((a1.divide(b1,4, BigDecimal.ROUND_HALF_EVEN).doubleValue()*100), 2);
|
if(st>120){
|
st=96.1; //有效作业率大于100，给出默认值90.1，防止数据错误，导致误差太大(测试数据，误差大正常)；wch 2015年10月13日
|
}
|
if(st==0){
|
return "0";
|
}
|
return st.toString();
|
}
|
}
|
|
|
|
/**
|
* 张璐-2015.10.14
|
* 设置列，带合并单元格的
|
* @param index
|
* @param value
|
* @param rowcoo:行坐标
|
* @param cumcoo:列坐标
|
* @param rowspan:跨行
|
* @param cumspan:跨列
|
*/
|
//设置单元格 index列号 value单元格填充值
|
public void setCellFirstSpan(int index, String value,int rowcoo,int cumcoo,int rowspan,int cumspan) {
|
sheet.addMergedRegion(new Region(rowcoo,(short)cumcoo,rowspan,(short)cumspan));
|
HSSFCell cell = this.row.createCell((short) index);
|
//生成一个字体
|
HSSFFont font1=workbook.createFont();
|
font1.setColor(HSSFColor.BLACK.index);
|
font1.setFontHeightInPoints((short)10);
|
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
|
HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
|
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直
|
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平
|
//把字体应用到当前的样式
|
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
|
cellStyle.setFont(font1);
|
cell.setCellStyle(cellStyle);
|
cell.setCellValue(value);
|
}
|
|
}
|
