Click here to Skip to main content
12,634,982 members (31,606 online)
Rate this:
 
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 4: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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.161208.2 | Last Updated 1 Mar 2012
Copyright © CodeProject, 1999-2016
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