ASP.net中导出Excel表
发布网友
发布时间：2022-04-30 18:48
共5个回答
热心网友
时间：2022-04-27 11:25
把DataSet中的数据填充到一个XML 文件中，然后以Excel的MIME格式输出。
你先耐心的仔细看下这个代码：
例子可以直接放到类中被调用使用。。。
/// <summary>
/// 导出excel并保存起来
/// </summary>
/// <param name="source">dataSet类型</param>
/// <param name="fileName">文件的完整路径</param>
public void exportToExcel(DataTable source, string fileName)
{
if (System.IO.File.Exists(fileName))
System.IO.File.Delete(fileName);
System.IO.StreamWriter excelDoc;
excelDoc = new System.IO.StreamWriter(fileName);
const string startExcelXML = "<xml version>\r\n<Workbook " +
"xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
" xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
"xmlns:x=\"urn:schemas- microsoft-com:office:" +
"excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
"office:spreadsheet\">\r\n <Styles>\r\n " +
"<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
"<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
"\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
"\r\n <Protection/>\r\n </Style>\r\n " +
"<Style ss:ID=\"BoldColumn\">\r\n <Font " +
"x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
" ss:Format=\"@\"/>\r\n</Style>\r\n <Style " +
"ss:ID=\"Decimal\">\r\n <NumberFormat " +
"ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
"<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
"ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
"ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
"ss:Format=\"yyyy-mm-dd;@\"/>\r\n </Style>\r\n " +
"</Styles>\r\n ";
const string endExcelXML = "</Workbook>";
int rowCount = 0;
int sheetCount = 1;
excelDoc.Write(startExcelXML);
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
excelDoc.Write("<Table>");
excelDoc.Write("<Row>");
for (int x = 0; x < source.Columns.Count; x++)
{
excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
excelDoc.Write(source.Columns[x].ColumnName);
excelDoc.Write("</Data></Cell>");
}
excelDoc.Write("</Row>");
foreach (DataRow x in source.Rows)
{
rowCount++;
//if the number of rows is > 64000 create a new page to continue output
if (rowCount == 64000)
{
rowCount = 0;
sheetCount++;
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
excelDoc.Write("<Table>");
}
excelDoc.Write("<Row>"); //ID=" + rowCount + "
for (int y = 0; y < source.Columns.Count; y++)
{
System.Type rowType;
rowType = x[y].GetType();
switch (rowType.ToString())
{
case "System.String":
string XMLstring = x[y].ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "&");
XMLstring = XMLstring.Replace(">", "＞");
XMLstring = XMLstring.Replace("<", "＜");
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write(XMLstring);
excelDoc.Write("</Data></Cell>");
break;
case "System.DateTime":
//Excel has a specific Date Format of YYYY-MM-DD followed by
//the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
//The Following Code puts the date stored in XMLDate
//to the format above
DateTime XMLDate = (DateTime)x[y];
if (ShowDateTime(XMLDate) != "")
{
string XMLDatetoString = ""; //Excel Converted Date
XMLDatetoString = XMLDate.Year.ToString() +
"-" +
(XMLDate.Month < 10 ? "0" +
XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
"-" +
(XMLDate.Day < 10 ? "0" +
XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
"T" +
(XMLDate.Hour < 10 ? "0" +
XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
":" +
(XMLDate.Minute < 10 ? "0" +
XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
":" +
(XMLDate.Second < 10 ? "0" +
XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
".000";
excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
"<Data ss:Type=\"DateTime\">");
excelDoc.Write(XMLDatetoString);
excelDoc.Write("</Data></Cell>");
}
else
{
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write("");
excelDoc.Write("</Data></Cell>");
}
break;
case "System.Boolean":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write(x[y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
"<Data ss:Type=\"Number\">");
excelDoc.Write(x[y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.Decimal":
case "System.Double":
excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
"<Data ss:Type=\"Number\">");
excelDoc.Write(x[y].ToString());
excelDoc.Write("</Data></Cell>");
break;
case "System.DBNull":
excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
"<Data ss:Type=\"String\">");
excelDoc.Write("");
excelDoc.Write("</Data></Cell>");
break;
default:
throw (new Exception(rowType.ToString() + " not handled."));
}
}
excelDoc.Write("</Row>");
}
excelDoc.Write("</Table>");
excelDoc.Write(" </Worksheet>");
excelDoc.Write(endExcelXML);
excelDoc.Close();
}
/// <summary>
/// 如果日期为1900-01-01则不显示
/// </summary>
/// <param name="objDateTime">时间</param>
/// <returns></returns>
public string ShowDateTime(object objDateTime)
{
string strDateTime = "";
if (objDateTime != System.DBNull.Value && objDateTime.ToString() != "")
{
strDateTime = Convert.ToDateTime(objDateTime).ToString("yyyy-MM-dd");
if (strDateTime != "1900-01-01")
{
strDateTime = Convert.ToDateTime(objDateTime).ToString("yyyy-MM-dd");
}
else
{
strDateTime = "";
}
}
return strDateTime;
}
如果还有不清楚的可以继续交流下！
你补充了，那我也补充下：
fileName你所说的fileName是不是我这个代码中的，
如果是，请注意：这里应该输入绝对问题（也就是服务器上的物理地址）
不知道你输入是什么地址？？？
热心网友
时间：2022-04-27 12:43
{
HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders= "", ls_item="";
int i=0;
//定义表对象与行对像，同时用DataSet对其值进行初始化
DataTable dt=ds.Tables[0];
DataRow[] myRow=dt.Select("");
// typeid=="1"时导出为EXCEL格式文件；typeid=="2"时导出为XML格式文件
if(typeid=="1")
{
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for(i=0;i<dt.Columns.Count-1;i++)
colHeaders+=dt.Columns[i].Caption.ToString()+"\t";
colHeaders +=dt.Columns[i].Caption.ToString() +"\n";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach(DataRow row in myRow)
{
//在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n
for(i=0;i<row.ItemArray.Length-1;i++)
ls_item +=row[i].ToString() + "\t";
ls_item += row[i].ToString() +"\n";
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item="";
}
}
else
{
if(typeid=="2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
热心网友
时间：2022-04-27 14:18
用引入excel组件类型库的方法是office自动化技术，这个方法可以打开一个编辑好的很美观的excel文件，再往相应的单元格写数据，缺点是速度非常慢，服务器会有死进程运行不稳定的风险，因为office天生不是给服务器运行的。
推荐使用SOAOffice中间件，一切问题迎刃而解，调用代码极其简单
热心网友
时间：2022-04-27 16:09
//导出Excel的方法
private void ExportExcel()
{
DataSet ds=dtsSelect;//数据源
if(ds==null) return;
string saveFileName="";
bool fileSaved=false;
SaveFileDialog saveDialog=new SaveFileDialog();
saveDialog.DefaultExt ="xls";
saveDialog.Filter="Excel文件|*.xls";
saveDialog.FileName ="Sheet1";
saveDialog.ShowDialog();
saveFileName=saveDialog.FileName;
if(saveFileName.IndexOf(":")<0) return; //被点了取消
Excel.Application xlApp=new Excel.Application();
if(xlApp==null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
Excel.Workbooks workbooks=xlApp.Workbooks;
Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//写入字段
for(int i=0;i<ds.Tables[0].Columns.Count;i++)
{
worksheet.Cells[1,i+1]=ds.Tables[0].Columns[i].ColumnName;
}
//写入数值
for(int r=0;r<ds.Tables[0].Rows.Count;r++)
{
for(int i=0;i<ds.Tables[0].Columns.Count;i++)
{
worksheet.Cells[r+2,i+1]=ds.Tables[0].Rows[r][i];
}
System.Windows.Forms.Application.DoEvents();
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
if(cmbxType.Text!="Notification")
{
Excel.Range rg=worksheet.get_Range(worksheet.Cells[2,2],worksheet.Cells[ds.Tables[0].Rows.Count+1,2]);
rg.NumberFormat="00000000";
}
if(saveFileName!="")
{
try
{
workbook.Saved =true;
workbook.SaveCopyAs(saveFileName);
fileSaved=true;
}
catch(Exception ex)
{
fileSaved=false;
MessageBox.Show("导出文件时出错,文件可能正被打开！\n"+ex.Message);
}
}
else
{
fileSaved=false;
}
xlApp.Quit();
GC.Collect();//强行销毁
if(fileSaved && System.IO.File.Exists(saveFileName)) System.Diagnostics.Process.Start(saveFileName); //打开EXCEL
}
需要在COM组件中找到Microsoft excel 11.0(不同的版本可能不同，我的是office2003).添加该引用。然后在程序中using Excel;
（VS2008：using Microsoft.Office.Interop.Excel;）
-----------------
楼主可以试试我这个代码，没有问题的
热心网友
时间：2022-04-27 18:17
