Remove From My Forums

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

Question
0

Sign in to vote

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

0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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
0

Sign in to vote

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

0

Sign in to vote

User-1908052339 posted

But where will the file get stored?

Thursday, August 2, 2012 6:10 AM
0

Sign in to vote

User-1908052339 posted

I got the solution(alternative)

Friday, August 24, 2012 3:08 AM

Asked by:

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

Question

All replies