你是要把dataGridView导出到excel，并且设置excel格式是吧，我写了2个方法
一个是输出到excel的，一个是改excel格式的(如果文件内容过大，改格式比较费时)
你拿去调用吧，根据需要修改格式，各个属性后面加了备注
/// 导出1个dataGridView到excel
///
///
/// 导出Excel文件名
/// sheet名
///
public static void ExportToExcel(string fileName, string sheetName, DataGridView dataGridView1)
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel file|*.xls";
saveDialog.FileName = fileName;
saveDialog.ShowDialog();
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消后不会报错
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Unable to create the Excel object, may be your PC did not install Excel");
return;
}
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); //我这是英文的，你可以改为中文的en-ZH
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
#region 创建sheet1
Microsoft.Office.Interop.Excel.Worksheet worksheet1 = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
//冻结首行
xlApp.ActiveWindow.SplitRow = 1;
xlApp.ActiveWindow.SplitColumn = 0;
xlApp.ActiveWindow.FreezePanes = true;
if (dataGridView1.RowCount > 1)
{
//写入标题
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
worksheet1.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < dataGridView1.Rows.Count; r++)
{
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
worksheet1.Cells[r + 2, i + 1] = dataGridView1.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();
}
//excel单元格格式设置
SetExcelFormat(worksheet1, dataGridView1.Rows.Count, dataGridView1.Columns.Count); //其他设置
}
else
{
worksheet1.Cells[1, 1] = "No records";
}
worksheet1.Name = sheetName;
#endregion
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//fileSaved = true;
}
catch (Exception ex)
{
//fileSaved = false;
MessageBox.Show("An error occurred when export file, the file likely is opening！\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();//强行销毁
MessageBox.Show("Saved success !", "Export Result", MessageBoxButtons.OK);
}
/// 设置sheet的格式
///
///
/// 工作簿sheet
/// sheet表行数
/// sheet表列数
public static void SetExcelFormat(Worksheet worksheet, int rowCount, int columnCount)
{
//设置列名格式：
try
{
Microsoft.Office.Interop.Excel.Range titleRange = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnCount]); //选取单元格，第一行 Exception from HRESULT: 0x800A03EC
if (titleRange.Text != null)
{
//range1.Merge(true);//合并单元格
//range1.Value2 = strTitle; //设置单元格内文本
titleRange.Font.Name = "Arial";//设置字体
titleRange.Font.Size = 11;//字体大小
titleRange.Font.Bold = true;//加粗显示
titleRange.Interior.ColorIndex = 8; //标题颜色
titleRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//水平居中
titleRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;//垂直居中
titleRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//设置边框
titleRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框常规粗细
titleRange.Interior.Color = Color.FromArgb(0, 255, 255);//设置颜色
}
}
catch (Exception)
{
MessageBox.Show("At least one sheet is empty");
return;
}
//设置内容格式：
for (int i = 1; i <= columnCount; i++)
{
Microsoft.Office.Interop.Excel.Range contentRange = worksheet.get_Range(worksheet.Cells[2, i], worksheet.Cells[rowCount + 1, i]); //选取其他单元格
contentRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;//水平居左
contentRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;//垂直居上
contentRange.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//设置边框
contentRange.Borders.Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;//边框常规粗细
contentRange.RowHeight = 100; //行高
contentRange.WrapText = true; //自动换行
contentRange.NumberFormatLocal = "@";//文本格式
contentRange.Font.Name = "Arial";
contentRange.Font.Size = 11;
contentRange.EntireColumn.AutoFit();
contentRange.Rows.Interior.Color =Color.FromArgb(0, 255, 255); //单元格颜色
}
}
private void button1_Click(object sender, EventArgs e)
{
string fileName = "Excel名_" + DateTime.Now.ToString("yyyyMMdd");
string sheetName = "sheet名";
ExportToExcel(fileName, sheetName, dataGridView1);
}
