Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#3.5
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
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-Mar-12 3:06am

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.140821.2 | Last Updated 1 Mar 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100