poi excel 怎么合并单元格

//poi-3.7.jar

/**

* 合并单元格处理--加入list

*

* @param sheet

* @return

*/

public void getCombineCell(HSSFSheet sheet, List<CellRangeAddress>list) {

// 获得一个 sheet 中合并单元格的数量

int sheetmergerCount = sheet.getNumMergedRegions()

// 遍历合并单元格

for (int i = 0i <sheetmergerCounti++) {

// 获得合并单元格加入list中

CellRangeAddress ca = sheet.getMergedRegion(i)

list.add(ca)

}

}

/**

* 判断单元格是否为合并单元格

*

* @param listCombineCell

*存放合并单元格的list

* @param cell

*需要判断的单元格

* @param sheet

*sheet

* @return

*/

public static Boolean isCombineCell(List<CellRangeAddress>listCombineCell,

HSSFCell cell, HSSFSheet sheet) {

int firstC = 0

int lastC = 0

int firstR = 0

int lastR = 0

for (CellRangeAddress ca : listCombineCell) {

// 获得合并单元格的起始行, 结束行, 起始列, 结束列

firstC = ca.getFirstColumn()

lastC = ca.getLastColumn()

firstR = ca.getFirstRow()

lastR = ca.getLastRow()

if (cell.getColumnIndex() <= lastC&&cell.getColumnIndex()>= firstC) {

if (cell.getRowIndex() <= lastR &&cell.getRowIndex() >= firstR) {

return true

}

}

}

return false

}

}

添加区域，合并单元格

[c-sharp] view plaincopyprint?

1.Region region = new Region((short)rowFrom,(short)columnFrom,(short)rowTo

2.(short)columnTo)//合并从第rowFrom行columnFrom列

3.sheet.addMergedRegion(region)// 到rowTo行columnTo的区域

4.//得到所有区域

5.sheet.getNumMergedRegions()

  /**

  * 

  * @param context

  * @param dictionary

  * @param rows 数据行

  * @param fileName 文件名

  * @param fields 列名

  * @param fieldsName 字段名

  */

 private void outExcelFile(HttpContext context,

   IContextDictionary dictionary, DataRowCollections rows,

   String fileName, List<String> fields, List<String> fieldsName) {

  int cellSize = fields.size()

  if(cellSize == 0){

   LogManager.debug("没有指定列头信息,无法导出Excel文件!")

   return

  }

  //============创建样式    start

  HSSFWorkbook workbook = new HSSFWorkbook()

  HSSFSheet sheet = workbook.createSheet()

  //列头字体样式

        HSSFFont headerFont = workbook.createFont()

        headerFont.setFontName("宋体")

        headerFont.setFontHeightInPoints((short) 12)

        headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)

        //列头样式

        HSSFCellStyle headerStyle = workbook.createCellStyle()

        headerStyle.setFont(headerFont)

        headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)// 左右居中

        headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)// 上下居中

        headerStyle.setLocked(true)

        headerStyle.setWrapText(true)

        //标题样式

        HSSFFont titleFont = workbook.createFont()

        titleFont.setFontName("宋体")

        titleFont.setFontHeightInPoints((short) 15)

        titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)

        

        HSSFCellStyle titleStyle = workbook.createCellStyle()

        titleStyle.setFont(titleFont)

        titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER)

        titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)

        titleStyle.setLocked(true)

        titleStyle.setWrapText(true)

        

        //普通单元格字体样式

        HSSFFont cellFont = workbook.createFont()

        cellFont.setFontName("宋体")

        cellFont.setFontHeightInPoints((short)12)

        //普通单元格样式

        HSSFCellStyle cellStyle = workbook.createCellStyle()

        cellStyle.setFont(cellFont)

        cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT)// 左右居中

        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)// 上下居中

        cellStyle.setLocked(true)

        cellStyle.setWrapText(true)

        //============创建样式    end

        

        //设置序号列、列宽和标题行     start

        HSSFRow titleRow = sheet.createRow(0)

        titleRow.setHeightInPoints(50)

        HSSFCell titleCell = titleRow.createCell(0)

        titleCell.setCellValue(new HSSFRichTextString(fileName))

        titleCell.setCellStyle(titleStyle)

        //sheet.addMergedRegion(new Region(0,(short)0,0,(short)cellSize))//合并单元格--方法过时                                                                                                                                                                                                                                                                          

        //CellRangeAddress  起始行 结束行 起始列 结束列

        sheet.addMergedRegion(new CellRangeAddress(0,0,(short)0,(short)cellSize))//合并单元格

       

        //显示总的数据个数 start

        HSSFRow countRow = sheet.createRow(1)

        countRow.setHeightInPoints(40)

        HSSFCell countCell = countRow.createCell(0)

        countCell.setCellValue(new HSSFRichTextString("共计专项检查（"+rows.size()+"）项"))

        countCell.setCellStyle(headerStyle)

        sheet.addMergedRegion(new CellRangeAddress(1,1,(short)0,(short)cellSize))//合并单元格

  //显示总的数据个数 end 

        

        HSSFRow headerRow = sheet.createRow(2)

        headerRow.setHeightInPoints(35)

        HSSFCell headerCell = null

        

        //序号

        int startIndex = 0 

     headerCell = headerRow.createCell(0)

  sheet.setColumnWidth(0, 2000)

  headerCell.setCellValue(new HSSFRichTextString("序号"))

  

  headerCell.setCellStyle(headerStyle)

  startIndex ++ 

  

  //列头

  for(int i = 0 i < cellSize i ++){

   sheet.setColumnWidth(startIndex + i, 7000)

   headerCell = headerRow.createCell(startIndex + i)

   headerCell.setCellValue(new HSSFRichTextString(fields.get(i)))

   headerCell.setCellStyle(headerStyle)

  }

  //设置序号列、列宽和标题行   end

  

  //文件正文 start

  int rowNum = 1

  int rowIndex = 0

  HSSFRow row = null

  List<Integer[]> cellRangeLst = new ArrayList<Integer[]>(0)

  Integer [] arr = null

  int l = 0

  String orgName = "" 

  for(int j = 2 j<rows.size()+2 j++){//循环行

   DataRow dataRow = rows.get(rowIndex) //对应数据库字段

   HSSFCell cell = null

   row = sheet.createRow(j + 1)

         row.setHeightInPoints(55)

   //序号

   cell = row.createCell(0)

   cell.setCellValue(rowNum++)

      cell.setCellStyle(cellStyle)

      

      if(StringHelper.isNullOrEmpty(orgName)){

       arr = new Integer[2]

       arr[0] = j + 1

       l =j + 1

       orgName = dataRow.getString("ORGNAME")

      }else{

       if(!orgName.equals(dataRow.getString("ORGNAME"))){

        arr[1] = j

        cellRangeLst.add(arr)

        sheet.addMergedRegion(new CellRangeAddress(l,j,1,1))

        arr = new Integer[2]

        l = j+1

        orgName = dataRow.getString("ORGNAME")

       }

       

       if(rowIndex == rows.size() - 1){

        arr[1] = j+1

        cellRangeLst.add(arr)

        sheet.addMergedRegion(new CellRangeAddress(l,j+1,1,1))

       }

      }

      

      for(int k = 0 k < cellSize k++){

       cell = row.createCell(k + startIndex)

       String column = fieldsName.get(k) //对应数据库字段

       String value = ""

       if("APSJ".equals(column)){

        value = getAPSJValue(dataRow.getString(column))

       }else{

        value = dataRow.getString(column)

       }

       cell.setCellValue(new HSSFRichTextString(value))

          cell.setCellStyle(cellStyle)

      }

      rowIndex ++

  }

  //文件正文 end

  

  //for(Integer[] te : cellRangeLst){

  // sheet.addMergedRegion(new CellRangeAddress(te[0],te[1],1,1))//合并处室单元格

  //}

  

  //下载

  HttpServletResponse response = context.getResponse()

  

  response.setContentType("application/x-downloadcharset=UTF-8")

  String title = "export"

  try {

   title = java.net.URLEncoder.encode(fileName, "UTF-8")

  } catch (UnsupportedEncodingException e) {

   e.printStackTrace()

  }

  response.addHeader("Content-Disposition", "attachmentfilename=" + title + ".xls")

  try {

   OutputStream out = response.getOutputStream()

   workbook.write(out)

   out.flush()

   out.close()

  } catch (IOException e) {

   e.printStackTrace()

  }

 }

//参考一下吧

---