Question

大侠，你好，看到你在网上搞得怎么将gridcontrol中的数据导入到excel问题，能不能把代码给我发一份？

Answer 1

private void linkLabel1_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
{
if (table1.Rows.Count == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件！");

}
else
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Title = "导出Excel";
saveFileDialog.Filter = "Excel文件(*.xls)|*.xls";
DialogResult dialogResult = saveFileDialog.ShowDialog(this);
if (dialogResult == DialogResult.OK)
{
DataSetToExcel(table1, saveFileDialog.FileName, false);

}
}
}
public bool DataSetToExcel(DataTable dataTable, string fileName, bool isShowExcle)
{
if (System.IO.File.Exists(fileName))
{
System.IO.File.Delete(fileName);
}//如果有同名文件就删除
//DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不包括字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;

//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = false;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;

//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

object[,] objData = new object[rowNumber, columnNumber];

for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
if (dataTable.Rows[r][c].GetType() == typeof(string))
{
objData[r, c] = "'" + dataTable.Rows[r][c];
}
else
{
objData[r, c] = dataTable.Rows[r][c];
}
}
//Application.DoEvents();
}

// 写入Excel
range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
range.Value2 = objData;
worksheet.get_Range(excel.Cells[2, 6], excel.Cells[rowNumber + 1, 6]).NumberFormat = "yyyy-m-d h:mm";
worksheet.get_Range(excel.Cells[2, 8], excel.Cells[rowNumber + 1, 8]).NumberFormat = "yyyy-m-d h:mm";
//worksheet.get_Range(excel.Cells[2, 3], excel.Cells[rowNumber + 1, 3]).NumberFormat = "@";
//string fileName = path + "\\" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
workbook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

try
{
workbook.Saved = true;
excel.UserControl = false;
//excelapp.Quit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
excel.Quit();
}

if (isShowExcle)
{
System.Diagnostics.Process.Start(fileName);
}
return true;
}