Search This Blog

Tuesday, December 26, 2017

Export Excel - c#

/// <summary>
        /// Export to xls to directory Path
        /// </summary>
        public static void ExportListFromTable()
        {
            // Here we create a DataTable with four columns.
            System.Data.DataTable table = new System.Data.DataTable();
            table.Columns.Add("File Path", typeof(string));
            table.Columns.Add("File Size", typeof(string));
            table.Columns.Add("Start DateTime", typeof(DateTime));
            table.Columns.Add("End DateTime", typeof(DateTime));
            table.Columns.Add("Status", typeof(string));
            // Here we add five DataRows.
            table.Rows.Add("FilePath", "Size", DateTime.Now, DateTime.Now, "Success");
            table.Rows.Add("FilePath", "Size", DateTime.Now, DateTime.Now, "Success");
            table.Rows.Add("FilePath", "Size", DateTime.Now, DateTime.Now, "Success");
            table.Rows.Add("FilePath", "Size", DateTime.Now, DateTime.Now, "Success");

            DirectoryInfo dir = new DirectoryInfo(@"D:\Reports");
            dir.Create();
            string excelFileName = string.Format(@"D:\Reports\FileLog_{0}.xls", DateTime.Now.Ticks.ToString());

            FileInfo file = new FileInfo(excelFileName);
            StreamWriter streamWriter = file.CreateText();

            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
            Table tblListView = new Table();
            tblListView.ID = "_tblListView";
            tblListView.BorderStyle = BorderStyle.Solid;
            tblListView.BorderWidth = Unit.Pixel(1);
            tblListView.BorderColor = System.Drawing.Color.Silver;
            System.Data.DataView dvListViewData = table.DefaultView;
            if (dvListViewData != null && dvListViewData.Count > 0)
            {
                tblListView.Rows.Add(new TableRow());
                tblListView.Rows[0].BackColor = System.Drawing.Color.Gainsboro;
                tblListView.Rows[0].Font.Bold = true;

                for (int i = 0; i < table.Columns.Count; i++)
                {
                    tblListView.Rows[0].Cells.Add(new TableCell());
                    tblListView.Rows[0].Cells[i].Text = table.Columns[i].ToString();                 
                }

                for (int i = 0; i < dvListViewData.Count; i++)
                {
                    tblListView.Rows.Add(new TableRow());

                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        tblListView.Rows[i + 1].Cells.Add(new TableCell());                    
                            tblListView.Rows[i + 1].Cells[j].Text = dvListViewData[i][j].ToString();                      
                    }
                }
            }
            tblListView.RenderControl(htmlTextWriter);
            streamWriter.Write(stringWriter.ToString());

            htmlTextWriter.Close();
            streamWriter.Close();
            stringWriter.Close();          

        }

No comments:

Post a Comment