DataGridView导出到Excel
#region 方法一 DateGridView导出到csv格式的Excel
/// <summary>
/// 导出数据到Excel.常用方法，列之间加\t，一行一行输出，此文件其实是csv文件，不过默认可以当成Excel打开。
/// </summary>
/// <param name="dgv"></param>
private void DataGridViewToExcel(DataGridView dgv)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = ;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出Excel文件到";
saveFileDialog.ShowDialog();
if (saveFileDialog.FileName == "")
{
return;
}
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, Encoding.Default);
string strhead = "";
string str = ""; try
{
//写表格标题
for (int i = ; i < dgv.ColumnCount; i++)
{
if (i == dgv.ColumnCount - )
strhead = strhead + dgv.Columns[i].HeaderText.ToString().Replace("\r", " ").Replace("\n", " ").Replace("\t", " ") + "\n";
else
strhead = strhead + dgv.Columns[i].HeaderText.ToString().Replace("\r", " ").Replace("\n", " ").Replace("\t", " ") + "\t";
}
sw.Write(strhead); //写内容
for (int i = ; i < dgv.Rows.Count; i++)
{
for (int j = ; j < dgv.ColumnCount; j++)
{
if (j == dgv.ColumnCount - )
str = str + dgv.Rows[i].Cells[j].Value.ToString().Replace("\r", " ").Replace("\n", " ").Replace("\t", " ") + "\n";
else
str = str + dgv.Rows[i].Cells[j].Value.ToString().Replace("\r", " ").Replace("\n", " ").Replace("\t", " ") + "\t";
}
sw.Write(str);
str = "";
}
sw.Close();
myStream.Close();
}
catch (Exception e1)
{
MessageBox.Show(e1.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
#endregion #region 方法二 DataGridView数据显示到Excel
/// <summary>
/// 打开Excel并将DataGridView控件中数据导出到Excel
/// </summary>
/// <param name="dgv">DataGridView对象 </param>
/// <param name="isShowExcle">是否显示Excel界面 </param>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// </remarks>
/// <returns> </returns>
public bool DataGridviewShowToExcel(DataGridView dgv, bool isShowExcle)
{
if (dgv.Rows.Count == )
return false;
//建立Excel对象
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcle;
//生成字段名称
for (int i = ; i < dgv.ColumnCount; i++)
{
excel.Cells[, i + ] = dgv.Columns[i].HeaderText;
}
//填充数据
for (int i = ; i < dgv.RowCount - ; i++)
{
for (int j = ; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + , j + ] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + , j + ] = dgv[j, i].Value.ToString();
}
}
}
return true;
}
#endregion #region 方法三 DataGridView导出到Excel，有一定的判断性
/// <summary> www.2cto.com
///方法，导出DataGridView中的数据到Excel文件
/// </summary>
/// <remarks>
/// add com "Microsoft Excel 11.0 Object Library"
/// using Excel=Microsoft.Office.Interop.Excel;
/// using System.Reflection;
/// </remarks>
/// <param name= "dgv"> DataGridView </param>
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;
//验证strFileName是否为空或值无效
if (fileNameString.Trim() == " ")
{ return; }
//定义表格内数据的行数和列数
int rowscount = dgv.Rows.Count;
int colscount = dgv.Columns.Count;
//行数必须大于0
if (rowscount <= )
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
} //列数必须大于0
if (colscount <= )
{
MessageBox.Show("没有数据可供保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
} //行数不可以大于65536
if (rowscount > )
{
MessageBox.Show("数据记录数太多(最多不能超过65536条)，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
} //列数不可以大于255
if (colscount > )
{
MessageBox.Show("数据记录行数太多，不能保存 ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
} //验证以fileNameString命名的文件是否存在，如果存在删除它
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;
//设置EXCEL不可见
objExcel.Visible = false; //向Excel中写入表格的表头
int displayColumnsCount = ;
for (int i = ; i <= dgv.ColumnCount - ; i++)
{
if (dgv.Columns[i].Visible == true)
{
objExcel.Cells[, displayColumnsCount] = dgv.Columns[i].HeaderText.Trim();
displayColumnsCount++;
}
}
//设置进度条
//tempProgressBar.Refresh();
//tempProgressBar.Visible = true;
//tempProgressBar.Minimum=1;
//tempProgressBar.Maximum=dgv.RowCount;
//tempProgressBar.Step=1;
//向Excel中逐行逐列写入表格中的数据
for (int row = ; row <= dgv.RowCount - ; row++)
{
//tempProgressBar.PerformStep(); displayColumnsCount = ;
for (int col = ; col < colscount; col++)
{
if (dgv.Columns[col].Visible == true)
{
try
{
objExcel.Cells[row + , displayColumnsCount] = dgv.Rows[row].Cells[col].Value.ToString().Trim();
displayColumnsCount++;
}
catch (Exception)
{ } }
}
}
//隐藏进度条
//tempProgressBar.Visible = false;
//保存文件
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
{
//关闭Excel应用
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
Excel导入到DataGridView
/// <summary>
/// Excel数据导入方法
/// </summary>
/// <param name="filePath"></param>
/// <param name="dgv"></param>
public void ExcelToDataGridView(DataGridView dgv)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "从Excel文件导入数据";
ofd.FileName = "";
ofd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
ofd.Filter = "Excel文件(*.xls)|*.xls";
ofd.ValidateNames = true;
ofd.CheckFileExists = true;
ofd.CheckPathExists = true; string fileName = string.Empty;
if (ofd.ShowDialog() == DialogResult.OK)
{
fileName = ofd.FileName;
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1"); //根据DataGridView的列构造一个新的DataTable
DataTable tb = new DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
//dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释，前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
tb.Columns.Add(dc);
}
} //根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[].Rows)
{
if (excelRow[].ToString().Trim().Equals("")) continue; int i = ;
DataRow dr = tb.NewRow();
foreach (DataColumn dc in tb.Columns)
{
try
{
dr[dc] = excelRow[i];
}
catch
{
dr[dc] = "";
}
i++; }
tb.Rows.Add(dr);
}
//在DataGridView中显示导入的数据,先清除，后绑定
//dgv.Columns.Clear();
dgv.DataSource = tb;
}