locked
ASP.NET Tips - Simple way to Create Excel from DataTable RRS feed

  • Question

  • User-1555584400 posted

    Here we will discuss a simple solution to create excel file from asp.net without excel software.

        Function CreateExcelFile(ByVal dt As DataTable) As Boolean
            Dim bFileCreated As Boolean = False
            Dim sTableStart As String = "<HTML><BODY><TABLE Border=1><TR><TH>Header1</TH></TR>"
            Dim sTableEnd As String = "</TABLE></BODY></HTML>"
            Dim sTableData As String
            Dim nRow As Long
            For nRow = 0 To dt.Rows.Count - 1
                sTableData &= "<TR><TD>" & dt.Rows(nRow).Item(0).ToString & "</TD></TR>"
            Next
            Dim sTable As String = sTableStart & sTableData & sTableEnd
            Dim oExcelFile As System.IO.File
            Dim oExcelWrite As System.IO.StreamWriter
            sExcelFile = "c:/excelfile.xls"
            oExcelWrite = oExcelFile.CreateText(sExcelFile)
            oExcelWrite.WriteLine(sTable)
            oExcelWrite.Close()
            bFileCreated = True
            Return bFileCreated
        End Function

     

    Thanks

    Rams

    Wednesday, August 2, 2006 10:58 AM

All replies

  • User14614248 posted
    I created a function in C# based on yours. I just want to leave a copy of it here. Thanks for your function.  
    public static bool CreateExcelFile(DataTable dt, string filename)
    {
    	try
    	{
    		string sTableStart = @"&lt;HTML><BODY><TABLE Border=1>";
    		string sTableEnd = @"&lt;/TABLE></BODY></HTML>";	
    		string sTHead = "&lt;TR>";
    		StringBuilder sTableData = new StringBuilder();
    
    
    		foreach(DataColumn col in dt.Columns)
    		{
    		    sTHead += @"&lt;TH>" + col.ColumnName + @"&lt;/TH>";
    		}
    
    		sTHead += @"&lt;/TR>";
    
    		foreach(DataRow row in dt.Rows)
    		{
    			sTableData.Append(@"&lt;TR>");
    			for(int i=0; i"&lt;TD>" + row[i].ToString() + @"&lt;/TD>");
    			}
    			sTableData.Append("@</TR>");
    		}
    
    		string sTable = sTableStart + sTHead + sTableData.ToString() + sTableEnd;
    
    		System.IO.StreamWriter oExcelWriter = System.IO.File.CreateText(filename);
    		oExcelWriter.WriteLine(sTable);
    		oExcelWriter.Close();
    		return true;
    	}
    	catch
    	{
    		return false;
    	}
    }			        
    Wednesday, August 30, 2006 10:21 PM
  • User14614248 posted
    I got some problem in posting because forum's code editor is not working well. following is the correct one. [;)] 
    public static bool CreateExcelFile(DataTable dt, string filename)
    {
     try
     {
      string sTableStart = @"<HTML><BODY><TABLE Border=1>";
      string sTableEnd = @"</TABLE></BODY></HTML>"; 
      string sTHead = "<TR>";
      StringBuilder sTableData = new StringBuilder();

      foreach(DataColumn col in dt.Columns)
      {
          sTHead += @"<TH>" + col.ColumnName + @"</TH>";
      }
      sTHead += @"</TR>";
      foreach(DataRow row in dt.Rows)
      {
       sTableData.Append(@"<TR>");
       for(int i=0; i<dt.Columns.Count; i++)
       {
        sTableData.Append(@"<TD>" + row[i].ToString() + @"</TD>");
       }
       sTableData.Append(@"</TR>");
      }
      string sTable = sTableStart + sTHead + sTableData.ToString() + sTableEnd;
      System.IO.StreamWriter oExcelWriter = System.IO.File.CreateText(filename);
      oExcelWriter.WriteLine(sTable);
      oExcelWriter.Close();
      return true;
     }
     catch
     {
      return false;
     }
    }
     
    Wednesday, August 30, 2006 10:32 PM
  • User-244987825 posted

     In the above code i am getting one error Occasionally.

    "The process cannot access the file 'F:\Quotation.xls' because it is being used by another process. "

    1. I am saving my Excel file in  F:\Quotation.xls'  path. My scenario is, First time 1 excel file was saved, and i open that file and try to edit that file.Next Ill try to save another 1 Quotation.xls file in same path, i got this above error.

    2. Next scenario is,I wont open that F:\Quotation.xls file, and continuously i am trying to save that excel  file in same path,I got this above error.

                  Do u have any ideas? Pls let me know.
     

     

    Wednesday, April 9, 2008 1:44 AM
  • User-525215917 posted
    After writing the file you must dispose any resources that may refer to this file. When you have file opened in Excel then opening that file fails because Excel locks it so hard. Make sure you have Excel closed when writing to file using ASP.NET /or any other technology).
    Wednesday, April 9, 2008 2:48 AM
  • User-244987825 posted

     Do u know how to make sure that excel file closed? How can i know? Any other technologies?

    Wednesday, April 9, 2008 4:44 AM
  • User-2055668820 posted

    Hi!

    This works great, but in Excel 2003, when the cell contents is like 001876 (all numbers), then leading 0s are removed and the cell content treated as a number.

    Friday, July 18, 2008 4:29 PM
  • User492809006 posted

    Hi 

    I have create a excel file from dataset ,Please look at this code below,

      public void excelgenerate(DataSet ds)
        {
            //Microsoft.Office.Interop.Excel.ApplicationClass excel = new ApplicationClass();

            //excel.Application.Workbooks.Add(true);
            Microsoft.Office.Interop.Excel.Application oAppln;
            //declaring work book
            Microsoft.Office.Interop.Excel.Workbook oWorkBook;
            //declaring worksheet
            Microsoft.Office.Interop.Excel.Worksheet oWorkSheet;
            oAppln = new Microsoft.Office.Interop.Excel.Application();
            oWorkBook = (Microsoft.Office.Interop.Excel.Workbook)(oAppln.Workbooks.Add(true));
            //oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)oWorkBook.ActiveSheet;
            int i = 0;
            foreach (DataTable table in ds.Tables)
            {
                //oWorkSheet = new Microsoft.Office.Interop.Excel.Worksheet();
                
                oWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)(oWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing));
                if (i == 0)
                {
                    oWorkSheet.Name = "first";
                }
                else
                {
                    oWorkSheet.Name = "second";
                }
                oWorkSheet.Activate();
                //oWorkBook.Worksheets.Add(null, null, 1, null);
                //DataTable table = DATASETNAME.Tables[0];
                int ColumnIndex = 0;
                
                foreach (DataColumn col in table.Columns)
                {
                    ColumnIndex++;

                    oWorkSheet.Cells[1, ColumnIndex] = col.ColumnName;
                }
                int rowIndex = 0;
                foreach (DataRow row in table.Rows)
                {
                    rowIndex++;
                    ColumnIndex = 0;
                    foreach (DataColumn col in table.Columns)
                    {
                        ColumnIndex++;
                        oWorkSheet.Cells[rowIndex + 1, ColumnIndex] = row[col.ColumnName].ToString();
                    }
                }

              
                // Worksheet worksheet = (Worksheet)oAppln.ActiveSheet;
                //worksheet.Activate();
                i++;
            }

            string fileName = System.Guid.NewGuid().ToString().Replace("-", "") + ".xls";
            string uploadfld = Server.MapPath("upload");
            fileName = uploadfld + fileName;
            //string strFile = "d://" + "report" + ".xls";
            oWorkBook.SaveAs(fileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
            oWorkBook.Close(null,null,null);
            oAppln.Quit();

        }

     

     

    Thanks and Regards

    Mrutyunjay palai

    Tuesday, December 9, 2008 7:01 AM
  • User647355463 posted

    Hi Mrutyunjay ,

    I have been searching for something like this since one week.

    In my application I have to create an Excel Workbook with 5 spreadsheets

    Each spreadsheet represents 5 different pages in the application.

    Please give me some idea how to do this in ASP.NET

     

    Thanks and Regards,

    Chaitanya

    Wednesday, December 10, 2008 2:22 AM
  • User100585308 posted

    I can suggest one more trick how to create an excel file. If you know the design of your future report you can create XML excel spreadsheet and save it on the disk. Then you need just insert data in corresponding places.

    If you using the NET 3.0 you can manipulate with XLSX files. Just add referince to Package namespace to have access to zip archive of XLSX files.

    You may consider 3rd party tools such as Aspose.Cells, Component XLS for .NET, Excel Jetcell .NET component.

    Wednesday, May 4, 2011 3:54 AM
  • User556728108 posted

    see below

    Sunday, July 22, 2012 10:38 AM
  • User556728108 posted

    Hi, I tried to use the function to create excel on the fly so that the program can send an email with excel attachment. 

    //creating two column excel header - "Sl. No" and "Email Address"
    string sTableStart = @"<HTML><BODY><TABLE Border=1>";
              string sTableEnd = @"</TABLE></BODY></HTML>"; 
              string sTHead = "<TR>";
              StringBuilder sTableData = new StringBuilder();
              
              {
                  sTHead += @"<TH>" + "Sl. No" + @"</TH>";
                  sTHead += @"<TH>" + "Email Address" + @"</TH>";
              }
              sTHead += @"</TR>";

    //inserting rows 

    string sTable = "";
               var hpin = from c in Contacts {just a linq query}
    
                if (hpin.ToList().Count > 0)
                {  int j = 0;
                
                    foreach (var i in hpin)
                    {   
                         sTableData.Append(@"<TR>");
                        sTableData.Append(@"<TD>" + i.ToString() + @"</TD>");
    
                        sTableData.Append(@"<TD>" + i.EMailAddress1.ToString() + @"</TD>");
                         sTableData.Append(@"</TR>");
                        
    
                                        
                    }
    
                    sTable = sTableStart + sTHead + sTableData.ToString() + sTableEnd;

    //creating mimetype attachment 

     var _hattach = new ActivityMimeAttachment()
                        {
                            ObjectId = new EntityReference(Email.EntityLogicalName, _emailId),
                            ObjectTypeCode = Email.EntityLogicalName,
                            FileName = "abcpin.xls", //Path.GetFileName(attachment.Name),
                            MimeType = "application/vnd.ms-excel",//"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                            Body = Convert.ToBase64String(new UnicodeEncoding().GetBytes(sTable.ToCharArray()))
                                
                        };

    //send email message with attachment; 

    when I open the excel file the i see this!!! 

    <HTML><BODY><TABLE Border=1><TR><TH>Sl. No</TH><TH>Email Address</TH></TR><TR><TD>Contact</TD><TD>abc@live.com</TD></TR><TR><TD>Contact</TD><TD>xyz@live.com</TD></TR><TR><TD>Contact</TD><TD>ericw@live.com</TD></TR><TR><TD>Contact</TD><TD>patrick@live.com</TD></TR></TABLE></BODY></HTML>
    



    Sunday, July 22, 2012 10:40 AM
  • User-1908052339 posted

    But where will the file get stored?

    Thursday, August 2, 2012 6:10 AM
  • User-1908052339 posted

    I got the solution(alternative)

    Friday, August 24, 2012 3:08 AM