locked
Creating excel sheet RRS feed

  • Question

  • Hi,

    I want create a excel sheet which contains multiple columns like "ID", "Title", "Description", "Owner".

    Then add the values to the excel at the run time. Please do let me know how can I achieve.

    Kindly waiting for your response.

    Thanks,

    Santosh 

    Tuesday, August 11, 2020 8:41 AM

All replies

  • Hello,

    If working with .xlsx rather than .xls look at SpreadSheetLight (free).

    To add a WorkSheet there is a method AddWorkSheet.

    private void Example(string fileName, string worksheetName)
    {
        using (var document = new SLDocument(fileName))
        {
            document.AddWorksheet(worksheetName);
            document.Save();
        }
    }
    

    Here I'm importing a DataTable and styling the headers, each DataColumn has it's caption set and used for headers.

    public static void ImportDataTableIntoExistingFile(string fileName, DataTable dataTable)
    {
    
        using (var document = new SLDocument(fileName))
        {
            /*
             * Set header style
             */
            var style = document.CreateStyle();
            style.Font.Bold = true;
            style.Font.FontColor = Color.White;
            style.Fill.SetPattern(PatternValues.Solid, 
                Color.Black, Color.Black);
    
            /*
             * Here I set the Caption property of each column
             */
            for (int index = 0; index < dataTable.Columns.Count; index++)
            {
                document.SetCellValue(SLConvert.ToCellReference(1, index +1), 
                    dataTable.Columns[index].Caption);
    
                document.SetCellStyle(1, index + 1, style);
            }
    
            
            document.ImportDataTable(2, 1, dataTable, false);
            document.RenameWorksheet("Sheet1", "Customers");
            document.Save();
            
        }
    
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, August 11, 2020 11:00 AM
  • Thank you so much for the response.

    Can you please let me know what exactly is SLDocument ?

    I could not get ImportDataTable in Excel.Worksheet.

    Kindly waiting for your response.

    Tuesday, August 11, 2020 11:59 AM
  • Hello,

    Everything I provided needs this package, nothing I presented references Microsoft.Office.Interop.Excel which it sounds like you are working with, Excel automation which has many disadvantages to a library such as SpreadSheetLight, or EPPlus (has a free and paid for version).

    SLDocument encapsulates the main properties and method to create and manipulate a spreadSheet in the library SpreadSheetLight. 


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, August 11, 2020 12:27 PM
  • Hi

    where you want to create the excel .The environment is windows forms or web app ,

    or

    whether you want to export / import excel

    Thanks and regards


    Tuesday, August 11, 2020 5:49 PM
  • Thanks again.

    Unfortunately SLDocument does not take string parameter. But you are passing filename .

    How exactly I have to create new instance of SLDocument ?

    Kindly waiting for your response.

    Wednesday, August 12, 2020 4:48 AM

  • Hi Santosh, 
    You can add Microsoft.Office.Interop.Excel reference via following steps:
    Right-click on the project ->Add Reference ->Assemblies-> Microsoft.Office.Interop.Excel-> OK
    Then you can store the value into the DataTable and then export to Excel.
    Here is code example you can refer to.

    private void Form1_Load(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application excel;
        Microsoft.Office.Interop.Excel.Workbook worKbooK;
        Microsoft.Office.Interop.Excel.Worksheet worKsheeT;
        Microsoft.Office.Interop.Excel.Range celLrangE;
        try
        {
            excel = new Microsoft.Office.Interop.Excel.Application();
            excel.Visible = false;
            excel.DisplayAlerts = false;
            worKbooK = excel.Workbooks.Add(Type.Missing);
    
    
            worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
            worKsheeT.Name = "StudentRepoertCard";
    
            worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 8]].Merge();
            worKsheeT.Cells[1, 1] = "Student Report Card";
            worKsheeT.Cells.Font.Size = 15;
    
    
            int rowcount = 2;
    
            foreach (DataRow datarow in ExportToExcel().Rows)
            {
                rowcount += 1;
                for (int i = 1; i <= ExportToExcel().Columns.Count; i++)
                {
    
                    if (rowcount == 3)
                    {
                        worKsheeT.Cells[2, i] = ExportToExcel().Columns[i - 1].ColumnName;
                        worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;
    
                    }
    
                    worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
    
                    if (rowcount > 3)
                    {
                        if (i == ExportToExcel().Columns.Count)
                        {
                            if (rowcount % 2 == 0)
                            {
                                celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
                            }
    
                        }
                    }
    
                }
    
            }
            celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, ExportToExcel().Columns.Count]];
            celLrangE.EntireColumn.AutoFit();
            Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
            border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
            border.Weight = 2d;
    
            celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, ExportToExcel().Columns.Count]];
    
            worKbooK.SaveAs(@"C:\Users\danielzh\Desktop\t.xls"); ;
            worKbooK.Close();
            excel.Quit();
    
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
    
        }
        finally
        {
            worKsheeT = null;
            celLrangE = null;
            worKbooK = null;
        }
    
    }
    public System.Data.DataTable ExportToExcel()
    {
        System.Data.DataTable table = new System.Data.DataTable();
        table.Columns.Add("ID", typeof(int));
        table.Columns.Add("Title", typeof(string));
        table.Columns.Add("Description", typeof(string));
        table.Columns.Add("Owner", typeof(string));
        table.Rows.Add(1, "Amar", "Nice", "Tom");
        table.Rows.Add(2, "Mohit", "Good","Daniel" );
        table.Rows.Add(3, "Garima", "Good","Cindy" );
        table.Rows.Add(4, "jyoti", "Nice","Tim");
      
        return table;
    }

    The result:


    Best Regards,
    Daniel Zhang


    "Windows Forms General" forum will be migrating to a new home on Microsoft Q&A (Preview)!
    We invite you to post new questions in the "Windows Forms General" forum’s new home on Microsoft Q&A (Preview)!
    For more information, please refer to the sticky post.

    Wednesday, August 12, 2020 8:37 AM
  • Try Free Spire.XLS for .NET to create worksheet from datatable. It's quite simply.

    using Spire.Xls;
    
    namespace DatatableToExcel
    {
        class Program
        {
            static void Main(string[] args)
            {
                //Create a workbook
                Workbook wb = new Workbook();
    
                //Get the first worksheet
                Worksheet sheet = wb.Worksheets[0];
    
                //Create a datatable
                System.Data.DataTable table = new System.Data.DataTable();
                table.Columns.Add("ID", typeof(int));
                table.Columns.Add("Title", typeof(string));
                table.Columns.Add("Description", typeof(string));
                table.Columns.Add("Owner", typeof(string));
                table.Rows.Add(1, "A", "E", "I");
                table.Rows.Add(2, "B", "F", "J");
                table.Rows.Add(3, "C", "G", "K");
                table.Rows.Add(4, "D", "H", "L");
    
                //Import data from datatable to workshset
                sheet.InsertDataTable(table, true, 1, 1);
    
                //Save the document
                wb.SaveToFile("DatatableToExcel.xlsx",ExcelVersion.Version2013);
            }
        }
    }

    Friday, August 14, 2020 2:01 AM