Csharp: Export or Import excel using MyXls, csharpmyxls

Csharp: Export or Import excel using MyXls, csharpmyxls

Excel 2003 (unsatisfactory results)

Using System; using System. collections. generic; using System. componentModel; using System. data; using System. drawing; using System. linq; using System. text; using System. windows. forms; using org. in2bits. myXls; using org. in2bits. myXls. byteUtil; using System. IO; using Directory = org. in2bits. myOle2.Directory; using NUnit. framework; using org. in2bits. myOle2; using System. diagnostics; namespace MyxlsDemo {// <summary> // Tu juwen // 20150730 // The effect is not ideal. /// </summary> public partial class Form2: Form {string strFileUrl = ""; /// <summary> ///// </summary> /// <returns> </returns> DataSet setData () {// Create an Emplyee DataTable employeeTable = new DataTable ("Employee"); employeeTable. columns. add ("Employee ID"); employeeTable. columns. add ("Employee Name"); employeeTable. rows. add ("1", "tu juwen"); employee Table. rows. add ("2", "geovindu"); employeeTable. rows. add ("3", "Li Xiaoyi"); employeeTable. rows. add ("4", "zookeeper"); employeeTable. rows. add ("5", "zookeeper "); // Create a Department Table DataTable departmentTable = new DataTable ("Department"); departmentTable. columns. add ("Department ID"); departmentTable. columns. add ("Department Name"); departmentTable. rows. add ("1", "IT"); departmentTable. rows. add ("2", "HR"); departmentTable. rows. add ("3", "Finance"); // Create a DataSet with the existing DataTables DataSet ds = new DataSet ("Organization"); ds. tables. add (employeeTable); ds. tables. add (departmentTable); return ds ;}/// <summary >///// </summary> public Form2 () {InitializeComponent ();} /// <summary> //// </summary> /// <param name = "sender"> </param> /// <param name = "e "> </param> private void Form2_Load (object sender, EventArgs e) {this. dataGridView1.DataSource = setData (). tables [0];} /// <summary> // Excel 2003 // tu juwen // </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 (Environme Nt. specialFolder. desktop); // JPEG Files (*. jpeg) | *. jpeg | PNG Files (*. png) | *. png | JPG Files (*. jpg) | *. jpg | GIF Files (*. gif) | *. gif 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; openFileDia Log1.RestoreDirectory = true; if (openFileDialog1.ShowDialog () = DialogResult. 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 = current Filename; XlsDocument xls = new XlsDocument (currentfilename); DataTable com = new DataTable (); com. columns. add ("id", typeof (int); com. columns. add ("name", typeof (string); // xls. fileName = currentfilename; for (int id = 0; id <xls. workbook. worksheets. count; id ++) {com. rows. add (id, xls. workbook. worksheets [id]. name);} this. combSheet. dataSource = com; this. combSheet. displayMember = "name"; this.com BSheet. valueMember = "id"; Worksheet sheet = xls. workbook. worksheets [0]; DataTable dt = new DataTable (); // xls. workbook. worksheets [0]. name. toString (); int I = 0; int FirstRow = (int) sheet. rows. minRow; if (I = 0) {// write data in every cell in the first row in the first worksheet as the column header (note: in order to write data from xls document in DataTable) for (int j = 1; j <sheet. rows [1]. ce LlCount + 1; j ++) {string ColumnName = Convert. toString (sheet. rows [1]. getCell (ushort. parse (j. toString ())). value); DataColumn column = new DataColumn (ColumnName); dt. columns. add (column) ;}firstrow ++ ;}// write data (not including column header) in datatable rows in sequence for (int k = FirstRow; k <sheet. rows. maxRow + 1; k ++) {Row row = sheet. rows [ushort. parse (k. toString ()]; DataRow dataRow = Dt. newRow (); for (int z = 1; z <sheet. rows [ushort. parse (k. toString ()]. cellCount + 1; z ++) {// write data in the current cell if it exists if (row. getCell (ushort. parse (z. toString ()))! = Null) {dataRow [z-1] = row. getCell (ushort. parse (z. toString ())). value. toString () ;}} dt. rows. add (dataRow);} this. dataGridView1.DataSource = dt; 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> //// </summary> /// <param name = "sender"> </param> /// <param name = "e "> </param> private void btnImport_Click (object sender, eventArgs e) {}/// <summary> //// </summary> /// <param name = "sender"> </param> /// <param name = "e"> </param> private void buttonExport_Click (object sender, eventArgs e) {ExportEasy (setData (). tables [0], "ex.xls ");} /// <summary> /// export /// </summary> /// <param name = "dtSource"> </param> /// <param name =" strFileName "> </param> public static void ExportEasy (DataTable dtSource, string strFileName) {try {XlsDocument xls = new XlsDocument (); Worksheet sheet = xls. workbook. worksheets. add ("Sheet1"); // fill in the header foreach (DataColumn col in dtSource. columns) {sheet. cells. add (1, col. ordinal + 1, col. columnName);} // fill in the content for (int I = 0; I <dtSource. rows. count; I ++) {for (int j = 0; j <dtSource. columns. count; j ++) {sheet. cells. add (I + 2, j + 1, dtSource. rows [I] [j]. toString () ;}// Save the xls. fileName = strFileName; xls. save ();} catch (Exception ex) {ex. message. toString ();}}}}