package org.noures.framework.modules.util.ExcelUtil;
import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ExcelUtilJxl_eg {
public static void exportsShiFeiExcel(String doc_path, String now) throws BiffException, IOException, WriteException {
String middPersonfile = doc_path + "/1.xls";
File file = new File(middPersonfile);
WritableWorkbook rwb = Workbook.createWorkbook(file); // 写入文件流
WritableSheet ws = rwb.createSheet("机检废品核查信息统计表(最终实废)", 0); // 工作表名
/**
* 设置字体
*/
jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont
.createFont("黑体"), 10, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
/**
* 启用字体样式
*/
jxl.write.WritableCellFormat wcfFCs = new jxl.write.WritableCellFormat(
wfc);
wcfFCs.setVerticalAlignment(VerticalAlignment.TOP); // 垂直对齐
wcfFCs.setAlignment(Alignment.LEFT);// 数据的对齐方式
wcfFCs.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
wcfFCs.setWrap(true); // 是否换行
/**
* 启用字体样式
*/
jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(
wfc);
wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE); // 垂直对齐
wcfFC.setAlignment(Alignment.CENTRE);// 数据的对齐方式
wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN); // 设置边框线
wcfFC.setWrap(true); // 是否换行
/*
* 合并单元格
* 通过writablesheet.mergeCells(int x,int y,int m,int n);来实现的
* 表示将从第x+1列，y+1行到m+1列，n+1行合并
* */
ws.mergeCells(0, 0,7, 0);// 合并一行的单元格(从0到10)
Label titleLable = new Label(0, 0, "机检废品核查信息统计表(最终实废)",wcfFC); // 加入标题
ws.addCell(titleLable);
// 设置第二行 合并单元格
ws.mergeCells(0, 1, 2, 1); //合并三个单元格
titleLable = new Label(0, 1, "编号:",wcfFC);
ws.addCell(titleLable);
ws.mergeCells(3, 1, 5, 1); //合并三个单元格
titleLable = new Label(3, 1, "时间:"+now,wcfFC);
ws.addCell(titleLable);
ws.mergeCells(6, 1, 7, 13 ); //合并三个单元格
titleLable = new Label(6, 1, "备注:",wcfFCs);
ws.addCell(titleLable);
// 设置第三行 合并单元格
ws.mergeCells(0, 2, 2, 2); //合并三个单元格
titleLable = new Label(0, 2, "车号:",wcfFC);
ws.addCell(titleLable);
ws.mergeCells(3, 2, 5, 2); //合并三个单元格
titleLable = new Label(3, 2, "冠字号:",wcfFC);
ws.addCell(titleLable);
// 设置第四行 合并单元格
ws.mergeCells(0, 3, 1, 3); //合并2个单元格
titleLable = new Label(0, 3, "工序",wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 3, "判废人A",wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 3, "判废人B",wcfFC);
ws.addCell(titleLable);
ws.mergeCells(4, 3, 5, 3); //合并2个单元格
titleLable = new Label(4, 3, "实废数量",wcfFC);
ws.addCell(titleLable);
// 设置第5行 合并单元格 j1count + j2count + wcount + ycount
ws.mergeCells(0, 4, 1, 4); //合并2个单元格
titleLable = new Label(0, 4, "胶印一印",wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 4, "判废人A",wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 4, "判废人B",wcfFC);
ws.addCell(titleLable);
ws.mergeCells(4, 4, 5, 4); //合并2个单元格
titleLable = new Label(4, 4, "22",wcfFC);
ws.addCell(titleLable);
// 设置第6行 合并单元格
ws.mergeCells(0, 5, 1, 5); //合并2个单元格
titleLable = new Label(0, 5, "废品总计",wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 5, "22",wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 5, "检查人",wcfFC);
ws.addCell(titleLable);
ws.mergeCells(4, 5, 5, 5); //合并2个单元格
titleLable = new Label(4, 5, "",wcfFC);
ws.addCell(titleLable);
// 设置第7行 合并单元格
titleLable = new Label(0, 6, "千位号", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(1, 6, "0|5", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 6, "1|6", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 6, "2|7", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(4, 6, "3|8", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(5, 6, "4|9", wcfFC);
ws.addCell(titleLable);
// 设置第8行 合并单元格
titleLable = new Label(0, 7, "实废数", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(1, 7, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 7, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 7, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(4, 7, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(5, 7, "", wcfFC);
ws.addCell(titleLable);
// 设置第9行 合并单元格
titleLable = new Label(0, 8, "机漏数", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(1, 8, "", wcfFCs);
ws.addCell(titleLable);
titleLable = new Label(2, 8, "", wcfFCs);
ws.addCell(titleLable);
titleLable = new Label(3, 8, "", wcfFCs);
ws.addCell(titleLable);
titleLable = new Label(4, 8, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(5, 8, "", wcfFC);
ws.addCell(titleLable);
// 设置第10行 合并单元格
titleLable = new Label(0, 9, "未检数", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(1, 9, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 9, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 9, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(4, 9, "", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(5, 9, "", wcfFC);
ws.addCell(titleLable);
// 设置第11行 合并单元格
titleLable = new Label(0, 10, "喷码号", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(1, 10, "开位号", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 10, "千位", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 10, "百位号", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(4, 10, "正背面", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(5, 10, "核查", wcfFC);
ws.addCell(titleLable);
// 设置第12行 合并单元格循环
titleLable = new Label(0, 11, "0321", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(1, 11, "第1开", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(2, 11, "第5千", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(3, 11, "009", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(4, 11, "背面", wcfFC);
ws.addCell(titleLable);
titleLable = new Label(5, 11, "", wcfFC);
ws.addCell(titleLable);
// 设置第13行 合并单元格循环
ws.mergeCells(0, 12, 0, 13 );
titleLable = new Label(0, 12, "漏检", wcfFC);
ws.addCell(titleLable);
ws.mergeCells(1, 12, 5, 13 );
titleLable = new Label(1, 12, "", wcfFC);
ws.addCell(titleLable);
rwb.write();
// 关闭可写入的Excel对象
rwb.close();
}
public static void main(String args[]) throws Exception {
ExcelUtilJxl_eg.exportsShiFeiExcel( "D:\\", "2012-02-17 10:03:01");
}
}package org.noures.framework.modules.util.ExcelUtil;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.DVConstraint;
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.HSSFDataValidation;
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.CellRangeAddressList;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.DataFormat;
public class ExcelUtilPoi {
public HSSFWorkbook wb = null;
public HSSFSheet sheet = null;
public HSSFDataFormat format = null;
public HSSFRow hdRow = null;
int listlength = 0;
/**
* 设置工作表的格式
*
* @param sheetName
*/
public ExcelUtilPoi() {
wb = new HSSFWorkbook();
}
public void createSheet(String sheetName) {
sheet = wb.createSheet(sheetName);
format = wb.createDataFormat();
hdRow = sheet.createRow(0);
sheet.setDefaultRowHeightInPoints(120);
sheet.setDefaultColumnWidth(12);
}
/* 设置各列单元格宽度 */
public void setDefaultCellHighWidthInRange(short[] eachCellWidth, int high) {
// 假定第一行和第一行所需的单元个已经建立好了，也就是说，在这之前已经调用了DesignXlsHeaderFooter.setXlsHeader
sheet.setDefaultRowHeightInPoints(high);// 设置默认高
/* 设置各列单元格宽度 */
for (int i = 0; i < eachCellWidth.length; i++) {
// System.out.print(""+i+"\t");
sheet.setColumnWidth((short) i, (short) ((eachCellWidth[i]) * 256));
}
}
/**
* 表头数据
*
* @throws Exception
*/
public void addHeader(List rowvalues, boolean isFilter) throws Exception {
listlength = rowvalues.size();
// 设置字体
HSSFFont workFont = wb.createFont();
workFont.setFontName("宋体");
workFont.setFontHeightInPoints((short) 17);
workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 表头样式及背景色
HSSFCellStyle hdStyle = wb.createCellStyle();
/*hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
hdStyle.setBottomBorderColor(HSSFColor.BLACK.index);
hdStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
hdStyle.setLeftBorderColor(HSSFColor.BLACK.index);
hdStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
hdStyle.setRightBorderColor(HSSFColor.BLACK.index);
hdStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
hdStyle.setTopBorderColor(HSSFColor.BLACK.index);
hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);*/
/*hdStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
hdStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
hdStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);*/
hdStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
hdStyle.setFont(workFont);
String[] title = new String[rowvalues.size()];
for (int i = 0; i < rowvalues.size(); i++) {
title[i] = (String) rowvalues.get(i);
}
HSSFRow dtRow = sheet.createRow((1));
dtRow.setRowStyle(hdStyle);
if (isFilter == true) {
for (int i = 0; i < title.length; i++) {
HSSFCell cell1 = hdRow.createCell(i);
HSSFRichTextString value = new HSSFRichTextString(title[i]);
cell1.setCellValue(value);
cell1.setCellStyle(hdStyle);
}
} else {
for (int i = 0; i < title.length; i++) {
HSSFCell cell2 = dtRow.createCell(i);
HSSFRichTextString value2 = new HSSFRichTextString(title[i]);
cell2.setCellValue(value2);
//cell2.setCellStyle(hdStyle);
}
}
}
/**
* 值：List rowvalues,从第几行开始添加：int s
* 添加一行
*/
// int s = 4;
public void addRow(List rowvalues,int s) {
HSSFRow dtRow = sheet.createRow(s++);
DataFormat format = wb.createDataFormat();
HSSFCellStyle dtStyle = wb.createCellStyle();
dtStyle.setDataFormat(format.getFormat("text"));
dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dtStyle.setBottomBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dtStyle.setLeftBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dtStyle.setRightBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dtStyle.setTopBorderColor(HSSFColor.BLACK.index);
dtStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFCellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(format.getFormat("yyyy-m-d"));
dateStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dateStyle.setBottomBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dateStyle.setLeftBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dateStyle.setRightBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dateStyle.setTopBorderColor(HSSFColor.BLACK.index);
dateStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
for (int j = 0; j < rowvalues.size(); j++) {
String flag = "";
Object cell_data = rowvalues.get(j);
HSSFCell cell = dtRow.createCell(j);
// 正文格式
if (cell_data instanceof String) {
flag = "string";
cell.setCellValue((String)cell_data);
}
else if (cell_data instanceof Double) {
cell.setCellValue((Double) cell_data);
}
else if (cell_data instanceof Integer) {
cell.setCellValue(Double.valueOf(String.valueOf(cell_data)));
}
else if (cell_data instanceof Date) {
flag = "date";
cell.setCellValue((Date) cell_data);
}
else if (cell_data instanceof Boolean) {
cell.setCellValue((Boolean) cell_data);
}else if (cell_data instanceof Float) {
cell.setCellValue((Float) cell_data);
}
// 背景颜色
// if(s%2!=0)
// dtStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// dtStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
if(flag==""||flag.equals("string")){
cell.setCellStyle(dtStyle);
dtRow.setRowStyle(dtStyle);
}else if(flag.equals("date")){
cell.setCellStyle(dateStyle);
dtRow.setRowStyle(dateStyle);
}
}
// }
}
/**
* 单元格样式
* @return HSSFCellStyle
*/
public HSSFCellStyle nameStyle(String n) {
DataFormat format = wb.createDataFormat();
// 设置字体
HSSFFont workFont = wb.createFont();
workFont.setFontName("宋体");
workFont.setFontHeightInPoints((short) 16);
workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
if("1".equals(n)){
HSSFCellStyle dtStyle = wb.createCellStyle();
dtStyle.setDataFormat(format.getFormat("text"));
dtStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
dtStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//设制内容靠右显示
return dtStyle;
}else if("2".equals(n)){
HSSFCellStyle dateStyle = wb.createCellStyle();
dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
dateStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
dateStyle.setWrapText(true);
dateStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dateStyle.setBottomBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dateStyle.setLeftBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dateStyle.setRightBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dateStyle.setTopBorderColor(HSSFColor.BLACK.index);
dateStyle.setFont(workFont);
return dateStyle;
}else if("3".equals(n)){
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
// 设置单元格字体
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 200);
cellStyle.setFont(font);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
return cellStyle;
}else{
// 创建单元格样式
HSSFCellStyle cellStyle1 = wb.createCellStyle();
// 指定单元格居中对齐
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 指定当单元格内容显示不下时自动换行
cellStyle1.setWrapText(true);
return cellStyle1;
}
}
/**
* 添加一行
*/
public void addRowStyles(List rowvalues,String flag, int height) {
int num = sheet.getLastRowNum()+1;
HSSFRow dtRow = sheet.createRow(num);
if(height>0)
dtRow.setHeight((short) height);
for (int j = 0; j < rowvalues.size(); j++) {
Object cell_data = rowvalues.get(j);
HSSFCell cell = dtRow.createCell(j);
// 正文格式
if (cell_data instanceof String) {
cell.setCellValue((String)cell_data);
}else if (cell_data instanceof Date) {
cell.setCellValue((Date) cell_data);
}else if (cell_data instanceof Double) {
cell.setCellValue((Double) cell_data);
}else if (cell_data instanceof Integer) {
cell.setCellValue(Double.valueOf(String.valueOf(cell_data)));
}else if (cell_data instanceof Boolean) {
cell.setCellValue((Boolean) cell_data);
}else if (cell_data instanceof Float) {
cell.setCellValue((Float) cell_data);
}
cell.setCellStyle(this.nameStyle(flag));
}
// }
}
/**
* 根据给定的行row添加一行
* @param rowvalues 此行的数据
* @param flag 单元格样式
* @param height 行高
* @param row 指定的行索引
*/
public void addRowStyles(List rowvalues,String flag,int height ,int row ) {
HSSFRow dtRow = sheet.createRow(row);
if(height>0)
dtRow.setHeight((short) height);
for (int j = 0; j < rowvalues.size(); j++) {
Object cell_data = rowvalues.get(j);
HSSFCell cell = dtRow.createCell(j);
// 正文格式
if (cell_data instanceof String) {
cell.setCellValue((String)cell_data);
}else if (cell_data instanceof Date) {
cell.setCellValue((Date) cell_data);
}else if (cell_data instanceof Double) {
cell.setCellValue((Double) cell_data);
}else if (cell_data instanceof Integer) {
cell.setCellValue(Double.valueOf(String.valueOf(cell_data)));
}else if (cell_data instanceof Boolean) {
cell.setCellValue((Boolean) cell_data);
}else if (cell_data instanceof Float) {
cell.setCellValue((Float) cell_data);
}
cell.setCellStyle(this.nameStyle(flag));
}
// }
}
/**
* 合并单元格//左上角到右下角int col1,int row1,int col2,int row2
* 添加合并的单元格的样式
*/
public void hebingStyle(int col1,int row1,int col2,int row2,String flag){
for(int i = col1;i<=col2;i++){
HSSFRow row = sheet.getRow(i);
if(row == null)
row = sheet.createRow(i);
for(int j = row1;j <= row2;j++){
HSSFCell cell = row.getCell(j);
if(cell == null)
cell = row.createCell(j);
cell.setCellStyle(this.nameStyle(flag));
}
}
sheet.addMergedRegion(new Region(col1, (short)row1, col2, (short) row2));
}
/**
* 添加一行
*/
int s1 =1;
public void addRowStyle(List rowvalues,String flag ) {
HSSFRow dtRow = sheet.createRow(s1++);
DataFormat format = wb.createDataFormat();
// 设置字体
HSSFFont workFont = wb.createFont();
workFont.setFontName("宋体");
workFont.setFontHeightInPoints((short) 14);
workFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle dtStyle = wb.createCellStyle();
dtStyle.setDataFormat(format.getFormat("text"));
dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dtStyle.setBottomBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dtStyle.setLeftBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dtStyle.setRightBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dtStyle.setTopBorderColor(HSSFColor.BLACK.index);
dtStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
dtStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);//设制内容靠右显示
HSSFCellStyle dateStyle = wb.createCellStyle();
dateStyle.setDataFormat(format.getFormat("text"));
dateStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dateStyle.setBottomBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dateStyle.setLeftBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dateStyle.setRightBorderColor(HSSFColor.BLACK.index);
dateStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dateStyle.setTopBorderColor(HSSFColor.BLACK.index);
dateStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
dateStyle.setFont(workFont);
for (int j = 0; j < rowvalues.size(); j++) {
Object cell_data = rowvalues.get(j);
HSSFCell cell = dtRow.createCell(j);
// 正文格式
if (cell_data instanceof String) {
cell.setCellValue((String)cell_data);
}else if (cell_data instanceof Date) {
cell.setCellValue((Date) cell_data);
}else if (cell_data instanceof Double) {
cell.setCellValue((Double) cell_data);
}else if (cell_data instanceof Integer) {
cell.setCellValue(Double.valueOf(String.valueOf(cell_data)));
}else if (cell_data instanceof Boolean) {
cell.setCellValue((Boolean) cell_data);
}else if (cell_data instanceof Float) {
cell.setCellValue((Float) cell_data);
}
// 背景颜色
// if(s%2!=0)
// dtStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// dtStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
if(flag==""||flag.equals("string")){
cell.setCellStyle(dtStyle);
dtRow.setRowStyle(dtStyle);
}else if(flag.equals("date")){
cell.setCellStyle(dateStyle);
dtRow.setRowStyle(dateStyle);
}
}
// }
}
/**
* 添加相同的行
* @param starRow
* @param rows
*/
// public void insertRow(int starRow,int rows) {
//
// sheet.shiftRows(starRow + 1, sheet.getLastRowNum(), rows,true,false);
Parameters:
startRow - the row to start shifting
endRow - the row to end shifting
n - the number of rows to shift
copyRowHeight - whether to copy the row height during the shift
resetOriginalRowHeight - whether to set the original row's height to the default
//
// starRow = starRow - 1;
//
// HSSFRow sourceRow = null;
// HSSFRow targetRow = null;
// HSSFCell sourceCell = null;
// HSSFCell targetCell = null;
// for (int i = 0; i < rows; i++) {
//
// short m;
//
// starRow = starRow + 1;
// sourceRow = sheet.getRow(starRow);
// targetRow = sheet.createRow(starRow + 1);
// targetRow.setHeight(sourceRow.getHeight());
//
// for (m = sourceRow.getFirstCellNum(); m < sourceRow.getLastCellNum(); m++) {
//
// sourceCell = sourceRow.getCell(m);
// targetCell = targetRow.createCell(m);
//
// //targetCell.setEncoding(sourceCell.getEncoding());
// targetCell.setCellStyle(sourceCell.getCellStyle());
// targetCell.setCellType(sourceCell.getCellType());
//
// }
// }
//
// }
/**
* 给指定的行追加一行数据
*
* @param rowvalues
* @param row
*/
public void insertRow(List rowvalues, int row) {
sheet.shiftRows(row, sheet.getLastRowNum(), 1);
HSSFRow dtRow = sheet.createRow(row);
HSSFCellStyle dtStyle = wb.createCellStyle();
dtStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
dtStyle.setBottomBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dtStyle.setLeftBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
dtStyle.setRightBorderColor(HSSFColor.BLACK.index);
dtStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
dtStyle.setTopBorderColor(HSSFColor.BLACK.index);
dtStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
DataFormat format = wb.createDataFormat();
Short str = format.getFormat("text");
Short date = format.getFormat("yyyy-m-d");
for (int j = 0; j < rowvalues.size(); j++) {
Object cell_data = rowvalues.get(j);
HSSFCell cell = dtRow.createCell(j);
if (cell_data instanceof String) {
dtStyle.setDataFormat(str);
cell.setCellValue((String) cell_data);
} else if (cell_data instanceof Double) {
cell.setCellValue((Double) cell_data);
} else if (cell_data instanceof Integer) {
cell.setCellValue(Double.valueOf(String.valueOf(cell_data)));
} else if (cell_data instanceof Date) {
dtStyle.setDataFormat(date);
cell.setCellValue((Date) cell_data);
} else if (cell_data instanceof Boolean) {
cell.setCellValue((Boolean) cell_data);
}else if (cell_data instanceof Float) {
cell.setCellValue((Float) cell_data);
}
// 背景颜色
// if(s%2!=0)
// dtStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// dtStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cell.setCellStyle(dtStyle);
}
}
/**
* 删除指定的行
* @param row
*/
public void delRow(int row) {
if(row>0){
try {
//HSSFRow dtRow = sheet.getRow(row);
sheet.shiftRows(row, sheet.getLastRowNum(), -1);
//sheet.removeRow(dtRow);
} catch (RuntimeException e) {
e.printStackTrace();
System.out.println("error");
}
}else{
System.out.println("错误的");
}
}
/**
* 给指定的列给出下拉列表
*
* @param row
* @param cells
* @param list
*/
public void setSelect(int row, int cells, List cellvalue) {
String[] str = new String[cellvalue.size()];
for (int i = 0; i < cellvalue.size(); i++) {
str[i] = (String) cellvalue.get(i);
}
CellRangeAddressList regions = new CellRangeAddressList(row, 65535, cells,cells);
DVConstraint constraint = DVConstraint.createExplicitListConstraint(str);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions,constraint);
sheet.addValidationData(dataValidate); // 加入数据有效性到当前sheet对象
}
/**
* 合并单元格//左上角到右下角int col1,int row1,int col2,int row2
*/
public void hebing(int col1,int row1,int col2,int row2){
// HSSFRow row = sheet.createRow(0);
// HSSFCell cell = row.createCell(0);
// cell.setCellValue(sheetName);
sheet.addMergedRegion(new Region(col1, (short)row1, col2, (short) row2));
}
/**
* 冻结窗口加拆分
*/
public void freezeChaifenPane(int xSplitPos,int ySplitPos,int leftmostColumn,int topRow,int activePane){
sheet.createSplitPane(xSplitPos, ySplitPos, leftmostColumn, topRow, activePane);
}
/**
* 冻结第一行 freezeSinglePane( 0, 1, 0, 1 ); 冻结第一列freezeSinglePane( 1, 0, 1, 0 );
*/
public void freezeSinglePane(int colSplit,int rowSplit,int leftmostColumn,int topRow){
sheet.createFreezePane(colSplit, rowSplit, leftmostColumn, topRow);
}
/**
* 冻结拆分窗口 冻结列和行int colSplit,int rowSplit
*/
public void freezeSinglePane(int colSplit,int rowSplit){
sheet.createFreezePane(colSplit, rowSplit);
}
/**
* 具体文件生成的路径
*
* @param file
* @throws Exception
*/
public void exportExcel(String file) throws Exception {
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
}
/**
* 具体文件生成的文件
*
* @param file
* @throws Exception
*/
public void exportExcel(File file) throws Exception {
FileOutputStream fileOut = new FileOutputStream(file);
wb.write(fileOut);
fileOut.close();
}
/**
* 具体文件生成的文件
*
* @param file
* @throws Exception
*/
public void exportExcel(OutputStream outputstream) throws Exception {
BufferedOutputStream buffout = new BufferedOutputStream(outputstream);
wb.write(buffout);
buffout.flush();
buffout.close();
}
public static void main(String[] args) throws Exception {
ExcelUtilPoi s = new ExcelUtilPoi();
s.createSheet("所领导打分");
List listHeader = new ArrayList();
listHeader.add("中层干部互评打分汇总");
s.hebingStyle(0, 0, 0,10, "4");///合并10列
s.addHeader(listHeader, true);
List listDate = new ArrayList();
listDate.add("2010-12-1");
s.hebingStyle(1, 0, 1,10, "4"); ///合并10列
s.addRowStyles(listDate,"1",0,1);
List listMenu = new ArrayList();
listMenu.add("序号");
s.hebingStyle(2, 0, 3,0, "3"); ///合并10列
listMenu.add("部门");
s.hebingStyle(2, 1, 3,1, "3"); ///合并10列
listMenu.add("姓名");
s.hebingStyle(2, 2, 3,2, "3"); ///合并10列
for(int i=1;i<40;i++){
listMenu.add(i);
s.hebingStyle(2, i+2, 3,i+2, "3"); ///合并10列
}
listMenu.add("总分");
s.hebingStyle(2, 42, 3,42, "3"); ///合并10列
listMenu.add("人数");
s.hebingStyle(2, 43, 3,43, "3"); ///合并10列
listMenu.add("平均分");
s.hebingStyle(2, 44, 3,44, "3"); ///合并10列
s.addRowStyles(listMenu,"2",550,2);
// ADD 数据
s.sheet.setColumnWidth((short)4,(short)1500);
List listData = null;
for(int j=0;j<40;j++){
listData = new ArrayList();
listData.add(j+1);
listData.add("部门");
listData.add("姓名");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("");
listData.add("总分");
listData.add("人数");
listData.add("平均分");
//s.addRow(listData,4+j);
s.addRowStyles(listData, "3",460);
}
// s.freezeSinglePane(3,4); //冻结拆分窗口 冻结列和行
// s.freezeChaifenPane(2000, 2000, 0, 0, HSSFSheet.PANE_LOWER_LEFT);//冻结窗口加拆分
// s.freezeSinglePane( 0, 1, 0, 1 ); // 冻结第一行
// s.freezeSinglePane( 1, 0, 1, 0 );//冻结第一列
//s.createSheet("所领导打分as");
File file = new File("D:\\1.xls");
s.exportExcel(file);
}
}JAVA技术学习 https://www.itkc8.com
919
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
