Question

使用asp.net编程，在实现“导出”功能的时候，遇到问题。导出的excel文件打开后是乱码。

Answer 1

/// <summary>
/// 导出execl
/// </summary>
/// <param name="myDataTable"></param>
/// <param name="saveFileName"></param>
/// <returns></returns>
public bool ExportExcel(DataTable myDataTable, string saveFileName)
{
try
{
if (saveFileName.IndexOf(":") < 0) return false; //被点了取消

Excel.Application xlApp = new Excel.Application();
object missing = System.Reflection.Missing.Value;
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return false;
}
Excel.Workbooks workbooks = xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; //取得sheet1
Excel.Range range;
range = worksheet.Columns;
range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平对齐设置
range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //垂直对齐设置
range.ColumnWidth = 15;
#region 写入字段
worksheet.Cells[1, 1] = "CenterID";
worksheet.Cells[1, 2] = "HostType";
worksheet.Cells[1, 3] = "HostID";
worksheet.Cells[1, 4] = "HostIP";
worksheet.Cells[1, 5] = "HostPort";
worksheet.Cells[1, 6] = "HostUser";
worksheet.Cells[1, 7] = "HostPass";
worksheet.Cells[1, 8] = "Channels";
worksheet.Cells[1, 9] = "ServerIP";
worksheet.Cells[1, 10] = "HostValidDate";
#endregion

for (int r = 0; r < myDataTable.Rows.Count; r++)
{
worksheet.Cells[r + 2, 1] = myDataTable.Rows[r]["CenterID"];
worksheet.Cells[r + 2, 2] = myDataTable.Rows[r]["HostType"];
worksheet.Cells[r + 2, 3] = myDataTable.Rows[r]["HostID"];
worksheet.Cells[r + 2, 4] = myDataTable.Rows[r]["HostIP"];
worksheet.Cells[r + 2, 5] = myDataTable.Rows[r]["HostPort"];
worksheet.Cells[r + 2, 6] = myDataTable.Rows[r]["HostUser"];
worksheet.Cells[r + 2, 7] = myDataTable.Rows[r]["HostPass"];
worksheet.Cells[r + 2, 8] = myDataTable.Rows[r]["Channels"];
worksheet.Cells[r + 2, 9] = myDataTable.Rows[r]["ServerIP"];
worksheet.Cells[r + 2, 10] = myDataTable.Rows[r]["HostValidDate"];
}
worksheet.SaveAs(saveFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
workbook.Close(missing, missing, missing);
xlApp.Quit();
GC.Collect();
GC.WaitForPendingFinalizers();
return true;
}
catch (Exception ex)
{
return false;
}
}

试试用这个方式 这个前提必须 有office 的插件

Answer 2

// 导出信息
void wsc_ExportLN18Completed(object sender, ExportLN18CompletedEventArgs e)
{
if (e.Error == null)
{
byte[] mybyte = new byte[e.Result.Length];
mybyte = e.Result;
SaveFileDialog saveDialog = (SaveFileDialog)e.UserState;
try
{
using (Stream stream = saveDialog.OpenFile())
{
stream.Write(mybyte, 0, mybyte.Length);
}
MessageBox.Show("导出已完成");
}
catch (Exception)
{
}
}
}

[OperationContract]
public byte[] ExportLN18(IList<string> strTitle, IDictionary<string, IList<string>> context)
{
if (strTitle == null || context == null)
{
return null;
}
//把title与context 数组转化成 DataTable对象
DataTable dt = WebUtils.getDataTableFromTitleAndContext(strTitle, context);
return CommUtil.ExportDataTableToExcel("信息", dt);
}

/// <summary>
/// 功 能: 把title与context 数组转化成 DataTable对象
/// <returns></returns>
internal static DataTable getDataTableFromTitleAndContext(IList<string> strTitle, IDictionary<string, IList<string>> context)
{
if (strTitle == null || context == null)
{
return null;
}
DataTable dt = new DataTable();
foreach (string item in strTitle)
{
DataColumn dc = new DataColumn(item, Type.GetType("System.String"));
dt.Columns.Add(dc);
}
foreach (KeyValuePair<string, IList<string>> item in context)
{
IList<string> list = item.Value;
if (list == null)
{
continue;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < list.Count; i++)
{
dr[i] = list[i];
}
dt.Rows.Add(dr);
}
return dt;
}

/// <summary>
/// 将数据集DataSet生成服务器目录下的Excel文件，并且返回文件的字节
/// </summary>
/// <param name="excelTitle">表格标题(工作表名称)</param>
/// <param name="dt">导出Excel的数据集</param>
/// <returns>生成Excel文件的字节</returns>
public static byte[] ExportDataTableToExcel(string excelTitle, System.Data.DataTable dt)
{
if (dt != null && dt.Rows.Count >= 65535) { throw new Exception("Line of your table number is greater than the maximum 65535"); }
string strFileName = DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".xls";
string strExAbsolutePath = PreparedExport(strFileName);
if (System.IO.Directory.Exists(System.Web.HttpRuntime.AppDomainAppPath.ToString() + CommConst.WaterFilePath.EXPORT_PATH) && ExportDataExcel(excelTitle, excelTitle, dt, strExAbsolutePath))
{
try
{
FileStream s2 = File.OpenRead(strExAbsolutePath);
byte[] downloadbyte = new byte[s2.Length];
s2.Read(downloadbyte, 0, downloadbyte.Length);
s2.Close();
return downloadbyte;
}
catch (Exception)
{
return new byte[0];
}
}
else
{
return new byte[0];
}
}

Answer 3

用DataSet来导出..就下面一点代码就可以导出来了..简单明了..

DataSet dataset = db.Getdate("select * from Computer");//导出的一个表

if (dataset.IsNotNull())//此表不为空
{
string fileName = "保存名字.xlsx";
string path = Server.MapPath("~/Excel/");//路径,在此项目下创一个Excel文件夹
if (ExportExcel.ExportToExcel(dataset.Tables[0], path, fileName, null))
{
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName); //设置回发内容为Excel
Response.ContentType = "application/ms-excel";
Response.WriteFile(path + fileName); //把刚刚生成的Excel文件写入Http流
Response.End();
}
}

Answer 4

导excel的通用类现在网上很多，可以搜索一下，下面给你提供一个使用ExcelWirter组件的方法：
DataTable dt = getdata();
String templateFieldPath = Request.PhysicalApplicationPath + String.Format("Reports\\Template\\{0}.xls", "myrpt");
if (dt == null || dt.Rows.Count <= 0)
{
Response.Write("<Script>alert('未查询，不能导出报表!');</script>");
return;
}
SoftArtisans.ExcelWriter.SAExcelTemplateDotNet xlw = null;
try
{
xlw = new SoftArtisans.ExcelWriter.SAExcelTemplateDotNet();
xlw.Open(templateFieldPath);
DataSet ds = new DataSet();
ds.Tables.Add(dt.Copy());
xlw.set_DataSource("dt", 100, ds);
xlw.set_DataSource("title1", 1,
"标题"
);
xlw.Process("Out.xls", SoftArtisans.ExcelWriter.SAProcessMethod.saProcessOpenInExcel, 0);
Page.Response.Close();

}
catch// (Exception ex)
{
xlw.Close();
}
finally
{
xlw.Close();
}
-----------------------------------------------------------------
再提供一个自己封装的excelhelper类（调用office的excel库）的方法：
ExcelHelper是我自己封装的一个操作excel的类，有需要留下mail，发给你。

String templateFieldPath = Request.PhysicalApplicationPath + String.Format("Reports\\Template\\{0}.xls", "rptParkingExpenseAnalysisDetail");
String outputFilePath = Request.PhysicalApplicationPath + "Reports\\Template\\TempFile\\ParkingExpenseAnalysisDetail"
+ DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Function.RemovedTempFiles(Request.PhysicalApplicationPath + "\\Reports\\Template\\TempFile\\");
//打开EXCEL文件模板
NanFangMgr.ExcelHelper.ExcelHelper excel = new NanFangMgr.ExcelHelper.ExcelHelper(templateFieldPath, outputFilePath);
excel.SetCells(1, 1, “标题");
excel.SetCells(2, 11, DateTime.Now.ToString("yyyy-MM-dd"));
excel.SetCells(2, 2, ddlRealEstate.SelectedItem.Text);
excel.SetCells(2, 6, String.Format("收款时间：{0}至{1}",this.txt_PayTimeStart.Text,this.txt_PayTimeEnd.Text));
//制表人+制单日期
//excel.PageSetup.CenterFooter = "制表人：" + Session["LoginInfo_UserName"].ToString()+ "制单日期：" + DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString();
try
{
int iStartRowIndex = 4;
int itmpindex = iStartRowIndex + this.gv.Rows.Count ;//合计行也在里面，所以比GridView的行多一行
excel.SetRangeValue(list, "A4", "N" + itmpindex.ToString());
//合计 行的样式
excel.ApplyStyle("A" + itmpindex.ToString(), "N" + itmpindex.ToString(), false);
excel.SaveFile(outputFilePath);
System.IO.FileInfo file = new System.IO.FileInfo(outputFilePath);
Response.Clear();
Response.Charset = "UTF8";
Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
Response.AddHeader("Content-Length", file.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
Response.WriteFile(file.FullName);
}
catch (Exception er)
{
Page.ClientScript.RegisterStartupScript(this.GetType(), "Hint", "<script>alert('" + er.Message + "');</" + "script>");
}
finally
{
excel.KillExcelProcess();
}