ASP.NET Tips - Simple way to Create Excel from DataTable : The Official Microsoft ASP.NET Forums

Reply

ramuis78

Member

52 Points

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

Aug 02, 2006 02:58 PM|LINK

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

Moved by tmorton on Sep 05, 2006 01:00 PM

Reply

kyawzaw

Member

10 Points

2 Posts

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

Aug 31, 2006 02:21 AM|LINK

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;
	}
}

Kyaw Zaw

Reply

kyawzaw

Member

10 Points

2 Posts

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

Aug 31, 2006 02:32 AM|LINK

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;
 }
}

Kyaw Zaw

Reply

s_kiruba_bte...

Member

42 Points

119 Posts

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

Apr 09, 2008 05:44 AM|LINK

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.

Reply

DigiMortal

Contributor

5658 Points

939 Posts

MVP

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

Apr 09, 2008 06:48 AM|LINK

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).

Don't forget to mark solution providing post as "Answered".

Also visit my ASP.NET blog or follow me @ Twitter:twitter.com/gpeipman

Reply

s_kiruba_bte...

Member

42 Points

119 Posts

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

Apr 09, 2008 08:44 AM|LINK

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

Reply

lchene

Member

2 Points

1 Post

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

Jul 18, 2008 08:29 PM|LINK

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.

Reply

mrutyunjaypa...

Member

18 Points

9 Posts

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

Dec 09, 2008 11:01 AM|LINK

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

Reply

kavuri.chait...

Member

2 Points

1 Post

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

Dec 10, 2008 06:22 AM|LINK

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

Reply

Larry Neblet...

Member

24 Points

7 Posts

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

May 04, 2011 07:54 AM|LINK

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.

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

13 replies

ramuis78

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

kyawzaw

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

kyawzaw

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

s_kiruba_bte...

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

DigiMortal

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

s_kiruba_bte...

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

lchene

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

mrutyunjaypa...

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

kavuri.chait...

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

Larry Neblet...

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

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft