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

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
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 = @"<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"<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: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