Csharp: read excel using Aspose. Cells, csharpaspose. cells

Source: Internet
Author: User

Csharp: read excel using Aspose. Cells, csharpaspose. cells

/// <Summary> ///// </summary> /// <param name = "strFileName"> </param> /// <returns> </returns> public static System. data. dataTable ReadExcel (String strFileName) {Workbook book = new Workbook (strFileName); // book. open (strFileName); // old version Worksheet sheet = book. worksheets [0]; Cells cells = sheet. cells; return cells. exportDataTableAsString (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true );} /// <summary> ///// </summary> /// <param name = "strFileName"> </param> /// <param name = "sheetname ""> </param> // <returns> </returns> public static System. data. dataTable ReadExcel (String strFileName, string sheetname) {Workbook book = new Workbook (strFileName); // book. open (strFileName); // old version Worksheet sheet = book. worksheets [sheetname]; Cells cells = sheet. cells; return cells. exportDataTableAsString (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true );} /// <summary> /// read the worksheet // tu juwen // 20150228 /// </summary> /// <param name = "strFileName"> </ param> // <param name = "comb"> </param> public static void ReadExcelCombox (String strFileName, system. windows. forms. comboBox comb) {comb. items. clear (); Workbook book = new Workbook (strFileName); // book. open (strFileName); // old version Worksheet sheet = book. worksheets [0]; for (int I = 0; I <book. worksheets. count; I ++) {comb. items. add (book. worksheets [I]. name. toString ();} // Cells = sheet. cells; // return cells. exportDataTableAsString (0, 0, cells. maxDataRow + 1, cells. maxDataColumn + 1, true) ;}/// <summary> /// DataTable export data to EXCEL /// http://www.aspose.com/docs/display/cellsnet/Aspose.Cells+Object+Model /// http://www.aspose.com/docs/display/cellsnet/Converting+Worksheet+to+Image+and+Worksheet+to+Image+by+Page /// </Summary> /// <param name = "datatable"> </param> /// <param name = "filepath"> </param> /// <param name = "error"> </param> // <returns> </returns> public static bool DataTableToExcel (DataTable datatable, string filepath, out string error) {error = ""; try {if (datatable = null) {error = "DataTableToExcel: datatable is empty"; return false;} Aspose. cells. workbook workbook = new Aspose. cells. workbook (); Aspose. cells. worksheet sheet = workbook. worksheets [0]; Aspose. cells. cells cells = sheet. cells; int nRow = 0; foreach (DataRow row in datatable. rows) {nRow ++; try {for (int I = 0; I <datatable. columns. count; I ++) {if (row [I]. getType (). toString () = "System. drawing. bitmap ") {// ------ insert image data ------- System. drawing. image image = (System. drawing. image) row [I]; MemoryStream mstream = new MemoryStream (); image. save (mstream, System. drawing. imaging. imageFormat. jpeg); sheet. pictures. add (nRow, I, mstream);} else {cells [nRow, I]. putValue (row [I]) ;}} catch (System. exception e) {error = error + "DataTableToExcel:" + e. message;} workbook. save (filepath); return true;} catch (System. exception e) {error = error + "DataTableToExcel:" + e. message; return false ;}} /// <summary> /// convert the worksheet to an image /// </summary> /// <param name = "file"> source EXCEL file </param> /// <param name = "sheetname"> worksheet name </param> // <param name = "toimagefile"> Generate an image file </param> public static void CellConverImge (string file, string sheetname, string toimagefile) {// Create a new Workbook object and // Open a template Excel file. workbook book = new Workbook (file); // Get the first worksheet. worksheet sheet = book. worksheets [sheetname]; // Define ImageOrPrintOptions imgOptions = new ImageOrPrintOptions (); // Specify the image format imgOptions. imageFormat = System. drawing. imaging. imageFormat. jpeg; // Only one page for the whole sheet wocould be rendered imgOptions. onePagePerSheet = true; // Render the sheet with respect to specified image/print options SheetRender sr = new SheetRender (sheet, imgOptions ); // Render the image for the sheet Bitmap bitmap = sr. toImage (0); // Save the image file specifying its image format. bitmap. save (toimagefile );} /// <summary> ///// </summary> /// <param name = "sURL"> </param> /// <param name = "toExcelFile "> </param> public static void LoadUrlImage (string sURL, string toExcelFile) {// Define memory stream object System. IO. memoryStream objImage; // Define web client object System. net. webClient objwebClient; // Define a string which will hold the web image url // string sURL =" http://files.myopera.com/Mickeyjoe_irl/albums/38458/abc.jpg "; Try {// Instantiate the web client object objwebClient = new System. net. webClient (); // Now, extract data into memory stream downloading the image data into the array of bytes objImage = new System. IO. memoryStream (objwebClient. downloadData (sURL); // Create a new workbook Aspose. cells. workbook wb = new Aspose. cells. workbook (); // Get the first worksheet in the book Aspose. cells. worksheet sheet = wb. Worksheets [0]; // Get the first worksheet pictures collection Aspose. cells. drawing. pictureCollection pictures = sheet. pictures; // Insert the picture from the stream to B2 cell pictures. add (1, 1, objImage); // Save the excel file "d: \ test \ webimagebook.xls" wb. save (toExcelFile);} catch (Exception ex) {// Write the error message on the console Console. writeLine (ex. message) ;}}/// <summa // <summ Ary> // tu juwen // 20150228 // </summary> /// <param name = "sender"> </param> /// <param name = "e"> </param> private void btnFile_Click (object sender, eventArgs e) {try {// bool imail = false; this. cursor = Cursors. waitCursor; openFileDialog1.InitialDirectory = Environment. getFolderPath (Environment. specialFolder. desktop); // JPEG Files (*. jpeg) | *. jpeg | PNG Files (*. png) | *. png | JPG Files (*. jpg) | *. jpg | GIF Files (*. gif) | *. gif openFileDialog1.FileName = ""; openFileDialog1.Filter = "Excel 2000-2003 files (*. xls) | *. xls | Excel 2007 files (*. xlsx) | *. xlsx "; // | (*. xlsx) | *. xlsx Image Files (*. BMP ;*. JPG ;*. GIF) | *. BMP ;*. JPG ;*. GIF | All files (*. *) | *. * txt files (*. txt) | *. txt | All files (*. *) | *. * "openFileDialog1.FilterIndex = 2; openFileDialog1.RestoreDirectory = true; if (openFileDialog1.ShowDialog () = DialogResu Lt. OK) {if (! OpenFileDialog1.FileName. Equals (String. Empty) {// reload and clear data this. combSheet. DataSource = null; if (this. combSheet. Items. Count! = 0) {this. combSheet. items. clear ();} FileInfo f = new FileInfo (openFileDialog1.FileName); if (f. extension. equals (". xls ") | f. extension. equals (". XLS ") | f. extension. equals (". xlsx ") {this. cursor = Cursors. waitCursor; strFileUrl = openFileDialog1.SafeFileName; this.txt FileUrl. text = openFileDialog1.FileName; string currentfilename = openFileDialog1.FileName; this.txt FileUrl. text = currentfilename; // ("463588883@qq.com", "geovindu", "golden Supreme file", "file", currentfilename); // MessageBox. show (imail. toString (); AsposeExcel. readExcelCombox (currentfilename, combSheet); this. cursor = Cursors. default;} else {MessageBox. show ("add file type error") ;}} else {MessageBox. show ("You want to select the exact location of the file") ;}} catch (Exception ex) {ex. message. toString ();} this. cursor = Cursors. default ;} /// <summary> /// import /// </summary> /// <param name = "sender"> </param> /// <param name =" e "> </param> private void btnImport_Click (object sender, eventArgs e) {DataTable dt = new DataTable (); // default first behavior title dt = AsposeExcel.ReadExcel(this.txt FileUrl. text. trim (), this. combSheet. text. trim (); this. dataGridView1.DataSource = dt ;}

  

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.