public class CwFundsChangeExportExcel {
private HSSFWorkbook wb = new HSSFWorkbook();
private HSSFSheet sheet = wb.createSheet();
public static void main(String[] args) {}
/**
*
* @param wb
* @param type
* 1:head 2 je
* @return
*/
public HSSFCellStyle setCellStyleHead(HSSFWorkbook wb,int type,int statu)
{
HSSFCellStyle cellStyle = wb.createCellStyle();
// 创建单元格样式
if(type != 2)
{
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
}
else
{
// 指定单元格居中对齐
cellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// 指定单元格垂直居中对齐
cellStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT);
}
//设置单元格边框和颜色
cellStyle.setBorderBottom((short)1);
cellStyle.setBorderLeft((short)1);
cellStyle.setBorderRight((short)1);
cellStyle.setBorderTop((short)1);
HSSFFont font = wb.createFont();
if(type == 1)
{
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
}
if(statu == 2)
{
font.setColor(HSSFColor.RED.index);
}
font.setFontName("宋体");
cellStyle.setFont(font);
//字体大小
//font.setFontHeight((short) 200);
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
// 指定当单元格内容显示不下时自动换行
cellStyle.setWrapText(true);
return cellStyle;
}
@SuppressWarnings("unchecked")
public void createExcel(Map<String,String> headMap,List<Map<String,Object>> valueList,String url) {
ExportExcel exportExcel = new ExportExcel(wb, sheet);
// 设置列头
HSSFRow row0 = sheet.createRow(0);
//一列
HSSFCell cell_00 = row0.createCell((short)0);
//列头样式
cell_00.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_00.setCellValue(new HSSFRichTextString("银行"));
//二列
HSSFCell cell_01 = row0.createCell((short)1);
//列头样式
cell_01.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_01.setCellValue(new HSSFRichTextString("期初"));
//三列
HSSFCell cell_02 = row0.createCell((short)2);
//列头样式
cell_02.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_02.setCellValue(new HSSFRichTextString());
//四列
HSSFCell cell_03 = row0.createCell((short)3);
//列头样式
cell_03.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_03.setCellValue(new HSSFRichTextString("收入"));
//五
HSSFCell cell_04 = row0.createCell((short)4);
//列头样式
cell_04.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_04.setCellValue(new HSSFRichTextString());
//六
HSSFCell cell_05 = row0.createCell((short)5);
//列头样式
cell_05.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_05.setCellValue(new HSSFRichTextString());
//七
HSSFCell cell_06 = row0.createCell((short)6);
//列头样式
cell_06.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_06.setCellValue(new HSSFRichTextString());
//八
HSSFCell cell_07 = row0.createCell((short)7);
//列头样式
cell_07.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_07.setCellValue(new HSSFRichTextString("支出"));
//九
HSSFCell cell_08 = row0.createCell((short)8);
//列头样式
cell_08.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_08.setCellValue(new HSSFRichTextString());
//十
HSSFCell cell_09 = row0.createCell((short)9);
//列头样式
cell_09.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_09.setCellValue(new HSSFRichTextString());
//十一
HSSFCell cell_010 = row0.createCell((short)10);
//列头样式
cell_010.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_010.setCellValue(new HSSFRichTextString());
//十二
HSSFCell cell_011 = row0.createCell((short)11);
//列头样式
cell_011.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_011.setCellValue(new HSSFRichTextString("转款"));
//十三
HSSFCell cell_012 = row0.createCell((short)12);
//列头样式
cell_012.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_012.setCellValue(new HSSFRichTextString("期末"));
//十四
HSSFCell cell_013 = row0.createCell((short)13);
//列头样式
cell_013.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_013.setCellValue(new HSSFRichTextString());
/** 列头 第二行 */
// 设置列头
HSSFRow row1 = sheet.createRow(1);
//一列
HSSFCell cell_10 = row1.createCell((short)0);
//列头样式
cell_10.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_10.setCellValue(new HSSFRichTextString());
//二列
HSSFCell cell_11 = row1.createCell((short)1);
//列头样式
cell_11.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_11.setCellValue(new HSSFRichTextString("活期"));
//三列
HSSFCell cell_12 = row1.createCell((short)2);
//列头样式
cell_12.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_12.setCellValue(new HSSFRichTextString("保证金"));
//四列
HSSFCell cell_13 = row1.createCell((short)3);
//列头样式
cell_13.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_13.setCellValue(new HSSFRichTextString("托收/贴现"));
//五
HSSFCell cell_14 = row1.createCell((short)4);
//列头样式
cell_14.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_14.setCellValue(new HSSFRichTextString("往来"));
//六
HSSFCell cell_15 = row1.createCell((short)5);
//列头样式
cell_15.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_15.setCellValue(new HSSFRichTextString("其他"));
//七
HSSFCell cell_16 = row1.createCell((short)6);
//列头样式
cell_16.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_16.setCellValue(new HSSFRichTextString("收入合计"));
//八
HSSFCell cell_17 = row1.createCell((short)7);
//列头样式
cell_17.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_17.setCellValue(new HSSFRichTextString("兑付"));
//九
HSSFCell cell_18 = row1.createCell((short)8);
//列头样式
cell_18.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_18.setCellValue(new HSSFRichTextString("往来"));
//十
HSSFCell cell_19 = row1.createCell((short)9);
//列头样式
cell_19.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_19.setCellValue(new HSSFRichTextString("其他"));
//十一
HSSFCell cell_110 = row1.createCell((short)10);
//列头样式
cell_110.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_110.setCellValue(new HSSFRichTextString("支出合计"));
//十二
HSSFCell cell_111 = row1.createCell((short)11);
//列头样式
cell_111.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_111.setCellValue(new HSSFRichTextString());
//十三
HSSFCell cell_112 = row1.createCell((short)12);
//列头样式
cell_112.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_112.setCellValue(new HSSFRichTextString("活期"));
//十四
HSSFCell cell_113 = row1.createCell((short)13);
//列头样式
cell_113.setCellStyle(setCellStyleHead(wb,1,0));
//VALUE
cell_113.setCellValue(new HSSFRichTextString("保证金"));
//银行名称 合并单元格
sheet.addMergedRegion(new Region(0, (short)0, 1, (short) 0));
//期初合并单元格
sheet.addMergedRegion(new Region(0, (short)1, 0, (short) 2));
//收入合并单元格
sheet.addMergedRegion(new Region(0, (short)3, 0, (short) 6));
//支出合并单元格
sheet.addMergedRegion(new Region(0, (short)7, 0, (short) 10));
//转款合并单元格
sheet.addMergedRegion(new Region(0, (short)11, 1, (short) 11));
//期末合并单元格
sheet.addMergedRegion(new Region(0, (short)12, 0, (short) 13));
//遍历value值
for (int i = 0 ; i < valueList.size(); i++)
{
HSSFRow _row = sheet.createRow(i+2);
Map<String,Object> valMap = valueList.get(i);
Iterator itHead = headMap.keySet().iterator();
String head = "";
int j = 0;
while(itHead.hasNext())
{
head = itHead.next() + "";
//日期类型
if(head.indexOf("dt") != -1)
{
HSSFCell cell12 = _row.createCell((short) j);
cell12.setCellValue(new HSSFRichTextString(valMap.get(head)+""));
cell12.setCellStyle(setCellStyleHead(wb,3,0));
}
else if(head.indexOf("sl") != -1)
{
HSSFCell cell12 = _row.createCell((short) j);
int statu = 0;
if(null == valMap.get(head) || "".equals(valMap.get(head)+""))
{
cell12.setCellValue(new HSSFRichTextString());
}
else
{
if((valMap.get(head)+"").indexOf("-")!=-1)
{
statu = 2;
}
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatNumber(valMap.get(head)+"",2)));
}
cell12.setCellStyle(setCellStyleHead(wb,2,statu));
}
else if(head.indexOf("je") != -1)
{
// 金额格式化
HSSFCell cell12 = _row.createCell((short) j);
int statu = 0;
BigDecimal je = new BigDecimal(valMap.get(head)+"");
//最后一行合计 不用处理
if(i != valueList.size()-1)
{
if(head.equals("tsjes_je") || head.equals("wljes_je") || head.equals("otherjes_je")
|| head.equals("sumjes_je") || head.equals("cdjez_je") || head.equals("wljez_je")
|| head.equals("otherjez_je") || head.equals("sumjez_je") || head.equals("sumzk_je"))
{
if(je.compareTo(new BigDecimal("0")) == 0)
{
cell12.setCellValue(new HSSFRichTextString());
}
else
{
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
}
}
else
{
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
}
}
else
{
cell12.setCellValue(new HSSFRichTextString(DataConvertUtils.formatMoney(valMap.get(head)+"",2)));
}
if((valMap.get(head)+"").indexOf("-") != -1)
{
statu = 2;
}
cell12.setCellStyle(setCellStyleHead(wb,2,statu));
}
else
{
HSSFCell cell = _row.createCell((short)j);
//列头样式
cell.setCellStyle(setCellStyleHead(wb,3,0));
//VALUE
cell.setCellValue(new HSSFRichTextString(valMap.get(head)+""));
}
j++;
}
}
for (int i = 0; i < headMap.size(); i++) {
//自适应宽度
sheet.autoSizeColumn((short)i);
//sheet.setColumnWidth((short)i, (short)3000);
}
exportExcel.outputExcel(url);
}
}
分享到：
相关推荐
NULL 博文链接：https://snow-163-com.iteye.com/blog/1470510
能稳定运行,windows 平台,ActiveXComponent技术
POI操作Excel生成水印，该水印其实就是一个图片，该代码是将文字生成图片，再将图片变成水印的。
Jakarta的POI项目提供了一组操纵Windows文档的Java API，如下几个类提供了操作Excel文件的方便的途径：HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell，分别代表了Excel电子表格中的Book、Sheet、行和单元格
poi生成excel及合并单元格示例，详见文章http://blog.csdn.net/geloin/article/details/17219885
JSON转EXCEL，使用HSSFWorkbook。可生成多个sheet，sheet页名为JSON参数数组的节点名称，可替换节点名称，以及可以把从数据库查出来的英文字段名替换为中文表头。详情可见博客：...
用Java输出图表的场景和这个一样，POI、JXL对操作Excel图表无能为力 但Excel支持脚本的，也有工作表载入事件，所以也可以用同样的原理实现 只不过这次客户端不再是浏览器而是Excel应用程序。 这样交代的应该比较...
import java.io.FileOutputStream; public class CreateXL{ public static String outputFile="D:\\zhangJ.xls"; public static void main(String argv[]){ try{ HSSFWorkbook workbook=new HSSFWorkbook();...
* 把数据库中的字段导入到Excel ，并生成Excel文档 **/ public ActionForward getDownload(ActionMapping actionMapping, ActionForm actionForm, HttpServletRequest request, HttpServletResponse response) ...
POI海量数据⼤数据⽂件⽣成 海量数据⼤数据⽂件⽣成SXSSFWorkbook使⽤简介 使⽤简介 在之前我们知道处理xls的excel⽤的workbook是HSSFWorkbook，处理xlsx的excel⽤的是XSSFWorkbook。 上⾯两个类导出excel的时候数据...
然后我们来看一个完整的STRUTS的小例子，在这个例子里面我们要做的事情是要模拟移动公司的网上营业厅里面的一个功能，我们要把一个客户当月的通话记录和各种信息查询出来，并且生成一张excel报表。首先，我们来看...