Call the stored procedure to store data in Excel through NPOI

// Call
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 ");
}

// Generate an Excel file

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 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 ();
}

// Obtain data

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;
}