poi excel 怎么合并单元格???

[penghaozhong]

上面是效果图,最终的图
我已经把课程类型和街道分院的单元格排好了,效果图如下 :




现在要排所属单位的单元格,该怎么排,排列时需要排序的按照中文A_Z进行排序,街道分院,所属单位 和授课老师 需要排序,相同的话就进行单元格合并,现在我说下我的现实思路.
每一条课程消息是一个记录, 整个是个List,首先我对List进行排序 ,按照街道分院来排序,排完顺序后,把相同的街道分院进行合并. 然后所属单位要针对街道分院才排序,例如 所属单位是在 a 街道分院的话,对应里面的3条记录进行中文排序,有相同的话进行合并.这个excel都弄了3天了,还没排到一半 真是很伤脑壳,忘大家能给点意见

[宁波朱超]

[Quote=引用 1 楼 woshigaoshou980 的回复:]
合并单元表格 右键 属性 里面有
[/Quote]

你高笑么。。。

[xuelu198708]

sheet.mergeCells(col_start, row_start, col_end, row_end);
然后添加数据
jxl.write.Number labelNF = new jxl.write.Number(col, row,
(Double) data, format);
sheet.addCell(labelNF);

[希望对你有帮助]

合并单元表格 右键 属性 里面有

[penghaozhong]

[Quote=引用 3 楼 xuelu198708 的回复:]
sheet.mergeCells(col_start, row_start, col_end, row_end);
然后添加数据
jxl.write.Number labelNF = new jxl.write.Number(col, row,
(Double) data, format);
sheet.addCell(labelNF);
[/Quote]
这个例子都懂 ,现在遇到的问题是第二次填入excel某列中的值后,第一次填入的值不见了,
看下代码哈 用的poi 的excel
HSSFWorkbook workbook = new HSSFWorkbook();

List<HSSFSheet> sts = new ArrayList<HSSFSheet>();
HashSet<SubjectType> subts = new HashSet<SubjectType>();

for (Subject sb : subs) {
subts.add(sb.getSubjectType());

}

Iterator<SubjectType> itsb = subts.iterator();
int k = 0;
while (itsb.hasNext()) {

HSSFSheet sheet1 = workbook.createSheet();
workbook.setSheetName(k, itsb.next().getName());
k++;
sts.add(sheet1);
}

// 表头样式

HSSFCellStyle styleTi = workbook.createCellStyle();
HSSFFont fontTi = workbook.createFont();
fontTi.setFontHeightInPoints((short) 14);
styleTi.setFillForegroundColor(HSSFColor.WHITE.index);
styleTi.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
styleTi.setBorderBottom(HSSFCellStyle.BORDER_THIN);
styleTi.setBorderLeft(HSSFCellStyle.BORDER_THIN);
styleTi.setBorderRight(HSSFCellStyle.BORDER_THIN);
styleTi.setBorderTop(HSSFCellStyle.BORDER_THIN);
styleTi.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
styleTi.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
styleTi.setFont(fontTi);

HSSFCellStyle style = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName("宋体");
style.setFillForegroundColor(HSSFColor.WHITE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
style.setFont(font);

// 创建表头
{
HSSFRow row = null;
HSSFCell cell = null;
List<Subject> sheetSubs = null;

if (sts.size() > 0) {
for (int j = 0; j < sts.size(); j++) {

sheetSubs = new ArrayList<Subject>();
sts.get(j).setColumnWidth((short) 0, (short) 6000);
sts.get(j).setColumnWidth((short) 1, (short) 6000);
sts.get(j).setColumnWidth((short) 2, (short) 6000);
sts.get(j).setColumnWidth((short) 3, (short) 10000);
sts.get(j).setColumnWidth((short) 4, (short) 6000);
sts.get(j).setColumnWidth((short) 5, (short) 10000);
row = sts.get(j).createRow(0);
cell = row.createCell((short) 0);

cell.setCellStyle(styleTi);
cell.setCellValue("课程类型");
cell = row.createCell((short) 1);
cell.setCellStyle(styleTi);
cell.setCellValue("课程名称");
cell = row.createCell((short) 2);
cell.setCellStyle(styleTi);
cell.setCellValue("任课老师");
cell = row.createCell((short) 3);
cell.setCellStyle(styleTi);
cell.setCellValue("所属单位");
cell = row.createCell((short) 4);
cell.setCellStyle(styleTi);
cell.setCellValue("联系电话");
cell = row.createCell((short) 5);
cell.setCellStyle(styleTi);
cell.setCellValue("街道分院");

int sheet = 0;

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

if (subs.get(i).getSubjectType().getName().equals(
workbook.getSheetName(j))) {

row = sts.get(j).createRow(sheet + 1);
cell = row.createCell((short) 0);
cell.setCellStyle(style);
cell.setCellValue(subs.get(i).getSubjectType()
.getName());
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell.setCellValue(subs.get(i).getName());
cell = row.createCell((short) 2);
cell.setCellStyle(style);
cell.setCellValue(subs.get(i).getTeacherName());
cell = row.createCell((short) 3);
cell.setCellStyle(style);
cell.setCellValue(subs.get(i).getOfOrganization());
cell = row.createCell((short) 4);
cell.setCellStyle(style);
cell.setCellValue(subs.get(i).getTelephone());
cell = row.createCell((short) 5);
cell.setCellStyle(style);
cell.setCellValue(subs.get(i).getOrganization()
.getName());

sheetSubs.add(subs.get(i));

sheet++;
}

}

// 构造所在单位的List
List<List<Subject>> ofOrgLists = new ArrayList<List<Subject>>();

List<Subject> strList = new ArrayList<Subject>();
List<Subject> strAllt = new ArrayList<Subject>();

int sheetOS = 1;// 街道分院的开始
int sheetOE = 0;// 结束
boolean isE = false;
boolean isS = false;
Subject sj = null;


// 合并街道分院
if (sheetSubs.size() > 0) {

for (int ss = 0; ss < sheetSubs.size(); ss++) {
strAllt.add(sheetSubs.get(ss));
if (ss > 0) {

// 街道分院
if (sheetSubs.get(ss).getOrganization()
.getName().equals(
sheetSubs.get(ss - 1)
.getOrganization()
.getName())) {
isS = true;
} else { // 没有相同的便结束
sheetOE = ss;
isE = true;
}
if (isE) {

sts.get(j).addMergedRegion(
new Region(sheetOS, (short) 5,
sheetOE, (short) 5));
isE = false;
isS = false;
sheetOS = sheetOE + 1;
// 构造所属单位List
sj = strAllt.remove(strAllt.size() - 1);

List<Subject> newLi = new ArrayList<Subject>(
strAllt);

Collections.sort(newLi, new Comparator() {
@Override
public int compare(Object o1, Object o2) {
String str3 = ((Subject) o1)
.getOfOrganization();
String str4 = ((Subject) o2)
.getOfOrganization();
Comparator cmp1 = Collator
.getInstance(java.util.Locale.CHINA);
return cmp1.compare(str3, str4);
}
});

ofOrgLists.add(newLi);
strAllt.clear();
strAllt.add(sj);
}
}

// 所属单位只有一条记录
if (sheetSubs.size() == 1) {
ofOrgLists.add(strAllt);
}

}
if (isS) {
sts.get(j).addMergedRegion(
new Region(sheetOS, (short) 5, sheetSubs
.size(), (short) 5));
// 所属单位都一样
List<Subject> newLi1 = new ArrayList<Subject>(
strAllt);

Collections.sort(newLi1, new Comparator() {
@Override
public int compare(Object o1, Object o2) {
String str3 = ((Subject) o1)
.getOfOrganization();
String str4 = ((Subject) o2)
.getOfOrganization();
Comparator cmp1 = Collator
.getInstance(java.util.Locale.CHINA);
return cmp1.compare(str3, str4);
}
});

ofOrgLists.add(newLi1);
}

}

// 合并课程类型
sts.get(j).addMergedRegion(
new Region(1, (short) 0, sheet, (short) 0));



// 合并所属单位
int sheetOf = 0;
// for (List<Subject> li : ofOrgLists) {
//
// for (Subject s : li) {
// if (s.getSubjectType().getName().equals(
// workbook.getSheetName(j))) {
//
// row = sts.get(j).createRow(sheetOf + 1);
// cell = row.createCell((short) 3);
// cell.setCellStyle(style);
// cell.setCellValue(s.getOfOrganization());
// sheetOf++;
// }
//
// }
//
// }

}

}

}

[penghaozhong]

[Quote=引用 3 楼 xuelu198708 的回复:]
sheet.mergeCells(col_start, row_start, col_end, row_end);
然后添加数据
jxl.write.Number labelNF = new jxl.write.Number(col, row,
(Double) data, format);
sheet.addCell(labelNF);
[/Quote]

第一次写入 街道分院,根据名字排序,相同名字的合并单元格, 第二次在写入所属单位 ,所属单位是同一个街道分院的 ,进行排序 后合并单元格 但是在二次写如所属单位后,第一次写入的值 不见了

[wxslzmb]

POI中可能会用到一些需要设置EXCEL单元格格式的操作小结：

先获取工作薄对象:


Java代码
HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();
HSSFWorkbook wb = new HSSFWorkbook();

HSSFSheet sheet = wb.createSheet();

HSSFCellStyle setBorder = wb.createCellStyle();

一、设置背景色：

Java代码
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

二、设置边框:


Java代码
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

三、设置居中:

Java代码
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中

四、设置字体:


Java代码
HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小

HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式
HSSFFont font = wb.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 16);//设置字体大小

HSSFFont font2 = wb.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);

setBorder.setFont(font);//选择需要用到的字体格式

五、设置列宽:


Java代码
sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值
sheet.setColumnWidth(0, 3766); //第一个参数代表列id(从0开始),第2个参数代表宽度值

六、设置自动换行:


Java代码
setBorder.setWrapText(true);//设置自动换行
setBorder.setWrapText(true);//设置自动换行

七、合并单元格:


Java代码
Region region1 = new Region(0, (short) 0, 0, (short) 6);

//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
sheet.addMergedRegion(region1);
Region region1 = new Region(0, (short) 0, 0, (short) 6);

//参数1：行号 参数2：起始列号 参数3：行号 参数4：终止列号
sheet.addMergedRegion(region1);

附一个完整的例子：


Java代码
package cn.com.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.CellStyle;

import java.io.FileOutputStream;

import javax.servlet.http.HttpServlet;

public class CreateXL extends HttpServlet {
/** Excel 文件要存放的位置，假定在D盘下 */
public static String outputFile = "c:\\test.xls";

private void cteateCell(HSSFWorkbook wb, HSSFRow row, short col, String val) {
HSSFCell cell = row.createCell(col);
// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(val);
HSSFCellStyle cellstyle = wb.createCellStyle();
cellstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
cell.setCellStyle(cellstyle);
}

public static void main(String argv[]) {
try {
// 创建新的Excel 工作簿
HSSFWorkbook workbook = new HSSFWorkbook();

// 设置字体
HSSFFont font = workbook.createFont();
// font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 14);

// HSSFFont font2 = workbook.createFont();
// font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// font.setFontHeightInPoints((short)14);

// 设置样式
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// HSSFCellStyle cellStyle2= workbook.createCellStyle();
// cellStyle.setFont(font2);
// cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

// 在Excel工作簿中建一工作表，其名为缺省值
// 如要新建一名为"月报表"的工作表，其语句为：
HSSFSheet sheet = workbook.createSheet("月报表");
CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0,
11);
sheet.addMergedRegion(cellRangeAddress);

//第一行
// 在索引0的位置创建行（最顶端的行）
HSSFRow row = sheet.createRow(0);
// 在索引0的位置创建单元格（左上端）
HSSFCell cell = row.createCell(0);
// 定义单元格为字符串类型
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(cellStyle);
// 在单元格中输入一些内容
cell.setCellValue(new HSSFRichTextString("北京亿卡联科技发展有限公司小区门禁维修月报表"));

//第二行
cellRangeAddress = new CellRangeAddress(1, 1, 3, 6);
sheet.addMergedRegion(cellRangeAddress);
row = sheet.createRow(1);
HSSFCell datecell = row.createCell(3);
datecell.setCellType(HSSFCell.CELL_TYPE_STRING);
datecell.setCellStyle(cellStyle);
datecell.setCellValue("时间间隔xxxxx");

cellRangeAddress = new CellRangeAddress(1, 1, 9,
10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("单位：元");

//第三行
row=sheet.createRow(2);
row.createCell(0).setCellValue("一、");
row.createCell(1).setCellValue("基本资料");

//第4行
row=sheet.createRow(3);
row.createCell(1).setCellValue("小区名称：");
cellRangeAddress=new CellRangeAddress(3,3,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");

//第5行
row=sheet.createRow(4);
row.createCell(1).setCellValue("座落地点：");
cellRangeAddress=new CellRangeAddress(4,4,2,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("xxxxx");

//第6行
row=sheet.createRow(5);
row.createCell(1).setCellValue("建成年月：");
cellRangeAddress=new CellRangeAddress(5,5,2,4);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(2).setCellValue("年月日：xxxxx");
row.createCell(5).setCellValue("联系人");
cellRangeAddress=new CellRangeAddress(5,5,6,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(6).setCellValue("XXX");
row.createCell(9).setCellValue("电话");
cellRangeAddress=new CellRangeAddress(5,5,10,11);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(10).setCellValue("XXX");

//第7行
row=sheet.createRow(6);
row.createCell(1).setCellValue("住户：");
row.createCell(2).setCellValue("(XX)");
row.createCell(3).setCellValue("(户)");
cellRangeAddress=new CellRangeAddress(6,6,4,5);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(4).setCellValue("共计（ ）");
row.createCell(6).setCellValue("幢");
cellRangeAddress=new CellRangeAddress(6,6,7,8);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("发卡张数");
cellRangeAddress=new CellRangeAddress(6,6,9,10);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(9).setCellValue("xxxx");


//第9行
row=sheet.createRow(8);
row.createCell(0).setCellValue("二、");
cellRangeAddress=new CellRangeAddress(8,8,1,2);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(1).setCellValue("维修用材料台账");
row.createCell(6).setCellValue("三、");
cellRangeAddress=new CellRangeAddress(8,8,7,9);
sheet.addMergedRegion(cellRangeAddress);
row.createCell(7).setCellValue("维修工时记录");
//第10行
row=sheet.createRow(9);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");

row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");

//填充数据
for (int i = 0; i < 10; i++) {
row=sheet.createRow(9+i+1);
row.createCell(0).setCellValue("日期");
row.createCell(1).setCellValue("维修事项");
row.createCell(2).setCellValue("材料清单");
row.createCell(3).setCellValue("数量");
row.createCell(4).setCellValue("单价");
row.createCell(5).setCellValue("材料金额");

row.createCell(7).setCellValue("日期");
row.createCell(8).setCellValue("技工");
row.createCell(9).setCellValue("工时数");
row.createCell(10).setCellValue("单价");
row.createCell(11).setCellValue("工时金额");
}


//第n+10行
row=sheet.createRow(9+10+1);
//cellRangeAddress=new CellRangeAddress(19,19,0,4);
//sheet.addMergedRegion(cellRangeAddress);
row.createCell(0).setCellValue("累计:");
row.createCell(1).setCellValue("xxx");
row.createCell(7).setCellValue("累计:");
row.createCell(8).setCellValue("xxx");




// 新建一输出文件流
FileOutputStream fOut = new FileOutputStream(outputFile);
// 把相应的Excel 工作簿存盘
workbook.write(fOut);
fOut.flush();
// 操作结束，关闭文件
fOut.close();
System.out.println("文件生成...");
} catch (Exception e) {
System.out.println("已运行 xlCreate() : " + e);
}
}
}

[xieshengjun2009]

查帮助文档！

[a797749]

我想看看回复

[LIWANGQIAN]

楼主现在解决了么？我现在也遇到这个问题 就是将一列中相同的值进行合并·······以前没用过poi····能帮下忙么··

[旗木-卡卡西]

8楼的去死吧