Click here to Skip to main content
15,886,724 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I created an Excel file from a datatable but i have a problem to resize column, this is the first problem. The second is how can i write in a specific cell(for example the cell(1,2) I need this for the header for example).Can you help me?.This is my code
C#
DataTable dt = (DataTable) ViewState["CurrentTable"];



            Workbook book = new Workbook();
            WorksheetStyle style = book.Styles.Add("Entete");
            style.Font.Bold = true;
            style.Font.Color = "White";
            style.Alignment.Horizontal = StyleHorizontalAlignment.Center;
            style.Interior.Color = "Blue";
            style.Interior.Pattern = StyleInteriorPattern.Solid;

            //create the first sheet
            Worksheet sheet = book.Worksheets.Add("Detail Seance");
            
            //The second sheet
            Worksheet sheetConf = book.Worksheets.Add("Total");

         
            //The header
            WorksheetRow row = sheet.Table.Rows.Add();
            row.Cells.Add(new WorksheetCell("Historique Exo"){ MergeAcross = 5 });
            
            row = sheet.Table.Rows.Add();
            Excel.Range range;
            
            foreach (DataColumn dc in dt.Columns)
            {
                row.Cells.Add(new WorksheetCell(dc.ColumnName, DataType.String, "Entete"));
                row.AutoFitHeight
            }

            int j;
            foreach (DataRow dr in dt.Rows)
            {
                row = sheet.Table.Rows.Add();
                for (j = 0; j < dt.Columns.Count; j++)
                {
                      row.Cells.Add(new WorksheetCell(dr[j].ToString()));
                    
                }
            }
            
            //save Excel file
            book.Save(Response.OutputStream); 

Thanks,
Posted

1 solution

Couple of things:

- When you create an Excel workbook using automation, 3 sheets are available by default, so you do not actually need to add sheets to the new workbook unless necessary.
- Similarly, the worksheet(s) have the rows already defined (64k in case of version 2003 and ~1 million in versions 2007 and above), so there is no need to add rows or cells


You can reference the whole worksheet as a 2 dimensional array (Row,Column) and write/read direct to the cells as:
[Workbook object].Sheets(1).Cells(R,C) = Value


HTH
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900