Use the Aspose plug-in to operate on Excel, and the aspose plug-in excel
Use the Aspose plug-in to import and export Excel documents
Download Aspose plug-in reference before use
Excel import:
The foreground uses the file tag to obtain the information and submits it in submit mode.
<Form id = "form1" enctype = "multipart/form-data" method = "post"> <table class = "table-condensed"> <tr> <td class =" text-right "> Import table: </td> <td class = "text-left"> <input type = "file" name = "file1" class = "btn-default btn-lg"/> </td> </tr> <td class = "text-left"> <input type = "submit" id = "btnImport" name = "btnImport" value =" import "class =" btn-default "/> </td> </tr> </table> </form>
Background reception:
HttpPostedFileBase fileBase = Request. Files ["file1"]; // The obtained name must be consistent with the front-end tag name if (fileBase! = Null) {string filename = Path. getFileName (fileBase. fileName); string extension = Path. getExtension (filename); string path = "/Upload/Test/" + DateTime. now. toString ("yyyyMMdd") + "/"; Directory. createDirectory (Path. getDirectoryName (Request. mapPath (path); string newFilename = DateTime. now. toString ("yyyyMMddHHmmssfff"); string fullFileName = path + newFilename + extension; fileBase. saveAs (Request. mapPath (fullFileName); try {Stopwatch sw = new Stopwatch (); // record the duration of the Import Operation sw. start (); // here you can add the BLL method to process string result = new ProductBLL (). importExcel (Request. mapPath (path), newFilename, extension); // The BLL method ProductBLLpublic string ImportExcel (string path, string filename, string extension)
{
Workbook workbook = new Workbook (path + filename + extension );
Worksheet worksheet = workbook. Worksheets [0];
Cells cells = worksheet. Cells;
For (int I = 1; I <cells. Rows. Count; I ++)
{
Try
{
String brand = cells [I, 0]. StringValue. Trim (); // obtain the column Value
String years = cells [I, 1]. StringValue. Trim ();
}
Catch (Exception e)
{
Continue;
}
}
Return "OK ";
} Sw. stop (); long runTime = sw. elapsedMilliseconds/1000; // number of seconds to obtain the operation} catch (Exception e) {Log. write ("import", "Import error", "error message:" + e. message );}}
Excel export:
String path = "/Upload/Test/" + DateTime. now. toString ("yyyyMMdd") + "/"; Directory. createDirectory (Path. getDirectoryName (Server. mapPath (path); string newFilename = DateTime. now. toString ("yyyyMMddHHmmssfff") + ". xls "; string fullFileName = Server. mapPath (path + newFilename); public void ExportInfo (List <Test> list, string fullFileName) {Aspose. cells. workbook workbook = new Aspose. cells. workbook (); Aspose. cells. worksheet cellSheet = workbook. worksheets [0]; cellSheet. pageSetup. leftMargin = 0.3; // left margin of cellSheet. pageSetup. rightMargin = 0.3; // The right side is located at cellSheet. pageSetup. topMargin = 1; // top margin cellSheet. pageSetup. bottomMargin = 0.5; // bottom margin cellSheet. pageSetup. footerMargin = 0.5; // footer cellSheet. pageSetup. headerMargin = 0.5; // header cellSheet. pageSetup. orientation = PageOrientationType. landscape; cellSheet. pageSetup. centerHorizontally = true; // horizontally centered cellSheet. pageSetup. centerVertically = true; cellSheet. cells [0, 0]. putValue ("sheet"); cellSheet. cells [0, 1]. putValue ("color"); cellSheet. cells [0, 2]. putValue ("size"); int I = 1; foreach (var item in list) {cellSheet. cells [I, 0]. putValue (item. productno); cellSheet. cells [I, 1]. putValue (item. size); cellSheet. cells [I, 2]. putValue (item. color); I ++;} cellSheet. autoFitColumns (); fullFileName = Path. getFullPath (fullFileName); workbook. save (fullFileName );}
Return File (fullFileName, "application/ms-excel", UserName + "_ Test single" + newFilename); // The method Action directly returns the File download.
// Export the DataTable data source and encapsulate the ToExcel method.
Da. ToExcel (fullFileName );
Return File (fullFileName, "application/ms-excel", UserName + "_ Test ticket" + newFilename );
/// <Summary>
/// Save the DataTable data table to Excel
/// </Summary>
/// <Param name = "dt"> data source </param>
/// <Param name = "fullFileName"> full file path </param>
Public static void ToExcel (this System. Data. DataTable dt, string fullFileName)
{
Aspose. Cells. Workbook workbook = new Aspose. Cells. Workbook ();
Aspose. Cells. Worksheet cellSheet = workbook. Worksheets [0];
CellSheet. Name = dt. TableName;
Int rowIndex = 0;
Int colIndex = 0;
Int colCount = dt. Columns. Count;
Int rowCount = dt. Rows. Count;
// Column name Processing
For (int I = 0; I <colCount; I ++)
{
CellSheet. Cells [rowIndex, colIndex]. PutValue (dt. Columns [I]. ColumnName );
// CellSheet. Cells [rowIndex, colIndex]. SetStyle. Font. IsBold = true;
// CellSheet. Cells [rowIndex, colIndex]. Style. Font. Name = "";
ColIndex ++;
}
Aspose. Cells. Style style = workbook. Styles [workbook. Styles. Add ()];
Style. Font. Name = "Arial ";
Style. Font. Size = 10;
Aspose. Cells. StyleFlag styleFlag = new Aspose. Cells. StyleFlag ();
CellSheet. Cells. ApplyStyle (style, styleFlag );
RowIndex ++;
For (int I = 0; I <rowCount; I ++)
{
ColIndex = 0;
For (int j = 0; j <colCount; j ++)
{
CellSheet. Cells [rowIndex, colIndex]. PutValue (dt. Rows [I] [j]. ToString ());
ColIndex ++;
}
RowIndex ++;
}
CellSheet. AutoFitColumns ();
FullFileName = Path. GetFullPath (fullFileName );
Workbook. Save (fullFileName );
}
As part of the code is extracted, no detailed comments are made on the layout and reference. The front-end and back-end Methods correspond to each other.
Make a note for your convenience. Make a note for your convenience. Make a note for your convenience. Make a note for your convenience.