DataGridView输出或保存为Excel文件

DataGridView输出或保存为Excel文件

2010-04-21 16:11:12| 分类：程序源码 |举报 |字号大中小订阅

///

///DataGridView控件

///保存的文件路径

///是否打开文件

///True/False

public bool OutputFileToExcel(DataGridViewC ExportGrid, string fullFileName, bool IsOpenFile)

{

int id = 0;

bool ExportSuccess = false;

//如果网格尚未数据绑定

if (ExportGrid == null)

{

return false;

}

//Excel2003 工作表大小 65,536 行乘以 256 列

if (ExportGrid.Rows.Count > 65536 || ExportGrid.ColumnCount > 256)

{

return false;

}

// 列索引，行索引

int colIndex = 0;

int rowIndex = 0;

//总可见列数，总可见行数

int colCount =

ExportGrid.Columns.GetColumnCount(DataGridViewElementStates.Visible);

int rowCount =

ExportGrid.Rows.GetRowCount(DataGridViewElementStates.Visible);

if (rowCount == 0 || colCount == 0) //如果DataGridView中没有行，返回{

return false;

}

// 创建Excel对象

Microsoft.Office.Interop.Excel.Application xlApp = new

Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)

{

return false;

}

// 创建Excel工作薄

Microsoft.Office.Interop.Excel.Workbook xlBook =

xlApp.Workbooks.Add(true);

Microsoft.Office.Interop.Excel.Worksheet xlSheet =

(Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets[1]; Microsoft.Office.Interop.Excel.Range range;

IntPtr t = new IntPtr(xlApp.Hwnd);

// 创建缓存数据

object[,] objData = new object[rowCount + 1, colCount];

// 获取列标题，隐藏的列不处理

for (int i = 0; i < ExportGrid.ColumnCount; i++)

{

if (ExportGrid.Columns[i].Visible)

{

objData[rowIndex, colIndex++] = ExportGrid.Columns[i].HeaderText;

if (ExportGrid.Columns[i].ValueType.ToString() == "System.String")

{

//设置成文本型,有效避免将前置的0自动删除了

range = xlSheet.get_Range(xlApp.Cells[4, colIndex], xlApp.Cells[rowCount + 4, colIndex]);

range.NumberFormat = "@";

}

}

}

// 获取数据，隐藏的列的数据忽略

for (int i = 1; i <= rowCount; i++)

{

rowIndex++;

colIndex = 0;

for (int j = 0; j < ExportGrid.ColumnCount; j++)

{

if (ExportGrid.Columns[j].Visible)

objData[rowIndex, colIndex++] = ExportGrid[j, rowIndex - 1].Value;

}

}

Application.DoEvents();

}

// 写入Excel

range = xlSheet.get_Range(xlApp.Cells[4, 1], xlApp.Cells[rowCount + 4, colCount]);

range.Value2 = objData;

// 保存

try

{

//设置报表表格为最适应宽度

xlApp.Cells.EntireColumn.AutoFit();

xlApp.Cells.VerticalAlignment =

Microsoft.Office.Interop.Excel.Constants.xlCenter;

xlApp.Cells.HorizontalAlignment =

Microsoft.Office.Interop.Excel.Constants.xlLeft;

range.Borders.LineStyle =

Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

xlApp.ErrorCheckingOptions.BackgroundChecking = false;

//设置列头格式

range = xlSheet.get_Range(xlApp.Cells[4, 1], xlApp.Cells[4, colCount]);

range.Font.Bold = true;

range.HorizontalAlignment =

Microsoft.Office.Interop.Excel.Constants.xlCenter;

#region "关东添加图形控制代码"

try

{

if (ExportGrid.HRChar != null)

{

Dundas.Charting.WinControl.Chart MyChar;

MyChar = ExportGrid.HRChar;

HS.Audit.Utilite.FileOperate fo = new HS.Audit.Utilite.FileOperate();

string tempFile = fo.GetNewFileFullName(Application.StartupPath + @"\TempFile\HRTemp.bmp");

if (MyChar.ChartAreas.Count > 0)

{

MyChar.SaveAsImage(tempFile, System.Drawing.Imaging.ImageFormat.Bmp); Application.DoEvents();

Clipboard.SetImage(System.Drawing.Image.FromFile(tempFile));

}

int maxRow = https://www.sodocs.net/doc/5d5545801.html,edRange.Rows.Count;

range = (Microsoft.Office.Interop.Excel.Range)xlSheet.Cells[maxRow + 2, 1];

range.Select();

xlSheet.Paste(System.Type.Missing,

System.Type.Missing);//((Microsoft.Office.Interop.Excel.ParseLine ,Dest ination

//range.Parse(System.Type.Missing,System.Type.Missing);

}

}

catch

#endregion

#region "添加台头信息"

try

{

Microsoft.Office.Interop.Excel.Range rangeT;

rangeT = xlSheet.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, colCount]);

rangeT.Merge(System.Type.Missing);

rangeT.set_Value(System.Type.Missing, UserPrinciple.ProjectName);

rangeT.Font.Size = 16;

rangeT.Font.Bold = true;

rangeT.HorizontalAlignment =

Microsoft.Office.Interop.Excel.Constants.xlCenter;

rangeT.RowHeight = 32;

rangeT = xlSheet.get_Range(xlApp.Cells[3, 1], xlApp.Cells[3, 3]);

rangeT.Merge(System.Type.Missing);

rangeT.set_Value(System.Type.Missing, "编制日期：" +

System.DateTime.Now.ToLongDateString());

rangeT = xlSheet.get_Range(xlApp.Cells[3, colCount - 1], xlApp.Cells[3, colCount]);

rangeT.Merge(System.Type.Missing);

rangeT.set_Value(System.Type.Missing, "编制人员：" +

UserPrinciple.LoginUserName);

xlApp.ActiveWindow.DisplayGridlines = false;

}

catch

#endregion

xlBook.Saved = true;

xlBook.SaveCopyAs(fullFileName);

ExportSuccess = true;

}

catch

{

ExportSuccess = false;

}

finally

{

//释放资源，关闭进程

xlApp.Quit();

GetWindowThreadProcessId(t, out id);

System.Diagnostics.Process p =

System.Diagnostics.Process.GetProcessById(id);

p.Kill();

}

if (IsOpenFile == true)

{

HS.Audit.Utilite.FileOperate FO = new HS.Audit.Utilite.FileOperate(); FO.OpenFile(fullFileName);

}

return ExportSuccess;

}