java excel表格合并多列_java 通用的导出 excel 合并单元格格式(二)

package com.nskj.utils;

import java.io.BufferedReader;

import java.io.ByteArrayInputStream;

import java.io.ByteArrayOutputStream;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStreamReader;

import java.io.OutputStream;

import java.text.DecimalFormat;

import java.text.SimpleDateFormat;

import java.util.HashMap;

import java.util.Iterator;

import java.util.List;

import java.util.Map;

import java.util.Scanner;

import java.util.Map.Entry;

import javax.servlet.ServletOutputStream;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

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.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.util.CellRangeAddress;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;/***

* excel 导出工具类

* @author Administrator

**/

public classExcelUtil {private static DecimalFormat df = new DecimalFormat("0");//默认单元格格式化日期字符串

private static SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss");//格式化数字

private static DecimalFormat nf = new DecimalFormat("0.00");private final static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);/***

* 读取本地文件txt 获取excel标题

* @param path

* @return*/

public staticString getTitle(String path){

FileInputStream fis= null;

InputStreamReader isr= null;

BufferedReader br= null; //用于包装InputStreamReader,提高处理性能。因为BufferedReader有缓冲的，而InputStreamReader没有。

String str = "";

String str1= "";try{

fis= new FileInputStream(path);//FileInputStream//从文件系统中的某个文件中获取字节

isr = new InputStreamReader(fis,"UTF-8");//InputStreamReader 是字节流通向字符流的桥梁,//br = new BufferedReader(new InputStreamReader(new FileInputStream(new File("")), "UTF-8"));

br = new BufferedReader(isr);//从字符输入流中读取文件中的内容,封装了一个new InputStreamReader的对象

while ((str = br.readLine()) != null) {

str1+= str + ",";

}

str1= str1.substring(0,str1.length()-1);//当读取的一行不为空时,把读到的str的值赋给str1

System.out.println(str1);//打印出str1

} catch(FileNotFoundException e) {

System.out.println("找不到指定文件");

}catch(IOException e) {

System.out.println("读取文件失败");

}finally{try{

br.close();

isr.close();

fis.close();//关闭的时候最好按照先后顺序关闭最后开的先关闭所以先关s,再关n,最后关m

} catch(IOException e) {

e.printStackTrace();

}

}return str1; //读取excel表头

}/***

* 读取服务器文件txt 获取excel标题

* @param fileName

* @return*/

publicString getFile(String fileName) {

StringBuilder result= new StringBuilder("");

String str1="";if(fileName.endsWith(".txt")){

ClassLoader classLoader=getClass().getClassLoader();

File file= newFile(classLoader.getResource(fileName).getFile());try (Scanner scanner = newScanner(file)) {while(scanner.hasNextLine()) {

String line=scanner.nextLine();

result.append(line).append(",");

}

scanner.close();

str1=result.toString();

str1= str1.substring(0,str1.length()-1);

}catch(IOException e) {

e.printStackTrace();

}

}if(fileName.endsWith(".xml")){

FileInputStream fis= null;

InputStreamReader isr= null;

BufferedReader br= null; //用于包装InputStreamReader,提高处理性能。因为BufferedReader有缓冲的，而InputStreamReader没有。

String str = "";

String str2="";try{

fis= new FileInputStream(this.getClass().getClassLoader().getResource("/").getPath()+"/"+fileName);

isr= new InputStreamReader(fis,"UTF-8");//InputStreamReader 是字节流通向字符流的桥梁,//br = new BufferedReader(new InputStreamReader(new FileInputStream(new File("")), "UTF-8"));

br = new BufferedReader(isr);//从字符输入流中读取文件中的内容,封装了一个new InputStreamReader的对象

while ((str = br.readLine()) != null) {

str2+= str + ",";

}//str1 = str1.substring(0,str1.length()-1);//str1 =str1.replace("<?xml version=\"1.0\" encoding=\"UTF-8\"?>,", "");//int str3=str2.indexOf(",");//str1 = str2.substring(str3+1,str2.length() -(str3+1));

str1=str2.substring(str2.indexOf(',')+1).trim();

str1= str1.substring(0,str1.length()-1);

}catch(IOException e) {

e.printStackTrace();

}

}returnstr1;

}//2. writeIntoExcel() 将datatable 数据写入excel

/*public static void writeExcel(ArrayList> result,String path){

if(result == null){

return;

}

HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet("sheet1");

for(int i = 0 ;i < result.size() ; i++){

HSSFRow row = sheet.createRow(i);

if(result.get(i) != null){

for(int j = 0; j < result.get(i).size() ; j ++){

HSSFCell cell = row.createCell(j);

cell.setCellValue(result.get(i).get(j).toString());

}

}

}

ByteArrayOutputStream os = new ByteArrayOutputStream();

try

{

wb.write(os);

} catch (IOException e){

e.printStackTrace();

}

byte[] content = os.toByteArray();

File file = new File(path);//Excel文件生成后存储的位置。

OutputStream fos = null;

try

{

fos = new FileOutputStream(file);

fos.write(content);

os.close();

fos.close();

}catch (Exception e){

e.printStackTrace();

}

}*/

public static void export(List>list, String filename, HttpServletResponse response, String titles) {try{

response.setContentType("application/x-execl");

response.setHeader("Content-Disposition","attachment;filename=" + new String(filename.getBytes(), "ISO-8859-1"));

ServletOutputStream outputStream=response.getOutputStream();

exportExcel(list, filename, outputStream, titles);if (outputStream != null) {

outputStream.close();

}

}catch(Exception e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}/**

* 数据导出公用方法

**/

public static void exportExcel(List>list, String filename, ServletOutputStream outputStream,String titles) {try{

Map map1 = list.get(0);

StringBuilder sb= newStringBuilder();

String[] fields=titles.split(",");/*sb.append(title);

String[] fields = sb.toString().split(",");*/

//1、创建工作簿

HSSFWorkbook workbook = newHSSFWorkbook();//1.1、创建合并单元格对象

CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, fields.length - 1);//起始行号，结束行号，起始列号，结束列号

CellRangeAddress cellRangeAddress1 = new CellRangeAddress(1, 1, 0, fields.length - 1);//起始行号，结束行号，起始列号，结束列号

CellRangeAddress cellRangeAddress2 = new CellRangeAddress(1, 1, 7, 12);//起始行号，结束行号，起始列号，结束列号//1.2、头标题样式

HSSFCellStyle style1 = createCellStyle(workbook, (short) 18);//1.3、列标题样式

HSSFCellStyle style2 = createCellStyle(workbook, (short) 13);

String headRow=filename;//2、创建工作表

HSSFSheet sheet =workbook.createSheet(headRow);//2.1、加载合并单元格对象

sheet.addMergedRegion(cellRangeAddress);

sheet.addMergedRegion(cellRangeAddress1);

sheet.addMergedRegion(cellRangeAddress2);//sheet.addMergedRegion(cellRangeAddress4);//设置默认列宽

sheet.setDefaultColumnWidth(22);

sheet.setDefaultRowHeightInPoints(22);

sheet.autoSizeColumn(1, true);//

//3、创建行//3.1、创建头标题行；并且设置头标题

HSSFRow row1 = sheet.createRow(0);

row1.setHeightInPoints(50);

HSSFCell cell1= row1.createCell(0);//加载单元格样式

cell1.setCellStyle(style1);

cell1.setCellValue(headRow);//3.1、创建副标题行；并且设置

HSSFRow row2 = sheet.createRow(1);

row2.setHeightInPoints(25);

HSSFCell cell2= row2.createCell(0);//3.2、创建列标题行；并且设置列标题

HSSFRow row3 = sheet.createRow(2);/*String[] titles = new String[fields.length];

for (int i = 0; i < fields.length; i++) {

titles[i] = getTitles(type, fields[i]);

}*/

for (int i = 0; i < fields.length; i++) {

HSSFCell cell5=row3.createCell(i);//加载单元格样式

cell5.setCellStyle(style2);

cell5.setCellValue(fields[i]);

}//备注详情

HSSFRow row36 = sheet.createRow(1);

row36.setHeightInPoints(18);

HSSFCell cell36= row36.createCell(0);//加载单元格样式

HSSFCellStyle style36 =workbook.createCellStyle();

style36.setAlignment(HSSFCellStyle.ALIGN_LEFT);//水平居左

style36.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

cell36.setCellStyle(style36);

HSSFFont font=workbook.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体

font.setFontHeightInPoints((short) 16);

style36.setFont(font);//cell36.setCellValue(data_batch);//自适应中文//4、操作单元格；写入excel

if (list != null && list.size() > 0) {for (int j = 0; j < list.size(); j++) {

Map map = list.get(j);//Object bean = getBean(type, map);

HSSFRow row = sheet.createRow(j + 3);int i=0;for(Map.Entryentry:map.entrySet()){

String val="";if((entry.getValue())!=null && (entry.getValue()) !="null"){

val=getValue( entry.getKey(),entry.getValue().toString());

}else{

val=" ";

}//String val=() ? entry.getValue().toString() : "null";

HSSFCell cell =row.createCell(i);

i++;

cell.setCellValue(val);

logger.info("第" + (j+3) + "行,第" + val+ "--赋值成功");

}/*for (int i = 0; i < fields.length; i++) {

//String value = CommonUtil.getValue(bean, StringUtils.toCamelCase(fields[i]));

//value = getValue(fields[i], value);

//cell.setCellValue(value);

cell.setCellValue(list.get(j).get(i).toString());

//cell.setCellValue(list.get(j).get(i).toString());

}*/}

}//5、输出

workbook.write(outputStream);

outputStream.flush();

outputStream.close();

}catch(Exception e) {

e.printStackTrace();

}

}/**

* 创建单元格样式

*

* @param workbook

* 工作簿

* @param fontSize

* 字体大小

* @return 单元格样式*/

private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, shortfontSize) {

HSSFCellStyle style=workbook.createCellStyle();

style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中

style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中//创建字体

HSSFFont font =workbook.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体

font.setFontHeightInPoints(fontSize);//加载字体

style.setFont(font);returnstyle;

}public staticString getValue(String field, String value) {if (field.equals("credLocation")) {if (value.equals("1")) {

value= "库内人员";

}if (value.equals("2")) {

value= "库外人员";

}

}if (field.equals("chongfu")) {if (value.equals("1")) {

value= "否";

}else{

value= "是";

}

}if (field.equals("days")) {if (value.contains("-")) {

String value1= "逾期"+value+"天";

value=value1.replace("-", "");

}else{

value= value+"天后保养";

}

}if (field.equals("recordStatus")) {if (value.equals("1")) {

value= "故障报修";

}else if (value.equals("2")){

value= "已派工";

}else if(value.equals("3")){

value= "已出发";

}else if(value.equals("4")){

value= "到达现场";

}else if(value.equals("5")){

value= "维修完成";

}else if(value.equals("6")){

value= "服务评定";

}

}returnvalue;

}public staticDecimalFormat getDf() {returndf;

}public static voidsetDf(DecimalFormat df) {

ExcelUtil.df=df;

}public staticSimpleDateFormat getSdf() {returnsdf;

}public static voidsetSdf(SimpleDateFormat sdf) {

ExcelUtil.sdf=sdf;

}public staticDecimalFormat getNf() {returnnf;

}public static voidsetNf(DecimalFormat nf) {

ExcelUtil.nf=nf;

}/**

* 多行表头

* dataList：导出的数据；sheetName：表头名称； head0：表头第一行列名；headnum0：第一行合并单元格的参数

* head1：表头第二行列名；headnum1：第二行合并单元格的参数；detail：导出的表体字段

**/

public static voidreportMergeXls(HttpServletRequest request,

HttpServletResponse response, List>dataList,

String sheetName, String[] head0, String[] headnum0,

String[] head1, String[] headnum1, String[] detail)

throws Exception {

HSSFWorkbook workbook= newHSSFWorkbook();//1、创建标题

String headRow =sheetName;//1.1、头标题样式

HSSFCellStyle headstyle = createCellStyle(workbook, (short) 18);//1.2、列标题样式

HSSFCellStyle style1 = createCellStyle(workbook, (short) 13);//2、创建工作表

HSSFSheet sheet =workbook.createSheet(headRow);//设置默认列宽

sheet.setDefaultColumnWidth(22);

sheet.setDefaultRowHeightInPoints(22);

sheet.autoSizeColumn(1, true);//3、创建行//3.1、创建头标题行；并且设置头标题

HSSFRow row1 = sheet.createRow(0);

row1.setHeightInPoints(50);

HSSFCell cell1= row1.createCell(0);//加载单元格样式

cell1.setCellStyle(headstyle);

cell1.setCellValue(headRow);//3.1、创建副标题行；并且设置

HSSFRow row2 = sheet.createRow(1);

row2.setHeightInPoints(25);

HSSFCell cell2= row2.createCell(0);//3.2、创建列标题行；并且设置列标题

HSSFRow row3 = sheet.createRow(2);//第一行表头标题

sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, head0.length-1));

HSSFRow row= sheet.createRow(0);

row.setHeight((short) 0x349);

HSSFCell cell= row.createCell(0);

cell.setCellStyle(headstyle);

CellUtil.setCellValue(cell, sheetName);//第二行表头列名

row = sheet.createRow(1);for (int i = 0; i < head0.length; i++) {

cell=row.createCell(i);

cell.setCellValue(head0[i]);

cell.setCellStyle(style1);

}//1.3、普通单元格样式(中文)样式

HSSFCellStyle style2 =workbook.createCellStyle();//HSSFCellStyle style36 = workbook.createCellStyle();

style2.setAlignment(HSSFCellStyle.ALIGN_LEFT);//水平居左

style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中

HSSFFont font =workbook.createFont();

font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗字体

font.setFontHeightInPoints((short) 10);

style2.setFont(font);//动态合并单元格

for (int i = 0; i < headnum0.length; i++) {

String[] temp= headnum0[i].split(",");

Integer startrow= Integer.parseInt(temp[0]);

Integer overrow= Integer.parseInt(temp[1]);

Integer startcol= Integer.parseInt(temp[2]);

Integer overcol= Integer.parseInt(temp[3]);

sheet.addMergedRegion(newCellRangeAddress(startrow, overrow,

startcol, overcol));

}//设置合并单元格的参数并初始化带边框的表头(这样做可以避免因为合并单元格后有的单元格的边框显示不出来)

row = sheet.createRow(2);//因为下标从0开始，所以这里表示的是excel中的第三行

for (int i = 0; i < head0.length; i++) {

cell=row.createCell(i);

cell.setCellStyle(style1);//设置excel中第四行的1、2、7、8列的边框

if(i > 1) {for (int j = 0; j < head1.length; j++) {

cell= row.createCell(j + 1);

cell.setCellValue(head1[j]);//给excel中第三行的3、4、5、6列赋值("温度℃", "湿度%", "温度℃", "湿度%")

cell.setCellStyle(style1);//设置excel中第三行的3、4、5、6列的边框

}

}

}//动态合并单元格

for (int i = 0; i < headnum1.length; i++) {

String[] temp= headnum1[i].split(",");

Integer startrow= Integer.parseInt(temp[0]);

Integer overrow= Integer.parseInt(temp[1]);

Integer startcol= Integer.parseInt(temp[2]);

Integer overcol= Integer.parseInt(temp[3]);

sheet.addMergedRegion(newCellRangeAddress(startrow, overrow,

startcol, overcol));

}//设置列值-内容

for (int i = 0; i < dataList.size(); i++) {

row= sheet.createRow(i + 3);//标题、时间、表头字段共占了3行，所以在填充数据的时候要加3，也就是数据要从第4行开始填充

for (int j = 0; j < detail.length; j++) {

Map tempmap= (HashMap) dataList.get(i);

Object data= tempmap.get(detail[j]);

cell=row.createCell(j);

cell.setCellStyle(style2);

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

CellUtil.setCellValue(cell, data);

}

}

String fileName= new String(sheetName.getBytes("gb2312"), "ISO8859-1");

ByteArrayOutputStream baos= newByteArrayOutputStream();

workbook.write(baos);

response.setContentType("application/x-download;charset=utf-8");

response.addHeader("Content-Disposition", "attachment;filename="

+ fileName + ".xls");

OutputStream os=response.getOutputStream();

ByteArrayInputStream bais= newByteArrayInputStream(baos.toByteArray());byte[] b = new byte[1024];while ((bais.read(b)) > 0) {

os.write(b);

}

bais.close();

os.flush();

os.close();

}

}