请教输出DataTable到Excel的文件名是乱码以及输出记录的格式设置
输出Excel按钮事件：
private void OutToExcel_Lk_Click(object sender, System.EventArgs e)
{
string year = Year_DDL.SelectedItem.Value;
string FileName = year + "年年度工作计划.xls ";
ExportDataGrid( "application/ms-excel ",FileName);
}
读取数据库记录到DataTable并输出：
private void ExportDataGrid(string p_FileType, string p_FileName)
{
string sqlStr = "select Convert(int,EveryYearID) as EveryYearID,isnull(Area, ' ') as Area,MapNo,MapName, '1: '+isnull(Scale, ' ') as Scale,SurveyQuality,isnull(SurveyCyc, ' ') as SurveyCyc,isnull(LastYear, ' ') as LastYear,isnull(LastSurveyQuality, ' ') as LastSurveyQuality,isnull(FactArea, ' ') as FactArea,isnull(ConvertArea, ' ') as ConvertArea,isnull(AssistantArea, ' ') as AssistantArea,isnull(TotalArea, ' ') as TotalArea,Case SurveyFlag when '1 ' then '是 ' when '0 ' then '否 ' end as SurveyFlag,isnull(PrintMap, ' ') as PrintMap,isnull(Rmk, ' ') as Rmk from TaskYearMng order by EveryYearID ";
string connectString = ConfigurationSettings.AppSettings[ "DBConnection "];
SqlConnection cn = new SqlConnection( connectString );
SqlDataAdapter da = new SqlDataAdapter( sqlStr,cn );
da.Fill(dsInfo);
DataTable dt = new DataTable();
dt.Columns.Add( "序号 ");
dt.Columns.Add( "区域 ");
dt.Columns.Add( "图号 ");
dt.Columns.Add( "图名 ");
dt.Columns.Add( "比例尺 ");
dt.Columns.Add( "测量性质 ");
dt.Columns.Add( "测量周期(基/检) ");
dt.Columns.Add( "上次测量年份 ");
dt.Columns.Add( "上次测量性质 ");
dt.Columns.Add( "实测面积 ");
dt.Columns.Add( "折算面积 ");
dt.Columns.Add( "辅助面积 ");
dt.Columns.Add( "合计面积 ");
dt.Columns.Add( "多波束测量 ");
dt.Columns.Add( "印图 ");
dt.Columns.Add( "备注 ");
for (int i = 0; i < dsInfo.Tables[0].Rows.Count; i++)
{
DataRow dr = dt.NewRow();
string num = dsInfo.Tables[0].Rows[i][0].ToString();
string id = " ";
if(num.IndexOf( '- ') > -1)
{
string[] nu = num.Split( '- ');
id = nu[0] + "-- " + nu[1];
}
else
{
id = num;
}
dr[0] = id;
dr[1] = dsInfo.Tables[0].Rows[i][1].ToString();
dr[2] = dsInfo.Tables[0].Rows[i][2].ToString();
dr[3] = dsInfo.Tables[0].Rows[i][3].ToString();
dr[4] = dsInfo.Tables[0].Rows[i][4].ToString();
dr[5] = dsInfo.Tables[0].Rows[i][5].ToString();
dr[6] = dsInfo.Tables[0].Rows[i][6].ToString();
dr[7] = dsInfo.Tables[0].Rows[i][7].ToString();
dr[8] = dsInfo.Tables[0].Rows[i][8].ToString();
dr[9] = dsInfo.Tables[0].Rows[i][9].ToString();
dr[10] = dsInfo.Tables[0].Rows[i][10].ToString();
dr[11] = dsInfo.Tables[0].Rows[i][11].ToString();
dr[12] = dsInfo.Tables[0].Rows[i][12].ToString();
dr[13] = dsInfo.Tables[0].Rows[i][13].ToString();
dr[14] = dsInfo.Tables[0].Rows[i][14].ToString();
dr[15] = dsInfo.Tables[0].Rows[i][15].ToString();
dt.Rows.Add(dr);
}
HttpResponse resp;
resp = System.Web.HttpContext.Current.Response;
resp.Clear();
resp.ContentEncoding = System.Text.Encoding.GetEncoding( "GB2312 ");
resp.AppendHeader( "Content-Disposition ", "attachment;filename= "+p_FileName);
string colHeaders = " ";
string ls_item= " ";
int j;
//定义表对象与行对像，同时用DataSet对其值进行初始化
DataRow[] myRow=dt.Select( " ");
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for(j=0;j <dt.Columns.Count-1;j++)
colHeaders+=dt.Columns[j].Caption.ToString()+ "\t ";
colHeaders +=dt.Columns[j].Caption.ToString() + "\n ";
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders);
//逐行处理数据
foreach(DataRow row in myRow)
{
//在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n
for(j=0;j <row.Table.Columns.Count-1;j++)
ls_item +=row[j].ToString() + "\t ";
ls_item += row[j].ToString() + "\n ";
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
resp.Write(ls_item);
ls_item= " ";
}
System.IO.File.Delete(p_FileName);//删除临时文件
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
请问我的文件名是2007年年度工作计划.xls，为什么输出之后却是乱码呢？
还有个问题就是我数据库中某条记录一个字段的数据是1/4(就是四分之一),输出到Excel之后却变成了4月1日,请问应该怎么才能让它输出正确的格式呢?
急!!
在线等!!
解决立刻给分！多谢！！
[解决办法]
顶
[解决办法]
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "gb2312 ";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
HttpContext.Current.Response.AddHeader( "content-disposition ", "attachment;filename= "+HttpContext.Current.Server.UrlPathEncode(fileName));
=-====
HttpContext.Current.Server.UrlPathEncode(fileName));这一句能让文件名正常显示为中文
至于那个1/4的可以试试
HttpContext.Current.Response.Write(@ " <style> .text { mso-number-format:\@; }td{font-size:12px;mso-number-format:\@;} </style> ");
[解决办法]
resp.AppendHeader( "Content-Disposition ", "attachment;filename= "+p_FileName);
> > > >
resp.AppendHeader( "Content-Disposition ", "attachment;filename= "+ Server.UrlPathEncode(p_FileName));
[解决办法]
你web.config里面设置的是什么编码？
utf-8 of gb2312?
1/4的你加到开始输出数据的最上面试试
那个不一定好用了
[解决办法]
private void ExportDataGrid(string p_FileType, string p_FileName)
{
string res;
HttpResponse resp;
resp = System.Web.HttpContext.Current.Response;
resp.Clear();
resp.ContentEncoding = System.Text.Encoding.GetEncoding( "GB2312 ");
resp.AppendHeader( "Content-Disposition ", "attachment;filename= " + HttpContext.Current.Server.UrlPathEncode(p_FileName));
HttpContext.Current.Response.Write(@ " <style> .text { mso-number-format:\@; }td{font-size:12px;mso-number-format:\@;} </style> ");
resp.Write( " <table > <tr> <td> 001 </td> <td> 1/4 </td> </tr> </table> ");
//System.IO.File.Delete(p_FileName);//删除临时文件
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
=========
以下在我的机子上正常
[解决办法]
给个例子给你：
用COM 控件的
public void OutputExcel(ArrayList arrExport, string str, string fileName)
{
System.Data.DataTable dt = (System.Data.DataTable)arrExport[3];
GC.Collect();
Application excel;
int rowIndex = 2;
int colIndex = 0;
_Workbook xBk;
_Worksheet xSt;
Type myExcel;
myExcel = Type.GetTypeFromProgID( "Excel.Application ");
excel = (Application)Activator.CreateInstance(myExcel);
xBk = excel.Workbooks.Add(true);
xSt = (_Worksheet)xBk.ActiveSheet;
#region 列名处理
excel.Cells[2, 1] = arrExport[1].ToString();//类别
xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, 1]).Select();
xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, 1]).Columns.AutoFit();//自动列宽
xSt.get_Range(excel.Cells[2, 1], excel.Cells[2, 1]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
colIndex = 1;
ArrayList returnArr = (ArrayList)arrExport[2];
foreach (System.Collections.Generic.KeyValuePair <string, string> listItem in returnArr)//标题
{
colIndex++;
excel.Cells[2, colIndex] = Strings.GetString(listItem.Key.Trim());
xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[2, colIndex]).Select();
xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[2, colIndex]).Columns.AutoFit();//自动列宽
xSt.get_Range(excel.Cells[2, colIndex], excel.Cells[2, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
}
if (!arrExport[4].Equals(string.Empty))//多选有其它其它
{
excel.Cells[2, dt.Columns.Count - 1] = Strings.GetString( "CF.FormAnalysis_Other "); //其它
xSt.get_Range(excel.Cells[2, dt.Columns.Count-1], excel.Cells[2, dt.Columns.Count-1]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
}
excel.Cells[2, dt.Columns.Count] = Strings.GetString( "CF.FormAnalysis_Total "); //总计
xSt.get_Range(excel.Cells[2, dt.Columns.Count], excel.Cells[2, dt.Columns.Count]).HorizontalAlignment = XlVAlign.xlVAlignCenter;
#endregion
foreach (DataRow row in dt.Rows)
{
rowIndex++;
colIndex = 0;
foreach (DataColumn col in dt.Columns)
{
colIndex++;
if (col.DataType == System.Type.GetType( "System.DateTime "))
{
excel.Cells[rowIndex, colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString( "yyyy-MM-dd ");
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置日期型的字段格式为居中对齐
}
else
{
if (col.DataType == System.Type.GetType( "System.String "))
{
excel.Cells[rowIndex, colIndex] = " ' " + row[col.ColumnName].ToString();
xSt.get_Range(excel.Cells[rowIndex, colIndex], excel.Cells[rowIndex, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置字符型的字段格式为居中对齐
}
else
{
excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
}
}
}
}
xSt.get_Range(excel.Cells[3, 1], excel.Cells[dt.Rows.Count+2, 1]).Select();
xSt.get_Range(excel.Cells[3, 1], excel.Cells[dt.Rows.Count+2, 1]).Columns.AutoFit();//自动列宽
#region. 标题处理 .
excel.Cells[1, 2] = str;
Excel.Range range = xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]);//合并
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;//居中
xSt.get_Range(excel.Cells[1, 1], excel.Cells[1, dt.Columns.Count]).Font.ColorIndex = 5;//着色
range.Cells.MergeCells = true;
xSt.get_Range(excel.Cells[3, dt.Columns.Count + 1], excel.Cells[3, dt.Columns.Count + 1]).Select();
xSt.get_Range(excel.Cells[3, dt.Columns.Count + 1], excel.Cells[3, dt.Columns.Count + 1]).Application.ActiveWindow.FreezePanes = true;//冻结
#endregion
// 显示效果
excel.Visible = true;
string path = Server.MapPath(fileName + ".xls ");
System.IO.FileInfo file = new System.IO.FileInfo(path);
if (file.Exists)
file.Delete();
xBk.SaveCopyAs(Server.MapPath( ". ") + "\\ " + fileName + ".xls ");
dt = null;
Session.Remove( "CFForm_CFFormAnalysis_tmpDataTable ");
xBk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
xBk = null;
excel = null;
xSt = null;
GC.Collect();
Response.Clear();
string strCharset = string.Empty;
switch (SaleCommon.GetSysParameter( "DefaultLanguage "))
{
case "CN ":
strCharset = "GB2312 ";
break;
default:
strCharset = "UTF-8 ";
break;
}
Response.Charset = strCharset;
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);
// 停止页面的执行
Response.End();
}
