Gridview导出到excel - 格式化问题 [英] Gridview export to excel - Formatting problems
问题描述
private void button2_Click（object sender，EventArgs e）
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application（ ）;
Microsoft.Office.Interop.Excel._Workbook工作簿= app.Workbooks.Add（Type.Missing）;
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
尝试
{
工作表=（Microsoft.Office.Interop.Excel.Worksheet）工作簿。表[Sheet1];
工作表=（Microsoft.Office.Interop.Excel.Worksheet）workbook.ActiveSheet;
worksheet.Name =Gioietta环境数据; （int i = 1; i< dataGridView1.Columns.Count + 1; i ++）
{
工作表Cells [1，i] = dataGridView1.Columns [i - 1] .HeaderText; （int i = 0; i< dataGridView1.Rows.Count - 1; i ++）
{
for（int j = 0; j< dataGridView1。 Columns.Count; j ++）
{
工作表Cells [i + 2，j + 1] = dataGridView1.Rows [i] .Cells [j] .Value.ToString（）;
}
}
string fileName = String.Empty;
SaveFileDialog saveFileDialog1 = new SaveFileDialog（）;
saveFileDialog1.Filter =Excel files | * .xls |所有文件（*。*）| *。*;
saveFileDialog1.FilterIndex = 2;
saveFileDialog1.RestoreDirectory = true;
if（saveFileDialog1.ShowDialog（）== DialogResult.OK）
{
fileName = saveFileDialog1.FileName;
工作簿.SaveAs（fileName，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing，Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive，Type.Missing，Type.Missing，型号，型号，型号，型号）;
}
else
return;
}
catch（System.Exception ex）
{
}
finally
{
app.Quit（）;
workbook = null;
app = null;
}
}
我遇到的问题是excel方面。基本上甚至将导出的值格式化为数字，我不能使用它们。我甚至不能总结他们！如果我手动重新输入相同的单元格中的值，可以使用它。有这个东西要与导出过程有关吗？
这是我如何将数据加载到datagridview：
var time = DateTime.Now.ToString（HH：mm：ss）;
dataGridView1.Rows.Add（new string [] {time，textBox1.Text，textBox2.Text}）;
问题来自于这一行，DataGridView如何被填充：
worksheet.Cells [i 2，j 1] = dataGridView1.Rows [i] .Cells [j] .Value.ToString（）;
数据导出为 string 由于 .ToString（），它们是字符串。
As DataGridViewCell.Value 包含字符串，因此您必须在导出之前将dgvCell.Value转换为数字值。例如：
if（j == 1 || j == 2）
{
工作表.Cells（i 2，j 1）= Convert.ToDecimal（dataGridView1.Rows（i）.Cells（j）.Value）
}
else
{
worksheet.Cells [i 2，j 1] = dataGridView1.Rows [i] .Cells [j] .Value.ToString（）;
}
如果DataGridViewCell.Value包含一个数字值（小数，双，整数，...），只需删除 .toString（），它应该按预期工作（Excel将设置适当的类型）。
在这两种情况下，如果要应用自定义格式在Excel中显示，您将在导出过程中松开它，您将不得不明确地设置它在Excel中，使用 Range.NumberFormat 属性。这些Q / A可以帮助实现这一点：
I am not sure about what is going on here. I use this button event to export data from datagridview to excel and the data get exported, file saved, etc.., so it looks like is working fine to me.
private void button2_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
app.Visible = true;
try
{
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
worksheet.Name = "Gioietta Environment Data";
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
string fileName = String.Empty;
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel files |*.xls|All files (*.*)|*.*";
saveFileDialog1.FilterIndex = 2;
saveFileDialog1.RestoreDirectory = true;
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
fileName = saveFileDialog1.FileName;
workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
}
else
return;
}
catch (System.Exception ex)
{
}
finally
{
app.Quit();
workbook = null;
app = null;
}
}
The problem I am experiencing is on that the excel side. Basically even formatting the exported values as numbers, I cannot use them. I cannot even sum them up! If I re-type the value in the same cell manually than it become usable. Has this something to to with the exporting process?
This is how I load the data to the datagridview:
var time = DateTime.Now.ToString("HH:mm:ss");
dataGridView1.Rows.Add(new string[] { time, textBox1.Text, textBox2.Text });
The problem comes from this line and how DataGridView is populated:
worksheet.Cells[i 2, j 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
Data are exported as string whatever the type due to .ToString() and they are string anyway.
As DataGridViewCell.Value contains a string, so you have to cast the dgvCell.Value to a numeric value before exporting. For example:
if (j == 1 || j == 2)
{
worksheet.Cells(i 2, j 1) = Convert.ToDecimal(dataGridView1.Rows(i).Cells(j).Value)
}
else
{
worksheet.Cells[i 2, j 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
If DataGridViewCell.Value contains a numeric value (Decimal, Double, Integer, ...), Just remove .toString() and it should work as expected (Excel will set the appropriate type).
In both cases, if you want to apply a custom format for displaying in Excel, you will loose it during export, and you will have to explictly set it in Excel, using Range.NumberFormat Property. These Q/A can help to achieve this:
这篇关于Gridview导出到excel - 格式化问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
