gridview导出excel的列宽设置问题
以下是导出按钮的代码,问题处在 我想将第二列以后的数据在excel中设置为最合适列宽,rg.Columns.AutoFit();这句代码对文本行的单元格有效,但是对于我第三列数字行的单元格就无效,打开excel显示数值型单元格时,如果数据位数多了就显示成了#####,有没有办法呀!小弟也是初次接触导出excel的问题,求高人支招!!!以下是代码
GridView1.AllowPaging = false;
bind();
Microsoft.Office.Interop.Excel.ApplicationClass oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
object oMissing = System.Reflection.Missing.Value;
oExcel.Workbooks.Add(oMissing);
Microsoft.Office.Interop.Excel.Workbook oBook = oExcel.Workbooks[1];
Microsoft.Office.Interop.Excel.Worksheet oSheet = (Microsoft.Office.Interop.Excel.Worksheet)oBook.Sheets[1];
oSheet.Name = "批量入账模块";
Microsoft.Office.Interop.Excel.Range rg;
for (int j = 0; j < this.GridView1.HeaderRow.Cells.Count - 3; j++) //GridView1.HeaderRow.Cells.Count-3列数减三是为了不把选择 修改 删除这三列添加到excel中,虽然“选择”这一列设置隐藏,实际在总数中还存在。
{
rg = ((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[1, j + 1]);
rg.FormulaR1C1 = this.GridView1.HeaderRow.Cells[j].Text;
}
oSheet.Cells.NumberFormatLocal = "@";
for (int i = 0; i < this.GridView1.Rows.Count; i++)
{
for (int j = 0; j < this.GridView1.Rows[0].Cells.Count; j++)
{
rg = ((Microsoft.Office.Interop.Excel.Range)oSheet.Cells[i + 2, j + 1]);//i是行号,excel读数从1开始,第一行又是表头所以初始加2,j是列号,加一就可以了。
//if (j + 1 == this.GridView1.Rows[0].Cells.Count)
if (j == 3) //j=3实际是第四列,循环从0开始。第四列是金额列,格式要设置为数字。
{
rg.NumberFormatLocal = "#,##0.00";
}
rg.FormulaR1C1 = this.GridView1.Rows[i].Cells[j].Text;
if (j > 1)
{
rg.Columns.AutoFit();
}
}
}
rg = null;
string VirFileName = "批量入账模块" + Regex.Replace(DateTime.Now.ToString(), @"\D", "") + ".xls";
oBook.SaveAs(Server.MapPath(VirFileName),56, oMissing, oMissing, oMissing, oMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
oMissing, oMissing, oMissing, oMissing, oMissing);
oExcel.Workbooks.Close();
oExcel.Quit();
oSheet = null;
oBook = null;
oExcel = null;
GC.Collect();
Response.Redirect(VirFileName);
GridView1.AllowPaging = true;
bind();
[解决办法]
如果你导出的数据不需要再次被读取
建议使用html输出
也就是html另存为excel
[解决办法]
Web程序最好不要用Microsoft.Office.Interop.Excel.ApplicationClass