Asp.net mvc NPOI generates an Excel file, mvcnpoi

Asp.net mvc NPOI generates an Excel file, mvcnpoi

Private string PushToDown (string addtime) {DataTable dt = _ bCreateCode. pushtoExcel (addtime); // 1. instantiate the workbook object HSSFWorkbook hssfworkbook = new HSSFWorkbook (); // 2. Create the document summary information DocumentSummaryInformation dsf = PropertySetFactory. createDocumentSummaryInformation (); dsf. company = "Company name"; // Company dsf. category = "Category"; // Category // CustomProperties custom property SummaryInformation si = PropertySetFactory. createSummaryInfor Mation (); si. author = "Author"; // Author si. subject = "Serial Number"; // topic si. title = "Serial Number list"; // Title // si. revNumber = "1.0"; // version number // 3. Assign the prepared document abstract to the workbook object hssfworkbook. documentSummaryInformation = dsf; hssfworkbook. summaryInformation = si; // 4. Create Sheet HSSFSheet sheet1 = (HSSFSheet) hssfworkbook. createSheet ("Sheet1"); // HSSFSheet Sheet2 = (HSSFSheet) hssfworkbook. createSheet ("Sheet2"); // HSSFSheet Sheet3 = (HSSFSheet) hs Sfworkbook. createSheet ("Sheet3"); // 5. Create the header and footer sheet1.CreateRow (0 ). createCell (1 ). setCellValue (123); sheet1.Header. center = "Statistical Data"; sheet1.Header. left = "logo.png"; sheet1.Header. right = "address"; sheet1.Footer. center = "page"; // 6. Title: string yeartime = DateTime. today. year + "-" + DateTime. today. month + "-" + DateTime. today. day + "-" + DateTime. now. hour + "-" + DateTime. now. minute + "-" + DateTime. no W. second; HSSFCell fcell = (HSSFCell) sheet1.CreateRow (0 ). createCell (0); // The first fcell. setCellValue (addtime + "serial number List"); // text // merge cell sheet1.AddMergedRegion (new CellRangeAddress (0, 0, 0, 13 )); // 2.0 use Region below 2.0 // Title style HSSFCellStyle fCellStyle = (HSSFCellStyle) hssfworkbook. createCellStyle (); HSSFFont ffont = (HSSFFont) hssfworkbook. createFont (); ffont. fontHeight = 20*20; ffont. fontName = ""; ffont. C Olor = HSSFColor. black. index; fCellStyle. setFont (ffont); fCellStyle. verticalAlignment = NPOI. SS. userModel. verticalAlignment. center; // vertical alignment fCellStyle. alignment = NPOI. SS. userModel. horizontalAlignment. center; // horizontal alignment fcell. cellStyle = fCellStyle; // 7. Set the cell format to create a cell./* simulate setting 7 columns */HSSFDataFormat dataformat = (HSSFDataFormat) hssfworkbook. createDataFormat (); // data format HSSFFont font = (HSSFFont) hssfworkbook. create Font (); // data font. color = HSSFColor. black. index; // color font. isItalic = false; // italic font. isStrikeout = false; // bold font. fontName = ""; // font // it is essential to change the type specified when loop output data needs to be called sqlDbType is more complex // Id int type HSSFCell cell1 = (HSSFCell) sheet1.CreateRow (1 ). createCell (0); // create cell HSSFCellStyle cellStyle1 = (HSSFCellStyle) hssfworkbook. createCellStyle (); // cell style cellStyle1.DataFormat = HSSFDataFormat. getBuiltinFormat ("" ); // CellRangeAddressList ranglist1 = new CellRangeAddressList (0, 65535, 0, 0); // set the specified type // DVConstraint constraint1 = DVConstraint. createNumericConstraint (DVConstraint. validationType. INTEGER, DVConstraint. operatorType. BETWEEN, "0", "100"); // constraint cellStyle1.SetFont (font); cell1.CellStyle = cellStyle1; cell1.SetCellValue (""); // Name HSSFCell cell2 = (HSSFCell) sheet1.CreateRow (1 ). createCell (1); HSSFCe LlStyle cellStyle2 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle2.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle2.SetFont (font); cell2.CellStyle = cellStyle2; cell2.SetCellValue (""); // phone HSSFCell cell3 = (HSSFCell) sheet1.CreateRow (1 ). createCell (2); HSSFCellStyle cellStyle3 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle3.DataFormat = HSSFDataFormat. getBuilt Iterator (""); cellStyle3.SetFont (font); cell3.CellStyle = cellStyle3; cell3.SetCellValue (""); // address HSSFCell cell4 = (HSSFCell) sheet1.CreateRow (1 ). createCell (3); HSSFCellStyle cellStyle4 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle4.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle4.SetFont (font); cell4.CellStyle = cellStyle4; cell4.SetCellValue (""); // Status HSSFCell c Ell5 = (HSSFCell) sheet1.CreateRow (1 ). createCell (4); HSSFCellStyle cellStyle5 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle5.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle5.SetFont (font); cell5.CellStyle = cellStyle5; cell5.SetCellValue (""); // balance HSSFCell cell6 = (HSSFCell) sheet1.CreateRow (1 ). createCell (5); HSSFCellStyle cellStyle6 = (HSSFCellStyle) hssfworkbook. create CellStyle (); cell6.SetCellValue (""); cellStyle6.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle6.SetFont (font); cell6.CellStyle = cellStyle6; // CreateDate HSSFCell cell7 = (HSSFCell) sheet1.CreateRow (1 ). createCell (6); HSSFCellStyle cellStyle7 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle7.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle7.SetFont (font); cell7.Cell Style = cellStyle7; cell7.SetCellValue (""); HSSFCell cell8 = (HSSFCell) sheet1.CreateRow (1 ). createCell (7); HSSFCellStyle cellStyle8 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle8.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle8.SetFont (font); cell8.CellStyle = cellStyle8; cell8.SetCellValue (""); HSSFCell cell9 = (HSSFCell) sheet1.CreateRow (1 ). createCell (8); HSSFCellStyle cel LStyle9 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle9.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle9.SetFont (font); cell9.CellStyle = cellStyle9; cell9.SetCellValue (""); HSSFCell cell10 = (HSSFCell) sheet1.CreateRow (1 ). createCell (9); HSSFCellStyle cellStyle10 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle10.DataFormat = HSSFDataFormat. getBuiltinFormat (""); ce LlStyle10.SetFont (font); cell10.CellStyle = cellStyle10; cell10.SetCellValue (""); HSSFCell cell11 = (HSSFCell) sheet1.CreateRow (1 ). createCell (10); HSSFCellStyle cellStyle11 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle11.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle11.SetFont (font); cell11.CellStyle = cellStyle11; cell11.SetCellValue (""); HSSFCell cell12 = (HSSFCell) sheet 1. createRow (1 ). createCell (11); HSSFCellStyle cellStyle12 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle12.DataFormat = HSSFDataFormat. getBuiltinFormat (""); cellStyle12.SetFont (font); cell12.CellStyle = cellStyle12; cell12.SetCellValue (""); HSSFCell cell13 = (HSSFCell) sheet1.CreateRow (1 ). createCell (12); HSSFCellStyle cellStyle13 = (HSSFCellStyle) hssfworkbook. createCellStyle (); cellStyle 13. dataFormat = HSSFDataFormat. getBuiltinFormat (""); transform (font); cell13.CellStyle = cellStyle13; cell13.SetCellValue (""); // 8. Create a cell and add data HSSFRow r = (HSSFRow) sheet1.CreateRow (1 ); // The title of the second line for (int I = 0; I <dt. columns. count; I ++) {r. createCell (I ). setCellValue (dt. columns [I]. toString ();} if (dt. rows. count> 0) {for (int I = 0; I <dt. rows. count; I ++) {HSSFRow row = (HSSFRow) sheet 1. createRow (I + 2); // write row for (int j = 0; j <dt. columns. count; j ++) // write column {row. createCell (j ). setCellValue (dt. rows [I] [j]. toString () ;}} FileStream fs = new FileStream (Server. mapPath ("~ /PushtoExcel/"+ yeartime +". xls "), FileMode. Create); hssfworkbook. Write (fs); fs. Close (); return yeartime +". xls ";}