关于java生成excel和读取有规律的excel返回list在这篇文章https://blog.csdn.net/lianzhang861/article/details/83092835

1.需求

这次需求是将有合并单元格的Excel数据读取出来并保存，难点是要将合并的单元格所占的行列数都保存下来，以便到时候可以完成还原Excel的表现格式。

excel模板：

要求进入数据库的格式：

每张表示一条记录，每一行的数据变成一个字段，有合并单元格的用数字记录占行列数，不是合并的用 “数据，1,1；” 表示

2.思路

用poi组件读取excel，其关键思想就是先循环sheet数量，再循环每个sheet的每一行，在循环每行row的每一列cell，循环的时候判断此cell是否属于合并单元格区域，（关于一大片的合并区域，poi只会返回左上角第一个cell的数据，其余的全都返回空），如果属于合并区域，则判断是否返回了值，如果返回了，则说明是左上角第一个cell，记录此cell的值和占据行列数，如果不属于合并区域，直接返回值即可，最后再将返回值处理一下字符串

3.实现

先写个工具类 ExcelUtil.java(关于引包看最上面的上篇文章)

package com.mobileBass.utils.util;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.orm.jpa.vendor.OpenJpaDialect;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelUtil {

	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

	/**
	 * 导出Excel
	 * @param sheetName sheet名称
	 * @param title 标题
	 * @param values 内容
	 * @param wb HSSFWorkbook对象
	 * @return
	 */
	public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){

		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if(wb == null){
			wb = new HSSFWorkbook();
		}

		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow row = sheet.createRow(0);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		//声明列对象
		HSSFCell cell = null;

		//创建标题
		for(int i=0;i<title.length;i++){
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}

		//创建内容
		for(int i=0;i<values.length;i++){
			row = sheet.createRow(i + 1);
			if(values[i]!=null){
				for(int j=0;j<values[i].length;j++){
					//将内容按顺序赋给对应的列对象
					row.createCell(j).setCellValue(values[i][j]);
				}
			}else{
				continue;
			}

		}
		return wb;
	}


	/**
	 * 将流中的Excel数据转成List<Map>(读取Excel)
	 *
	 * @param in
	 *            输入流
	 * @param fileName
	 *            文件名（判断Excel版本）
	 * @return
	 * @throws Exception
	 */
	public static List<Map<String,Object>> readExcel(InputStream in, String fileName) throws Exception {
		// 根据文件名来创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;
		// 返回数据
		List<Map<String,Object>> resultList=new ArrayList<>();
		//循环多个工作表
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			Map<String,Object> result=new HashMap<>();
			sheet = work.getSheetAt(i);
			if (sheet == null)
				continue;
			//获取有合并单元格的区域
			List<CellRangeAddress> combineCellList=getCombineCellList(sheet);
			// 测试有几行数据是有表头数据的
			Boolean flag=true;
			int h=0;
			for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {
				row = sheet.getRow(j);
				// 遍历所有的列
				if(flag) {
					for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
						cell = row.getCell(y);
						Object v = getCellValue(cell);
						flag = false;
						if (v != null && !v.toString().equals("")) {
							flag = true;
							break;
						}
					}
					h++;
				}
			}
			//列数
			int colNum=0;
			//表头行数
			int h1=h-1;
			//表头数据
			List<List> list1=new ArrayList<>();
			//循环行，不要从0开始，防止前几行为空的情况
			for (int k = sheet.getFirstRowNum(); k < sheet.getFirstRowNum()+h1; k++) {
				List<Map<String,Object>> list=new ArrayList<>();
				row = sheet.getRow(k);
				//获取列数
				colNum=row.getLastCellNum()-row.getFirstCellNum();
				// 遍历所有的列
				for (int x = row.getFirstCellNum(); x < row.getLastCellNum(); x++) {
					Map<String,Object> map=new HashMap<>();
					cell = row.getCell(x);
					//表格cell的数据，合并的只有左上角一个有数据，其余全为空
					String v = getCellValue(cell).toString();
					//判断该cell是否为合同单元格中的一个
					Map<String,Object> isCombined=isCombineCell(combineCellList,cell,sheet);
					//如果是，则判断是否有值，有值的才添加到list中
					if((Boolean) isCombined.get("flag")){
						if(v!=null&&!v.equals("")){
							map.put("name",v);
							map.put("x",isCombined.get("mergedCol"));
							map.put("y",isCombined.get("mergedRow"));
							list.add(map);
						}
					//如果不是，则直接插入
					}else{
						map.put("name",v);
						map.put("x",1);
						map.put("y",1);
						list.add(map);
					}
				}
				list1.add(list);
			}

			int rowIndex=1;
			//处理数据拼接字符串
			for(int c=0;c<list1.size();c++){
				String s="";
				for(int d=0;d<list1.get(c).size();d++){
					Map<String,Object> map2= (Map<String, Object>) list1.get(c).get(d);
					s+=map2.get("name")+","+map2.get("x")+","+map2.get("y")+";";
				}
				result.put("row"+rowIndex++,s);
			}
			
			System.out.print(result);
			resultList.add(result);
		}

		//work.close();
		return resultList;
	}

	//获取合并单元格集合
	public static List<CellRangeAddress> getCombineCellList(Sheet sheet)
	{
		List<CellRangeAddress> list = new ArrayList<>();
		//获得一个 sheet 中合并单元格的数量
		int sheetmergerCount = sheet.getNumMergedRegions();
		//遍历所有的合并单元格
		for(int i = 0; i<sheetmergerCount;i++)
		{
			//获得合并单元格保存进list中
			CellRangeAddress ca = sheet.getMergedRegion(i);
			list.add(ca);
		}
		return list;
	}

	/**
	 * 判断cell是否为合并单元格，是的话返回合并行数和列数（只要在合并区域中的cell就会返回合同行列数，但只有左上角第一个有数据）
	 * @param listCombineCell  上面获取的合并区域列表
	 * @param cell
	 * @param sheet
	 * @return
     * @throws Exception
     */
	public  static Map<String,Object> isCombineCell(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet)
			throws Exception{
		int firstC = 0;
		int lastC = 0;
		int firstR = 0;
		int lastR = 0;
		String cellValue = null;
		Boolean flag=false;
		int mergedRow=0;
		int mergedCol=0;
		Map<String,Object> result=new HashMap<>();
		result.put("flag",flag);
		for(CellRangeAddress ca:listCombineCell)
		{
			//获得合并单元格的起始行, 结束行, 起始列, 结束列
			firstC = ca.getFirstColumn();
			lastC = ca.getLastColumn();
			firstR = ca.getFirstRow();
			lastR = ca.getLastRow();
			//判断cell是否在合并区域之内，在的话返回true和合并行列数
			if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
			{
				if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
				{
					flag=true;
					mergedRow=lastR-firstR+1;
					mergedCol=lastC-firstC+1;
					result.put("flag",true);
					result.put("mergedRow",mergedRow);
					result.put("mergedCol",mergedCol);
					break;
				}
			}
		}
		return result;
	}

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 *
	 * @param inStr
	 *            ,fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 *
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字
		if(cell!=null){
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_STRING:
					value = cell.getRichStringCellValue().getString();
					break;
				case Cell.CELL_TYPE_NUMERIC:
					if ("General".equals(cell.getCellStyle().getDataFormatString())) {
						value = df.format(cell.getNumericCellValue());
					} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
						value = sdf.format(cell.getDateCellValue());
					} else {
						value = df2.format(cell.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_BOOLEAN:
					value = cell.getBooleanCellValue();
					break;
				case Cell.CELL_TYPE_BLANK:
					value = "";
					break;
				case Cell.CELL_TYPE_ERROR:
					value="";
					break;
				default:
					break;
			}
		}else{
			value="";
		}
		return value;
	}

	public static void main(String[] args) throws Exception {
		File file = new File("D:\\通报模板(增加两列黄色内容)new.xlsx");
		FileInputStream fis = new FileInputStream(file);
		List<Map<String, Object>> ls = readExcel(fis, file.getName());
		//System.out.println(JSON.toJSONString(ls));
	}





}

controller中调用

@RequestMapping(value = "/insertTable", produces = "text/html;charset=utf-8")
@ResponseBody
public String insertTable(@RequestParam(value = "file") MultipartFile file) {
    Map<String,Object> result=new HashMap<>();
    List<Map<String,Object>> list=new ArrayList<>();
    try {
        String fileName = file.getOriginalFilename();
        //将multipartFile转为inputstream
        CommonsMultipartFile cFile = (CommonsMultipartFile) file;
        DiskFileItem fileItem = (DiskFileItem) cFile.getFileItem();
        InputStream inputStream = fileItem.getInputStream();
        Map<String,Object> params=new HashMap<>();
        list= ExcelUtil.readExcel(inputStream,fileName);

            for(int i=0;i<list.size();i++){
                commonService.insertTable(list.get(i));
            }

        result.put("msg", "成功！");
        result.put("success", true);
    } catch (Exception e) {
        e.printStackTrace();
        result.put("msg", "失败！");
        result.put("success", false);
    }
    return JSON.toJSONString(result);
}

4.总结

1.要判断是否属于合并单元格要先获取合并单元格集合，这个poi可以返回，方法是我的工具类的getCombineCellList方法

2.循环每个cell时都要判断一下是否在合并单元格集合内，如果是需要判断其值是否为空，空的话不要保存，就保存左上角那一个唯一有值的

3.如果不需要行列信息，在处理字符串时不拼行列数即可

A---区域获取合并跨行数
var a=sheet.Range["A1"].MergeArea.Rows.Count;

B---区域获取合并跨列数
var b=sheet.Range["B2"].MergeArea.Columns.Count;

获取合并单元格跨的格子数量
var c=sheet.Range["A1"].MergeArea.Cells.Count;

先上效果图：

先声明一个IWorkbook对象：

				IWorkbook workbook;
                //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                string fileExt = Path.GetExtension(fileName).ToLower();
                if (fileExt == ".xlsx") {
                    workbook = new XSSFWorkboo
                 }
                else if (fileExt == ".xls") {
                    workbook = new HSSFWorkbook();
                } else {
                    workbook = null;
                }
                if (workbook == null)  {
                    return;
                }

标题合并单元格，字体居中加粗，先定义单元格样式：

                ICellStyle cellStyle = workbook.CreateCellStyle();//声明样式
                cellStyle.Alignment = HorizontalAlignment.Center;//水平居中
                cellStyle.VerticalAlignment = VerticalAlignment.Center;//垂直居中
                IFont font = workbook.CreateFont();//声明字体
                font.Boldweight = (Int16)FontBoldWeight.Bold;//加粗
                font.FontHeightInPoints = 18;//字体大小
                cellStyle.SetFont(font);//加入单元格

其次实现单元格合并：

				IRow row0 = sheet.CreateRow(0);//创建行
                row0.HeightInPoints = 35;//行高
                ICell cell0 = row0.CreateCell(0);//创建单元格
                cell0.SetCellValue("XXXX年XX月对账单");//赋值
                cell0.CellStyle = cellStyle;//设置样式
                 sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtBody.Columns.Count-1));//合并单元格（第几行，到第几行，第几列，到第几列）

这样第一列的标题效果完成，接下来是一行的多个单元格合并，步骤如上，需要注意的是，单元格赋值的时候，下标要写每个合并单元格域的第一列，不然会被覆盖。
接下来是一个DataTable的读取，可以先计算每一列的列宽：

int[] arrColWidth = new int[dtBody.Columns.Count];//声明列宽数组
                foreach (DataColumn item in dtBody.Columns)//先存储列头的宽度
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int i = 0; i < dtBody.Rows.Count; i++)//遍历整张表，如果宽度大于列头宽度，则替换
                {
                    for (int j = 0; j < dtBody.Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(dtBody.Rows[i][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }

接下来填充数据：

				#region 填充表头
                IRow row = sheet.CreateRow(2);//先前占用了2行，表头从3开始
                for (int i = 0; i < dtBody.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dtBody.Columns[i].ColumnName);
                    sheet.SetColumnWidth(i, arrColWidth[i] * 300);
                    cell.CellStyle = dateStyle;
                }
                #endregion

                #region  填充表身
                for (int i = 0; i < dtBody.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 3);//表身从3开始
                    for (int j = 0; j < dtBody.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        if (dtBody.Columns[j].DataType.FullName == "System.Decimal")//数字类型的值单独的样式
                        {
                            cell.SetCellType(CellType.Numeric);
                            if (dtBody.Rows[i][j].ToString() == "")
                            {
                                cell.SetCellValue(0);
                            }
                            else
                            {
                                cell.SetCellValue(double.Parse(dtBody.Rows[i][j].ToString()));
                            }
                            cell.CellStyle = numberStyle;//数字样式
                        }
                        else
                        {
                            cell.SetCellValue(dtBody.Rows[i][j].ToString());
                            cell.CellStyle = initStyle;
                        }
                    }
                   #region 统计行
                    if (i == dtBody.Rows.Count - 1)//最后一行
                    {
                        IRow rowCount = sheet.CreateRow(i + 4);//追加一行统计列
                        sheet.AddMergedRegion(new CellRangeAddress(i+ 4, i+4 , 0, 5));//合并单元格（第几行，到第几行，第几列，到第几列）
                        ICell cellEnd1 = rowCount.CreateCell(6);//创建单元格
                        ICell cellEnd2 = rowCount.CreateCell(7);
                        ICell cellEnd3 = rowCount.CreateCell(8);
                        ICell cellEnd4 = rowCount.CreateCell(9);;
                        cellEnd1.SetCellValue("对帐总金额：");
                        cellEnd3.SetCellValue("确认总金额：");
                        cellEnd1.CellStyle = textStyle;
                        cellEnd3.CellStyle = textStyle;
                        cellEnd2.CellStyle = moneyStyle;
                        cellEnd4.CellStyle = moneyStyle;
                        sheet.SetColumnWidth(6, 16 * 256);//设置单元格宽度
                        sheet.SetColumnWidth(7, 14 * 256);
                        sheet.SetColumnWidth(8, 16 * 256);
                        sheet.SetColumnWidth(9, 14 * 256);
                        string CellFormulaString1= "sum(I4:I"+ (dtBody.Rows.Count + 3) + ")"; //计算表达式，行统计： sum（A1:C1）,列统计：sum(B1:B10)
                        string CellFormulaString2= "sum(J4:J"+ (dtBody.Rows.Count + 3) + ")"; //计算表达式
                        sheet.GetRow(dtBody.Rows.Count + 3).GetCell(7).SetCellFormula(CellFormulaString1);//赋值表达式
                        sheet.GetRow(dtBody.Rows.Count + 3).GetCell(9).SetCellFormula(CellFormulaString2);
                    }
                    #endregion
                }

                #endregion

统计行中moneyStyle单元格样式中需要添加“￥”字符，网上搜了很久的方法没有找到，自己无意中试出来的：

				ICellStyle moneyStyle= workbook.CreateCellStyle();
                IFont font1 = workbook.CreateFont();
                font1.Color = (Int16)FontColor.Red;
                moneyStyle.SetFont(font1);
                IDataFormat dataformat = workbook.CreateDataFormat();
                style3.DataFormat = dataformat.GetFormat("￥0.00"); ;

填充完数据之后，需要将其写入文件中，然后导出：

				MemoryStream stream = new MemoryStream(); 
                workbook.Write(stream);
                var buf = stream.ToArray();//转为字节数组 
                //创建文件
                using (FileStream fs = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write))//savePath为文件保存路径
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }

至此，后端完成