C#中如何将数据库数据直接导出到Excel?

发布网友 发布时间：2022-04-23 02:07

我来回答

共1个回答

热心网友 时间：2022-05-05 00:32

// <summary>
/// 导出Excel格式
/// </summary>
/// <param name="gridView"></param>
/// <param name="filename"></param>
private static void ExportGridViewToExcel(DevExpress.XtraGrid.Views.Grid.GridView gridView, string filename)
{
SaveFileDialog sfd = new SaveFileDialog();
filename += DateTime.Now.ToString("yyyyMMdd") + "-" + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString();

sfd.FileName = filename;
sfd.Filter = "Excel files (*xls) | *.xls";
sfd.RestoreDirectory = true;

if (sfd.ShowDialog() == DialogResult.OK && sfd.FileName.Trim() != null)
{
int rowIndex = 2;
int colIndex = 0;
int colNum = gridView.Columns.Count;

System.Reflection.Missing miss = System.Reflection.Missing.Value;

Microsoft.Office.Interop.Excel.Application xlapp = new
Microsoft.Office.Interop.Excel.Application(true);
xlapp.Visible = true;

Microsoft.Office.Interop.Excel.Workbooks mBooks =
(Microsoft.Office.Interop.Excel.Workbooks)xlapp.Workbooks;

Microsoft.Office.Interop.Excel.Workbook mBook =
(Microsoft.Office.Interop.Excel.Workbook)mBooks.Add(miss);

Microsoft.Office.Interop.Excel.Worksheet mSheet =
(Microsoft.Office.Interop.Excel.Worksheet)mBook.ActiveSheet;

Microsoft.Office.Interop.Excel.Range mRange = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value);

//设置对齐方式
mSheet.Cells.HorizontalAlignment = XlHAlign.xlHAlignCenter;
//设置文字自动换行
//mSheet.Cells.WrapText = true;
//设置标题行高度
((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["1:1", System.Type.Missing]).RowHeight = 20;
//设置数据行高度
((Microsoft.Office.Interop.Excel.Range)mSheet.Rows["2:" + gridView.RowCount + 1, System.Type.Missing]).RowHeight = 16;
//设置字体大小（10号字体）
mSheet.Range[mSheet.Cells[1, 1], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]].Font.Size = 10;
//设置单元格边框
Microsoft.Office.Interop.Excel.Range range1 =gridView.RowCount>17?mSheet.Range[mSheet.Cells[1, 2], mSheet.Cells[gridView.RowCount + 1, gridView.Columns.Count]]:mSheet.Range[mSheet.Cells[1, 2], mSheet.Cells[19, gridView.Columns.Count]];
range1.Borders.LineStyle = 1;
//设置标题颜色
mSheet.Range[mSheet.Cells[2, 1], mSheet.Cells[1, gridView.Columns.Count]].Font.Color =Color.Blue;
//将-￥000格式化成￥-000
string InOutInvSum = Convert.ToDecimal(gridView.GetRowCellValue(irow, gridView.Columns[icol])).ToString("C").Replace("￥", "").Replace("全角￥", "");
mSheet.Cells[rowIndex, colIndex] = "￥" + InOutInvSum;

Microsoft.Office.Interop.Excel.Range rH = mSheet.get_Range("C1","L1");
rH.Merge(0);
//报表的标题vlookup
mSheet.Cells[1,3] = "";

//写标题
for (int row = 2; row <= gridView.Columns.Count; row++ )
{
mSheet.Cells[2, row] = gridView.Columns[row - 1].GetTextCaption();

}
try
{
for (int irow = 0; irow < gridView.Columns.Count; irow++)
{
rowIndex++;
colIndex = 1;
for (int icol = 1; icol < gridView.Columns.Count; icol++)
{
colIndex++;
mSheet.Cells[rowIndex, colIndex] = gridView.GetRowCellValue(irow, gridView.Columns[icol]);

}//end 列
}//end 行
mBook.SaveAs(sfd.FileName,
Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, miss, miss, miss, miss,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
miss, miss, miss, miss, miss);
//retuen true;

}//end TRY
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
mBook.Close(false, miss, miss);
mBooks.Close();
xlapp.Quit();
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mRange);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mSheet);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mBook);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(mBooks);
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlapp);
GC.Collect();
}

}
}