Regarding Merging Cells

[bindu]

Hi,

I am trying to merge cells from D To G columns in excel from row 27.I am importing a data table here according to number of rows i am merging cells in excel.Here is the code i wrote for it.But,the cells are not merged can you please look into it and let me know.

int s2 = 0;

string cell2 = null;

string cell1 = null;

Aspose.Cells.Range range;

for (int i = 0; i < (dt.Rows.Count - 1); i++)

{

s2 = 27 + i;

cell1 = "D" + s2.ToString();

cell2 = "G" +s2.ToString();

range = cells.CreateRange(cell1, cell2);

range.Merge();

}

I also did in this way

int s2 = 27 + (dt.Rows.Count-1);

string cell2 = "H" + s2.ToString();

range=cells.CreateRange("A27",cell2);

range.Merge();

Even though cells are merged the data in it is disappearing and there ae only empty cells

Thanks in Advance,

Bindu.

[Amjad_Sahi]

Hi,

I have tested your scenario and sample code, it works abs fine.

Even though cells are merged the data in it is disappearing and there ae only empty cells

It is to be noted here, when you merge some cells (in a range), only one cell (merged cell) is popped out. A Merged Cell is basically a single cell that is created by combining two or more selected cells. The cell reference for a merged cell is the upper-left cell in the original selected range. Mind you, if there is data in other cells, the data will be deleted.

Please check for your reference: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/merging-unmerging-cells-in-the-worksheet.html

If you still find your issue, kindly post your template file, we will check it soon.

Thank you.

[bindu]

Hi,

I am trying to rexplain my problem.If i use the below code i could merge cells from D27 to G.(dt.Rows.Count).Cells with in given range are merged .Now nwxt i am importing the datattable to the cells from A27 to H(dt.Rows.Count).You can refer to code down.My problem here is that The data is being imported but the cells from D-G in every row is empty and there is no datat imported there.I have a table with 5 columns .Fourth column is merged from D-G. I am getting data imported into all columns but column four which is merged is empty and nothing is imported into it.Can you please check this.

int s2 = 0;

string cell2 = null;

Aspose.Cells.Range range;

cell2 = "G" + (27 + (dt.Rows.Count - 1)).ToString();

range = cells.CreateRange("D27",cell2);

range.Merge();

worksheet.Cells.ImportDataTable(dt, false, "A27");

and aslo the cells out of range are alos getting merged and there is slight distortion.I am also attaching the template can you please check this and let me know.

Thanks in advance,

Bindu.

[Amjad_Sahi]

Hi Bindu,

Thanks for providing us the template file.

There is slight error in your scenario. You are importing datatable columns to the worksheet but there is a column which is merged (D-G........as you described), and after the column there is another column H. So, your data would be divided into five columns if I am right i.e.., "A", "B", "C", "D-G" and "H". When the data is imported using the code i.e.., worksheet.Cells.ImportDataTable(dt, false, "A27"); some new (blank )rows would be inserted and data would be imported to those rows, so, apparently A-E columns would be filled and F-H columns would be continuously empty. And also, you are getting "the cells out of range are also getting merged and there is slight distortion" I think, that's why, below the data table, you are getting those merged range of cells, it not it?

I think you can perform your task in the following ways.

1). Fill the data cell by cell using your own code from datatable after merging the cells. Please make sure that you are not filling the data into the merged/hidden cells (E-G cells) in the column

2). You may try to use Cells.ImportDataColumn method to import the columns one by one into the desired cells to implement your scenario. I use the following sample code which works fine, you may try to consult it. The input and output files are attached here.

Sample code:

Workbook workbook = new Workbook();
workbook.Open("f:\\test\\mergeproblem.xls");
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;

DataTable dataTable1 = new DataTable();

dataTable1.Columns.Add("Cus_IP",typeof(string));
dataTable1.Columns.Add("FUND#",typeof(string));
dataTable1.Columns.Add("Material_Proxy",typeof(string));
dataTable1.Columns.Add("Fund_Name",typeof(string));
dataTable1.Columns.Add("Quantity",typeof(string));

for (int i = 0; i <= 40; i++)
{
DataRow dr = dataTable1.NewRow();
for (int j = 0; j < 5; j++)
{
dr[j] = "AsposeTest" + i.ToString() + j.ToString();
}
dataTable1.Rows.Add(dr);
}


int s2 = 0;
string cell2 = null;
string cell1 = null;
Aspose.Cells.Range range;
for (int i = 0; i <= (dataTable1.Rows.Count - 1); i++)
{

s2 = 28 + i;

cell1 = "D" + s2.ToString();
cell2 = "G" +s2.ToString();
range = cells.CreateRange(cell1, cell2);
range.Merge();

}

int index = 0;

for (int i = 0; i < dataTable1.Columns.Count; i++)
{
//Since D-G columns are merged so, we have to mange that the last column should be filled
//on H column.
if (i > 3)
{
sheet.Cells.ImportDataColumn(dataTable1, false, 27, 7, i, false);
}
sheet.Cells.ImportDataColumn(dataTable1, false, 27, index, i, false);
index = index + 1;
}


sheet.AutoFitColumns();

workbook.Save("f:\\test\\mergecellsrangetest.xls");

3). You may try to use Aspose.Cells' Smart Marker feature, set/place smart markers into different cells as column headers and process the markers to fill the data into the cells, check for your reference: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/smart-markers.html

Thank you.