尋一dataset導出到excel的 哎,從網上找了幾個function,可導出來的都是亂碼。。也設置了web.config,utf-8,gb2312也都用了,也都保持一致了,咋還亂碼??尋求幫助。 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 #region 导出excel,最后一行合并 /// <summary> /// 导出excel,最后一行合并 /// </summary> /// <param name="dt"></param> /// <param name="filePath"></param> public static void Export(System.Data.DataTable dt, string filePath) { if (dt == null) { throw new Exception("数据表中无数据"); } int eRowIndex = 1; int eColIndex = 1; int cols = dt.Columns.Count; int rows = dt.Rows.Count; Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); try { //列名的处理 for (int i = 0; i < cols; i++) { xlApp.Cells[eRowIndex, eColIndex] = dt.Columns[i].ColumnName; eColIndex++; } //列名加粗显示 xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[eRowIndex, cols]).Font.Bold = true; xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Name = "Arial"; xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Size = "10"; eRowIndex++; for (int i = 0; i < rows; i++) { eColIndex = 1; for (int j = 0; j < cols; j++) { xlApp.Cells[eRowIndex, eColIndex] = dt.Rows[i][j].ToString(); eColIndex++; } eRowIndex++; } //合并单元格 //ran1.Merge(ran1.MergeCells); Microsoft.Office.Interop.Excel._Worksheet _workSheet = (_Worksheet)xlApp.ActiveSheet; Microsoft.Office.Interop.Excel.Range ran1 = _workSheet.get_Range(_workSheet.Cells[dt.Rows.Count + 1, 1], _workSheet.Cells[dt.Rows.Count + 1, 14]); ran1.MergeCells = true; //控制单元格中的内容。 xlApp.Cells.EntireColumn.AutoFit(); xlApp.DisplayAlerts = false; xlBook.SaveCopyAs(filePath); xlApp.Workbooks.Close(); } catch { throw; } finally { xlApp.Quit(); //杀掉Excel进程。 GC.Collect(); } } #endregion private void doStock(string sqls,string OrderName) { //获取数据源DataSet DataSet ds = CatalogAccess.GetFillDataSet(sqls); if (ds != null && ds.Tables[0].Rows.Count > 0) { string file = Server.MapPath("~/App_Data/" + OrderName + ".xls"); System.IO.File.Copy(Server.MapPath("~/App_Data/TemporaryFiles/AssetTemplate.xls"), file, true); string conString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + file + ";Extended Properties=Excel 8.0;"; string sql = "insert into [Stock$] values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}')"; //string sql = "insert into [Stock$] values('{0}','{1}','{2}','{3}','{4}','{5}')"; using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conString)) { System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand(); conn.Open(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { cmd.CommandText = string.Format(sql, ds.Tables[0].Rows[i]["OrderID"].ToString(), ds.Tables[0].Rows[i]["DateCreated"].ToString(), ds.Tables[0].Rows[i]["Product_Coding"].ToString(), ds.Tables[0].Rows[i]["Product_Name"].ToString(), ds.Tables[0].Rows[i]["Quantity"].ToString(), ds.Tables[0].Rows[i]["UnitCost"].ToString(), ds.Tables[0].Rows[i]["Subtotal"].ToString(), Type(Convert.ToInt32(ds.Tables[0].Rows[0]["Completed"])), ds.Tables[0].Rows[i]["Shipped_DateShipped"].ToString(), float.Parse(ds.Tables[0].Compute("sum(Subtotal)", "").ToString()).ToString(), ds.Tables[0].Rows[i]["Invoice"].ToString(), ds.Tables[0].Rows[i]["UserName"].ToString(), ds.Tables[0].Rows[i]["Handset"].ToString(), ds.Tables[0].Rows[i]["Address"].ToString(), ds.Tables[0].Rows[i]["postalcode"].ToString(), ds.Tables[0].Rows[i]["Shipped_Consignee"].ToString(), ds.Tables[0].Rows[i]["Shipped_Handset"].ToString(),ds.Tables[0].Rows[i]["Shipped_Address"].ToString(), ds.Tables[0].Rows[i]["Shipped_postalcode"].ToString(), ds.Tables[0].Rows[i]["Shipped_LeaveWord_Content"].ToString()); cmd.ExecuteNonQuery(); } } Response.Clear(); Response.AppendHeader("Content-Disposition", "attachment;filename=" + OrderName + ".xls"); Response.WriteFile(file); Response.End(); Response.Write("<script language=javascript>window.alert('导出成功!');</script>"); } else { Response.Write("<script language=javascript>window.alert('无内容可导出!');</script>"); } } 我知道你说的那情况,那个根据grid导出excel的方法,设置编码格式时设成那个Default的应该就可以了。不用特意指定某种格式。 你知道什麽?我這可沒gridview哦,就是dataset直接導出excel.. using Microsoft.Office.Interop.Excel;这个命名空间,要加引用~ 还有个问题~如果操作系统是繁体的话,要将字放word里转成繁体再导。这是前些年弄个erp的时候碰到过的。 如下:[code=C#] public void ExportByDataTable(DataTable dt, string strFileName) { try { StringWriter sw = new StringWriter(); string colstr = ""; foreach (DataColumn col in dt.Columns) { colstr += col.ColumnName + "\t"; } sw.WriteLine(colstr); foreach (DataRow row in dt.Rows) { colstr = ""; foreach (DataColumn col in dt.Columns) { colstr += row[col.ColumnName].ToString() + "\t"; } sw.WriteLine(colstr); } sw.Close(); System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=Station.xls"); System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel"; System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); System.Web.HttpContext.Current.Response.Write(sw); System.Web.HttpContext.Current.Response.End(); } catch (Exception ex) { lblMessage.Text = ex.Message; } }[/code] System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");就是这句,可以设成Default编码格式。实际上你这个方法和把grid导出来意思一个样子。不调用excel的dll. System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;我也用过。我当时候是把这句改成上面的样子就好了。 所以這裡的default是指將這行代碼注釋掉?還是有其他專門的寫法? 恩,你說的不錯,我改成Default就可以了。代碼如下:System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; 关于密码过期问题 一个 js 时间相加的问题? 超长曲线图形怎么做 SQL分页大家进50分!!! 请各位看看本站做得如何,欢迎提出修改意见 批量删除数据,出现超时.... 多语言的网站要怎么弄? 登陆网站出错,无法确定是我网站问题还是挂的服务器问题 请高手看看MemoryStream序列化中byte[]的问题 如何嵌套读取数据库中两个表的记录? Language="VB" 的 aspx文件 如何引用vb.net 写的dll 在asp.net中怎样获取别人网站上的一段内容?
/// <summary>
/// 导出excel,最后一行合并
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static void Export(System.Data.DataTable dt, string filePath)
{
if (dt == null)
{
throw new Exception("数据表中无数据");
}
int eRowIndex = 1;
int eColIndex = 1;
int cols = dt.Columns.Count;
int rows = dt.Rows.Count;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
try
{
//列名的处理
for (int i = 0; i < cols; i++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Columns[i].ColumnName;
eColIndex++;
}
//列名加粗显示
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[eRowIndex, cols]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Name = "Arial";
xlApp.get_Range(xlApp.Cells[eRowIndex, 1], xlApp.Cells[rows + 1, cols]).Font.Size = "10";
eRowIndex++; for (int i = 0; i < rows; i++)
{
eColIndex = 1;
for (int j = 0; j < cols; j++)
{
xlApp.Cells[eRowIndex, eColIndex] = dt.Rows[i][j].ToString();
eColIndex++;
}
eRowIndex++;
} //合并单元格
//ran1.Merge(ran1.MergeCells);
Microsoft.Office.Interop.Excel._Worksheet _workSheet = (_Worksheet)xlApp.ActiveSheet; Microsoft.Office.Interop.Excel.Range ran1 = _workSheet.get_Range(_workSheet.Cells[dt.Rows.Count + 1, 1], _workSheet.Cells[dt.Rows.Count + 1, 14]);
ran1.MergeCells = true;
//控制单元格中的内容。
xlApp.Cells.EntireColumn.AutoFit(); xlApp.DisplayAlerts = false;
xlBook.SaveCopyAs(filePath);
xlApp.Workbooks.Close(); }
catch
{
throw;
}
finally
{
xlApp.Quit();
//杀掉Excel进程。
GC.Collect();
}
}
#endregion
{
//获取数据源DataSet
DataSet ds = CatalogAccess.GetFillDataSet(sqls); if (ds != null && ds.Tables[0].Rows.Count > 0)
{
string file = Server.MapPath("~/App_Data/" + OrderName + ".xls");
System.IO.File.Copy(Server.MapPath("~/App_Data/TemporaryFiles/AssetTemplate.xls"), file, true); string conString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + file + ";Extended Properties=Excel 8.0;";
string sql = "insert into [Stock$] values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}')";
//string sql = "insert into [Stock$] values('{0}','{1}','{2}','{3}','{4}','{5}')";
using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(conString))
{
System.Data.OleDb.OleDbCommand cmd = conn.CreateCommand();
conn.Open(); for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
cmd.CommandText = string.Format(sql, ds.Tables[0].Rows[i]["OrderID"].ToString(), ds.Tables[0].Rows[i]["DateCreated"].ToString(), ds.Tables[0].Rows[i]["Product_Coding"].ToString(),
ds.Tables[0].Rows[i]["Product_Name"].ToString(), ds.Tables[0].Rows[i]["Quantity"].ToString(), ds.Tables[0].Rows[i]["UnitCost"].ToString(), ds.Tables[0].Rows[i]["Subtotal"].ToString(), Type(Convert.ToInt32(ds.Tables[0].Rows[0]["Completed"])), ds.Tables[0].Rows[i]["Shipped_DateShipped"].ToString(),
float.Parse(ds.Tables[0].Compute("sum(Subtotal)", "").ToString()).ToString(), ds.Tables[0].Rows[i]["Invoice"].ToString(), ds.Tables[0].Rows[i]["UserName"].ToString(), ds.Tables[0].Rows[i]["Handset"].ToString(), ds.Tables[0].Rows[i]["Address"].ToString(), ds.Tables[0].Rows[i]["postalcode"].ToString(), ds.Tables[0].Rows[i]["Shipped_Consignee"].ToString(),
ds.Tables[0].Rows[i]["Shipped_Handset"].ToString(),ds.Tables[0].Rows[i]["Shipped_Address"].ToString(), ds.Tables[0].Rows[i]["Shipped_postalcode"].ToString(), ds.Tables[0].Rows[i]["Shipped_LeaveWord_Content"].ToString());
cmd.ExecuteNonQuery();
}
}
Response.Clear();
Response.AppendHeader("Content-Disposition", "attachment;filename=" + OrderName + ".xls");
Response.WriteFile(file);
Response.End(); Response.Write("<script language=javascript>window.alert('导出成功!');</script>");
}
else
{
Response.Write("<script language=javascript>window.alert('无内容可导出!');</script>");
}
}
不用特意指定某种格式。
你知道什麽?我這可沒gridview哦,就是dataset直接導出excel..
这个命名空间,要加引用~
public void ExportByDataTable(DataTable dt, string strFileName)
{
try
{
StringWriter sw = new StringWriter();
string colstr = "";
foreach (DataColumn col in dt.Columns)
{
colstr += col.ColumnName + "\t";
}
sw.WriteLine(colstr); foreach (DataRow row in dt.Rows)
{
colstr = "";
foreach (DataColumn col in dt.Columns)
{
colstr += row[col.ColumnName].ToString() + "\t";
}
sw.WriteLine(colstr);
}
sw.Close();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=Station.xls");
System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
System.Web.HttpContext.Current.Response.Write(sw);
System.Web.HttpContext.Current.Response.End();
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
}
}[/code]
就是这句,可以设成Default编码格式。
实际上你这个方法和把grid导出来意思一个样子。不调用excel的dll.
我也用过。我当时候是把这句改成上面的样子就好了。
代碼如下:System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;