此方法并不是写Html代码生成伪Excel文件,添加引用 using Microsoft.Office.Interop.Excel;
/// <summary>
/// 导出Grid数据到excel
/// </summary>
/// <param name="gridView"></param>
private void ExportToExcel(Grid gridView)
{
if(gridView.Rows.Count == 0)
{
Alert.Show("No data");
return;
}
//First fetch all records from grid to dataset
DataSet dset = new DataSet();
dset.Tables.Add();
//First Add Columns from gridview to excel
for (int i = 0; i < columns.Length; i++) //GridView is id of gridview
{
dset.Tables[0].Columns.Add(columns[i]);
}
//add rows to the table
System.Data.DataRow dr1;
for (int i = 0; i < gridView.Rows.Count; i++)
{
dr1 = dset.Tables[0].NewRow();
string PortName = gridView.Rows[i].Values[1].ToString();
string BitHi = gridView.Rows[i].Values[2].ToString();
string BitLow = gridView.Rows[i].Values[3].ToString();
string Direction = gridView.Rows[i].Values[4].ToString();
string GroupName = gridView.Rows[i].Values[5].ToString();
string AliasInGroup = gridView.Rows[i].Values[6].ToString();
dr1[0] = PortName;
dr1[1] = BitHi;
dr1[2] = BitLow;
dr1[3] = Direction;
dr1[4] = GroupName;
dr1[5] = AliasInGroup;
dset.Tables[0].Rows.Add(dr1);
}
//below code is export dset to excel
Application excel = new Application();
Workbook wBook;
Worksheet wSheet;
wBook = excel.Workbooks.Add(System.Reflection.Missing.Value);
wSheet = (Worksheet)wBook.ActiveSheet;
System.Data.DataTable dt = dset.Tables[0];
System.Data.DataColumn dc = new DataColumn();
int colIndex = 0;
int rowIndex = 0;
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
excel.Cells[1, colIndex] = dcol.ColumnName;
}
foreach (DataRow drow in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
excel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
}
}
wSheet.Columns.AutoFit();
String strFileName ="d:\\importExcel.xls"; // File Path Where you want to save excel file.
Boolean blnFileOpen = false;
try
{
System.IO.FileStream fileTemp = File.OpenWrite(strFileName);
fileTemp.Close();
}
catch
{
blnFileOpen = false;
}
if (System.IO.File.Exists(strFileName)) //It checks if file exists then it delete that file.
{
System.IO.File.Delete(strFileName);
}
wBook.SaveAs(strFileName, XlFileFormat.xlExcel12, System.Reflection.Missing.Value, System.Reflection.Missing.Value
, false, false, XlSaveAsAccessMode.xlShared, XlSaveConflictResolution.xlLocalSessionChanges, false, System.Reflection.Missing.Value,
System.Reflection.Missing.Value, false);
Alert.Show("File has been saved in " + strFileName);
}