/// <summary>
/// 将指定的Dataset导出到Excel文件
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
DataTable dt = (DataTable)fmt.GetFormatData_(re);
string[] str = new string[2];
str[0] = "权限名称";
str[1] = "用户";
DataSet ds = new DataSet();
ds.Tables.Add(dt.Copy());
QXJS.qXJS.ExportToExcel(ds, "权限用户", str);
public bool ExportToExcel(DataSet ds, string ReportName, string[] columnName)
{
if (ds.Tables[0].Rows.Count == 0)
{
MessageBox.Show("数据集为空");
}
Microsoft.Office.Interop.Excel._Application xlapp = new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
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 = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, ds.Tables[0].Columns.Count]);
range.MergeCells = true;
xlapp.ActiveCell.FormulaR1C1 = ReportName;
xlapp.ActiveCell.Font.Size = 20;
xlapp.ActiveCell.Font.Bold = true;
xlapp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
xlapp.Visible = true;
int colIndex = 0;
int RowIndex = 2;
//开始写入每列的标题
foreach (string dc in columnName)
{
colIndex++;
xlsheet.Cells[RowIndex, colIndex] = dc;
}
//开始写入内容
string flag = null;
int RowCount = ds.Tables[0].Rows.Count;//行数
for (int i = 0; i < RowCount; i++)
{
RowIndex++;
int ColCount = ds.Tables[0].Columns.Count;//列数
for (colIndex = 1; colIndex <= ColCount; colIndex++)
{
if (i == 0 && colIndex == 1)
{
flag = ds.Tables[0].Rows[i][colIndex - 1].ToString();
}
if (colIndex == 1)
{
string value = ds.Tables[0].Rows[i][colIndex - 1].ToString();
if (flag != value)
{
RowIndex++;
xlsheet.Cells[RowIndex, 1] = ds.Tables[0].Rows[i][1 - 1].ToString();
xlsheet.Cells[RowIndex, 2] = ds.Tables[0].Rows[i][2 - 1].ToString();
flag = value;
break;
}
else
{
xlsheet.Cells[RowIndex, colIndex] = value;
}
}
else
{
xlsheet.Cells[RowIndex, colIndex] = ds.Tables[0].Rows[i][colIndex - 1].ToString();
}
}
}
xlbook.Saved = true;
xlbook.SaveCopyAs(filePath+".xls");
xlapp.Quit();
GC.Collect();
MessageBox.Show("导出完成");
return true;
}
catch (Exception e1)
{
MessageBox.Show(e1.Message + "/ExportToExcel/导出excel");
return false;
}
}