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

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