111,097
社区成员




/// DataGridView中绑定的是一个DataTable
/// </summary>
/// <param name="grid">DataGridView</param>
/// <param name="ExcelTitle">Excel标题</param>
public static void ToExcel(DataGridView grid)
{
//System.Data.DataTable mytable = (System.Data.DataTable)grid.DataSource;
System.Data.DataTable mytable = (grid.DataSource as DataSet).Tables[0];
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
int rowIndex, colIndex;
rowIndex = 1;
colIndex = 0;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
if (grid.Rows.Count > 0)
{
foreach (DataGridViewColumn colu in grid.Columns)
{
if (colu.Visible)
{
colIndex = colIndex + 1;
xlApp.Cells[1, colIndex] = colu.HeaderText;
}
}
for (int row = 0; row < mytable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < grid.Columns.Count; col++)
{
if (grid.Columns[col].Visible)
{
colIndex = colIndex + 1;
if (grid.Columns[col].CellType.Name == "DataGridViewComboBoxCell")
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].FormattedValue;
else
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
}
else
{
foreach (DataColumn Col in mytable.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[1, colIndex] = Col.ColumnName;
}
for (int row = 0; row < mytable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < mytable.Columns.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("错误消息:" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
/// DataGridView中绑定的是一个DataTable
/// </summary>
/// <param name="grid">DataGridView</param>
/// <param name="ExcelTitle">Excel标题</param>
public static void ExportDataGridTableToExcel(DataGridView grid, string ExcelTitle)
{
//System.Data.DataTable myTable = (System.Data.DataTable)grid.DataSource;
System.Data.DataTable myTable = (grid.DataSource as DataSet).Tables[0];
try
{
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
int rowIndex;
int colIndex;
rowIndex = 2;
colIndex = 0;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//Microsoft.Office.Interop.Excel.Range range1 = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, grid.Columns.Count]);
// range1.NumberFormat = "@";
if (grid.Rows.Count > 0)
{
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, grid.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ExcelTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
foreach (DataGridViewColumn colu in grid.Columns)
{
if (colu.Visible)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = colu.HeaderText;
}
}
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < grid.Columns.Count; col++)
{
if (grid.Columns[col].Visible)
{
colIndex = colIndex + 1;
if (grid.Columns[col].CellType.Name == "DataGridViewComboBoxCell")
{
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].FormattedValue;
// grid.Rows[row].Cells[0]
}
else
{
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
}
}
else
{
Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, myTable.Columns.Count]);
range.MergeCells = true;
xlApp.ActiveCell.FormulaR1C1 = ExcelTitle;
xlApp.ActiveCell.Font.Size = 18;
xlApp.ActiveCell.Font.Bold = true;
//将表中的栏位名称填到Excel的第一行
foreach (DataColumn Col in myTable.Columns)
{
colIndex = colIndex + 1;
xlApp.Cells[2, colIndex] = Col.ColumnName;
}
//得到的表所有行,赋值给单元格
for (int row = 0; row < myTable.Rows.Count; row++)
{
rowIndex = rowIndex + 1;
colIndex = 0;
for (int col = 0; col < myTable.Columns.Count; col++)
{
colIndex = colIndex + 1;
xlApp.Cells[rowIndex, colIndex] = grid.Rows[row].Cells[col].Value;
}
}
}
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, colIndex]).Font.Bold = true;
xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
xlApp.Cells.EntireColumn.AutoFit();
xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlApp.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show("错误消息:" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Microsoft.Office.Interop.Excel.Range range1 = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[grid.Rows.Count + 1, grid.Columns.Count ]);// xlApp.Cells[grid.Rows.Count - 1, grid.Columns.Count - 1]
range1.NumberFormatLocal = "@";
这样写不知道哪里出错了 为什么文本格式汉字乱码了呢?
range.NumberFormatLocal = "@"