DataGridView导出到Excel的三个方法

博客园 - 夜来风雨香

【整理】C#文件操作大全(SamWang)<转> c# 文件及目录操作类 BIOS设置之UEFI/Legacy BIOS切换图文详解 UEFI+GPT引导实践篇（一）：切换到UEFI启动，准备安装介质 UEFI+GPT引导实践篇（二）：UEFI引导安装64位Win7/Win8 UEFI+GPT引导基础篇（一）：什么是GPT，什么是UEFI？预装WIN8系统的电脑安装WIN7的方法串口 COM口 TTL RS-232 RS-485 区别释疑 WinForm DataGridView分页功能 C#GridViewExport帮助类，美化导出 C#EXCEL 操作类--C#DataToExcel帮助类 C#EXCEL 操作类--C#ExcelHelper操作类 VS2010 项目引用了DLL文件，也写了Using，但是编译时提示:未能找到类型或命名空间名称 <转> FPGA Verilog HDL 系列实例--------步进电机驱动控制 C++使用VARIANT实现二维数组的操作 VS2010+VMWare8+VisualDDK1.5.6 创建并调试你的第一个驱动程序 - 完全教程 USB编程研究之二（常见设备类型的GUID) C++——CString用法大全 C++ 如何有效地使用对话框

夜来风雨香 · 2014-08-15 · via 博客园 - 夜来风雨香

#region DataGridView数据显示到Excel
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
{
if (dgv.Rows.Count == 0)
return false;
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
for (int i = 0; i < dgv.RowCount - 1; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
return true;
}
#endregion
#region DateGridView导出到csv格式的Excel
private void DataGridViewToExcel(DataGridView dgv)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为Excel文件";
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 < dgv.ColumnCount; i++)
{
if (i > 0)
{
columnTitle += "/t";
}
columnTitle += dgv.Columns[i].HeaderText;
}
sw.WriteLine(columnTitle);
for (int j = 0; j < dgv.Rows.Count; j++)
{
string columnValue = "";
for (int k = 0; k < dgv.Columns.Count; k++)
{
if (k > 0)
{
columnValue += "/t";
}
if (dgv.Rows[j].Cells[k].Value == null)
columnValue += "";
else
columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
}
#endregion
#region DataGridView导出到Excel，有一定的判断性
public static void DataGridViewToExcel(DataGridView dgv)
{
#region 验证可操作性
SaveFileDialog dlg = new SaveFileDialog();
dlg.DefaultExt = "xls ";
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
dlg.InitialDirectory = Directory.GetCurrentDirectory();
if (dlg.ShowDialog() == DialogResult.Cancel) return;
string fileNameString = dlg.FileName;
if (fileNameString.Trim() == " ")
{ return; }
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
objExcel.Visible = false;
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
#endregion
#region DataGridView数据显示到Excel
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
{
if (dgv.Rows.Count == 0)
return false;
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
for (int i = 0; i < dgv.ColumnCount; i++)
{
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
}
for (int i = 0; i < dgv.RowCount - 1; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
}
return true;
}
#endregion
#region DateGridView导出到csv格式的Excel
private void DataGridViewToExcel(DataGridView dgv)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为Excel文件";
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 < dgv.ColumnCount; i++)
{
if (i > 0)
{
columnTitle += "/t";
}
columnTitle += dgv.Columns[i].HeaderText;
}
sw.WriteLine(columnTitle);
for (int j = 0; j < dgv.Rows.Count; j++)
{
string columnValue = "";
for (int k = 0; k < dgv.Columns.Count; k++)
{
if (k > 0)
{
columnValue += "/t";
}
if (dgv.Rows[j].Cells[k].Value == null)
columnValue += "";
else
columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
}
#endregion
#region DataGridView导出到Excel，有一定的判断性
public static void DataGridViewToExcel(DataGridView dgv)
{
#region 验证可操作性
SaveFileDialog dlg = new SaveFileDialog();
dlg.DefaultExt = "xls ";
dlg.Filter = "EXCEL文件(*.XLS)|*.xls ";
dlg.InitialDirectory = Directory.GetCurrentDirectory();
if (dlg.ShowDialog() == DialogResult.Cancel) return;
string fileNameString = dlg.FileName;
if (fileNameString.Trim() == " ")
{ return; }
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
if (rowscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (colscount <= 0)
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (rowscount > 65536)
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
if (colscount > 255)
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
FileInfo file = new FileInfo(fileNameString);
if (file.Exists)
{
try
{
file.Delete();
}
catch (Exception error)
{
MessageBox.Show(error.Message, "删除失败 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
}
#endregion
Excel.Application objExcel = null;
Excel.Workbook objWorkbook = null;
Excel.Worksheet objsheet = null;
try
{
objExcel = new Microsoft.Office.Interop.Excel.Application();
objWorkbook = objExcel.Workbooks.Add(Missing.Value);
objsheet = (Excel.Worksheet)objWorkbook.ActiveSheet;
objExcel.Visible = false;
int displayColumnsCount = 1;
for (int i = 0; i <= dgv.ColumnCount - 1; i++)
{
if (dgv.Columns[i].Visible == true)
{
objExcel.Cells[1, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
for (int row = 0; row <= dgv.RowCount - 1; row++)
{
displayColumnsCount = 1;
for (int col = 0; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + 2, displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{
}
}
}
}
objWorkbook.SaveAs(fileNameString, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
}
catch (Exception error)
{
MessageBox.Show(error.Message, "警告 ", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
finally
{
if (objWorkbook != null) objWorkbook.Close(Missing.Value, Missing.Value, Missing.Value);
if (objExcel.Workbooks != null) objExcel.Workbooks.Close();
if (objExcel != null) objExcel.Quit();
objsheet = null;
objWorkbook = null;
objExcel = null;
}
MessageBox.Show(fileNameString + "/n/n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
#endregion

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - 夜来风雨香