//调用
public ActionResult GenerateExcel() {
DataTable headerTable = new DataTable();
headerTable.Columns.Add("Status_Id", Type.GetType("System.String"));
headerTable.Columns.Add("Status_Name", Type.GetType("System.String"));
headerTable.Columns.Add("Is_Now", Type.GetType("System.String"));
headerTable.Columns.Add("Curr_User_Id", Type.GetType("System.String"));
headerTable.Columns.Add("Flow_Id", Type.GetType("System.String"));

DataRow headerRow = headerTable.NewRow();
headerRow["Status_Id"] = "Status_Id";
headerRow["Status_Name"] = "Status_Name";
headerRow["Is_Now"] = "Is_Now";
headerRow["Curr_User_Id"] = "Curr_User_Id";
headerRow["Flow_Id"] = "Flow_Id";
headerTable.Rows.Add(headerRow);

DataTable contentTable = getDataByProc();

generateExcelOP(headerTable, contentTable);
ViewData["result"] = "Create successfully";
return View("ExcelUpload");
}

//生成Excel

public void generateExcelOP(DataTable headerTable,DataTable dt) {

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet() as HSSFSheet;

//excel header info
HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
for(int i = 0 ;i < headerTable.Columns.Count;i++){
HSSFCell headerCell = headerRow.CreateCell(i) as HSSFCell;
headerCell.SetCellValue(headerTable.Rows[0][i].ToString());
headerRow.Cells.Add(headerCell);
}

//excel content info
for(int m =0 ;m < dt.Rows.Count;m++){
HSSFRow row = sheet.CreateRow((m+1)) as HSSFRow;
for (int n = 0; n < dt.Columns.Count;n++ )
{
HSSFCell cell = row.CreateCell(n) as HSSFCell;
cell.SetCellValue(dt.Rows[m][n].ToString());
row.Cells.Add(cell);
}
}

FileStream fs = new FileStream(@"D:\data\output.xls", FileMode.Create);
workbook.Write(fs);
fs.Flush();
fs.Close();
workbook.Clear();
}

//获取数据

public DataTable getDataByProc() {
DataTable dt = new DataTable();
string callName = "GetAuthHistory";
string devConn = System.Configuration.ConfigurationManager.ConnectionStrings["devConn"].ToString();
using(SqlConnection conn = new SqlConnection(devConn)){
SqlCommand command = new SqlCommand(callName, conn);
command.CommandType = CommandType.StoredProcedure;
SqlParameter[] sps = {
new SqlParameter("@currPage",SqlDbType.Int),
new SqlParameter("@pageSize",SqlDbType.Int)
};
sps[0].Value = 1;
sps[1].Value = 20;
foreach(SqlParameter sp in sps){
command.Parameters.Add(sp);
}
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = command;
sda.Fill(dt);
}

return dt;
}