c# excel2007 导出
场景：关于vs2008 C#导出Excel2007的有关问题
关于vs2008 C#导出Excel2007的问题
winform中读取了数据库的数据，保存在数据集ds中，想把这些数据导出为一个Excel表格，但是导出时出现问题，代码如下：
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "excel files (*.xls)|*.xls|All files (*.*)|*.*";
sfd.FilterIndex = 1;
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel文档");
}
else
{
//Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Excel.Workbook workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
//先只保存一个表头
worksheet.Cells[1, i] = ds.Tables[0].Columns[i].ColumnName;
}
workbook.Saved = true;
workbook.SaveCopyAs(sfd.FileName);
}
}
//Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Excel.Workbook workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
这两句出现未处理的COMException错误，错误提示为：
内存或磁盘空间不足，Microsoft Office Excel 无法再次打开或保存任何文档。
• 要想获得更多的可用内存，请关闭不再使用的工作簿或程序。
• 要想释放磁盘空间，请删除相应磁盘上不需要的文件。
请遇见过此异常的高手门不吝赐教，万分感谢！！！
------解决方案--------------------
保存为excel的局限性是只能保存6万5千行，我一般保存为csv。我这个代码是datagridview保存到csv文件的。加个ProgressBar空间，显示进度。
------解决方案--------------------
public class ExportToEXCEL
{
public void ExportTo(SaveFileDialog saveFileDialog1, Button button3, DataGridView dataGridView1, GroupBox groupBox2, ProgressBar progressBar1)
{
bool b1 = true; //导出数据是否出错
double n1 = 0; //进度条显示
string s1 = ""; //进度条显示
string sSaveFileName; //EXCEL文件名
saveFileDialog1.Reset();
saveFileDialog1.DefaultExt = "xlsx";
saveFileDialog1.Filter = "xlsx
------解决方案--------------------
*.xlsx";
saveFileDialog1.ShowDialog();
sSaveFileName = saveFileDialog1.FileName;
if (string.IsNullOrEmpty(sSaveFileName) == false)
{
try
{
button3.Enabled = false;
if (dataGridView1.Rows.Count > 0)
{
groupBox2.Text = "请等待，正在导出数据至EXCEL... ...";
groupBox2.Refresh();
Microsoft.Office.Interop.Excel.Application Excel1 = new Microsoft.Office.Interop.Excel.Application();
Excel1.Application.Workbooks.Add(true);
Excel1.Visible = false;
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (dataGridView1.Columns[i].Visible == true)
Excel1.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
for (int i1 = 0; i1 < dataGridView1.RowCount; i1++)
{
Application.DoEvents();
n1 = (double)(i1 + 1) / dataGridView1.RowCount * 100;
progressBar1.Value = (int)n1;
s1 = progressBar1.Value.ToString() + "%";
groupBox2.Text = "完成百分比：" + s1;
for (int i2 = 0; i2 < dataGridView1.ColumnCount; i2++)
{
if (dataGridView1.Columns[i2].Visible == true)
Excel1.Cells[i1 + 2, i2 + 1] ="'" + dataGridView1.Rows[i1].Cells[i2].Value;
}
}
//如果文件存在，则先删除该文件
if (System.IO.File.Exists(sSaveFileName))
{
System.IO.File.Delete(sSaveFileName);
}
Excel1.ActiveWorkbook.Close(true, sSaveFileName, Missing.Value);
Excel1.Quit();
groupBox2.Text = "数据导出EXCEL完成";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
b1 = false;
}
finally
{
if (b1 == true)
MessageBox.Show("导出EXCEL文件成功完成", "Note", MessageBoxButtons.OK, MessageBoxIcon.Information);
button3.Enabled = true;
}
}
}
}
我项目中用的导出数据至ＥＸＣＥＬ文件的类，用了一年多了，没问题
关于vs2008 C#导出Excel2007的问题
winform中读取了数据库的数据，保存在数据集ds中，想把这些数据导出为一个Excel表格，但是导出时出现问题，代码如下：
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "excel files (*.xls)|*.xls|All files (*.*)|*.*";
sfd.FilterIndex = 1;
sfd.RestoreDirectory = true;
if (sfd.ShowDialog() == DialogResult.OK)
{
Excel.ApplicationClass xlApp = new Excel.ApplicationClass();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel文档");
}
else
{
//Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Excel.Workbook workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
//先只保存一个表头
worksheet.Cells[1, i] = ds.Tables[0].Columns[i].ColumnName;
}
workbook.Saved = true;
workbook.SaveCopyAs(sfd.FileName);
}
}
//Excel.Workbook workbook = xlApp.Workbooks.Add(true);
Excel.Workbook workbook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
这两句出现未处理的COMException错误，错误提示为：
内存或磁盘空间不足，Microsoft Office Excel 无法再次打开或保存任何文档。
• 要想获得更多的可用内存，请关闭不再使用的工作簿或程序。
• 要想释放磁盘空间，请删除相应磁盘上不需要的文件。
请遇见过此异常的高手门不吝赐教，万分感谢！！！
------解决方案--------------------
保存为excel的局限性是只能保存6万5千行，我一般保存为csv。我这个代码是datagridview保存到csv文件的。加个ProgressBar空间，显示进度。
private bool ExportToCsv(DataGridView dgv, ProgressBar pb)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "CSV(逗号分隔)(*.csv)
------解决方案--------------------
*.csv";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为CSV(逗号分隔)文件";
if (dlg.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = dlg.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string columnTitle = "";
try
{
//写入列标题
for (int i = 0; i+1 < dgv.ColumnCount; i++)
{
if (i > 0)
{
columnTitle += ",";
}
columnTitle += dgv.Columns[i].HeaderText;
}
columnTitle.Remove(columnTitle.Length - 1);
sw.WriteLine(columnTitle);
//写入列内容
for (int j = 0; j < dgv.Rows.Count; j++)
{
string columnValue = "";
pb.Value = j * 100 / dgv.Rows.Count;
for (int k = 0; k+1 < dgv.Columns.Count; k++)
{
if (k > 0)
{
columnValue += ",";
}
if (dgv.Rows[j].Cells[k].Value == null)
{
columnValue += "";
}
else
{
string m = dgv.Rows[j].Cells[k].Value.ToString().Trim();
columnValue += m.Replace(",", "，");
}
}
columnValue.Remove(columnValue.Length - 1);
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
MessageBox.Show("完成！");
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return false;
}
finally
{
sw.Close();
myStream.Close();
}
return true;
}
else
{
return false;
}
}
------解决方案--------------------
public class ExportToEXCEL
{
public void ExportTo(SaveFileDialog saveFileDialog1, Button button3, DataGridView dataGridView1, GroupBox groupBox2, ProgressBar progressBar1)
{
bool b1 = true; //导出数据是否出错
double n1 = 0; //进度条显示
string s1 = ""; //进度条显示
string sSaveFileName; //EXCEL文件名
saveFileDialog1.Reset();
saveFileDialog1.DefaultExt = "xlsx";
saveFileDialog1.Filter = "xlsx
------解决方案--------------------
*.xlsx";
saveFileDialog1.ShowDialog();
sSaveFileName = saveFileDialog1.FileName;
if (string.IsNullOrEmpty(sSaveFileName) == false)
{
try
{
button3.Enabled = false;
if (dataGridView1.Rows.Count > 0)
{
groupBox2.Text = "请等待，正在导出数据至EXCEL... ...";
groupBox2.Refresh();
Microsoft.Office.Interop.Excel.Application Excel1 = new Microsoft.Office.Interop.Excel.Application();
Excel1.Application.Workbooks.Add(true);
Excel1.Visible = false;
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (dataGridView1.Columns[i].Visible == true)
Excel1.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
for (int i1 = 0; i1 < dataGridView1.RowCount; i1++)
{
Application.DoEvents();
n1 = (double)(i1 + 1) / dataGridView1.RowCount * 100;
progressBar1.Value = (int)n1;
s1 = progressBar1.Value.ToString() + "%";
groupBox2.Text = "完成百分比：" + s1;
for (int i2 = 0; i2 < dataGridView1.ColumnCount; i2++)
{
if (dataGridView1.Columns[i2].Visible == true)
Excel1.Cells[i1 + 2, i2 + 1] ="'" + dataGridView1.Rows[i1].Cells[i2].Value;
}
}
//如果文件存在，则先删除该文件
if (System.IO.File.Exists(sSaveFileName))
{
System.IO.File.Delete(sSaveFileName);
}
Excel1.ActiveWorkbook.Close(true, sSaveFileName, Missing.Value);
Excel1.Quit();
groupBox2.Text = "数据导出EXCEL完成";
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
b1 = false;
}
finally
{
if (b1 == true)
MessageBox.Show("导出EXCEL文件成功完成", "Note", MessageBoxButtons.OK, MessageBoxIcon.Information);
button3.Enabled = true;
}
}
}
}
我项目中用的导出数据至ＥＸＣＥＬ文件的类，用了一年多了，没问题