csharp: Importing or Exporting Data from Worksheets using aspose cell

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123

/// <summary>     /// 涂聚文     /// 20150728     /// EXCEL win7 32位,64位OK     /// </summary>     public class ExcelHelperImport     {           /*  http://www.cnblogs.com/wangrsh2010/archive/2012/03/21/2410182.html  * http://npoi.codeplex.com/SourceControl/latest  * http://sourceforge.net/projects/myxls/ http://svn.code.sf.net/p/myxls/code/trunk myxls-code  */           /// <summary>         ///         /// </summary>         /// <param name="strFileName"></param>         /// <param name="inumber"></param>         /// <returns></returns>         public static System.Data.DataTable ReadExcel(String strFileName,int inumber)         {             Workbook book = new Workbook();             book.Open(strFileName); //过时             Worksheet sheet = book.Worksheets[inumber];             Cells cells = sheet.Cells;             return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);         }         /// <summary>         /// geovindu         /// </summary>         /// <param name="strFileName"></param>         /// <param name="num"></param>         /// <returns></returns>         public static DataTable ImportExcel(string strFileName, int num)         {             Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(strFileName);               ////Creating a file stream containing the Excel file to be opened             //FileStream fstream = new FileStream(strFileName, FileMode.Open);             ////Instantiating a Workbook object             ////Opening the Excel file through the file stream             //Workbook workbook = new Workbook(fstream);               //Accessing the first worksheet in the Excel file             Worksheet worksheet = workbook.Worksheets[num];             Cells cells = worksheet.Cells;             //Exporting the contents of 7 rows and 2 columns starting from 1st cell to DataTable             //DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, 7, 2, true);             DataTable dataTable = worksheet.Cells.ExportDataTable(0, 0, cells.MaxDataRow+1 , cells.MaxDataColumn+1 , false);             //fstream.Close();             return dataTable;           }           /// <summary>         /// geovindu 涂聚文         /// </summary>         /// <typeparam name="T"></typeparam>         /// <param name="data"></param>         /// <param name="response"></param>         private static void Export<T>(IEnumerable<T> data, HttpResponse response,string filename)         {             Workbook workbook = new Workbook();             Worksheet sheet = (Worksheet)workbook.Worksheets[0];               PropertyInfo[] ps = typeof(T).GetProperties();             var colIndex = "A";               foreach (var p in ps)             {                   sheet.Cells[colIndex + 1].PutValue(p.Name);                 int i = 2;                 foreach (var d in data)                 {                     sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));                     i++;                 }                   colIndex = ((char)(colIndex[0] + 1)).ToString();             }               response.Clear();             response.Buffer = true;             response.Charset = "utf-8";             response.AppendHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");             response.ContentEncoding = System.Text.Encoding.UTF8;             response.ContentType = "application/ms-excel";             response.BinaryWrite(workbook.SaveToStream().ToArray());             response.End();         }           /// <summary>         /// Geovin Du         /// </summary>         /// <param name="dataTable"></param>         /// <param name="fileName"></param>         public static void ExportToExcel(DataTable dataTable, string fileName)         {             HttpContext context = HttpContext.Current;             context.Response.Clear();             foreach (DataColumn column in dataTable.Columns)             {                 context.Response.Write(column.ColumnName + ",");             }             context.Response.Write(Environment.NewLine);                foreach (DataRow row in dataTable.Rows)             {                 for (int i = 0; i < dataTable.Columns.Count; i++)                 {                     context.Response.Write(row[i].ToString() + ",");                 }                 context.Response.Write(Environment.NewLine);             }             context.Response.ContentType = "application / ms - excel";             context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + fileName + ".csv");             context.Response.End();         }         }

　　from: http://www.aspose.com/.net/excel-component.aspx

https://github.com/heavenwing/WeiXinSDK

https://github.com/heavenwing/MyWeChatPublic

https://github.com/geffzhang/opendotnet

https://github.com/jrsoftware/issrc

 

http://sourceforge.net/projects/ibatisnet/files/ibatisnet/

http://sourceforge.net/projects/nhibernate/files/?source=navbar

http://sourceforge.net/projects/castleproject/files/?source=navbar

https://github.com/castleproject/

 

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

/// <summary>        /// 获取工作表名称        /// </summary>        /// <param name="strFileName"></param>        /// <returns></returns>        private  static DataTable getDataSheetName(string strFileName)        {            DataTable dt = new DataTable();            dt.Columns.Add("id", typeof(int));            dt.Columns.Add("name", typeof(string));           // Aspose.Cells.Workbook workbook = new Workbook();//4.0            //workbook.Open(strFileName);//4.0            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(strFileName);// 7.0            for(int i=0;i<workbook.Worksheets.Count;i++)            {                dt.Rows.Add(i, workbook.Worksheets[i].Name);            }            return dt;        }          /// <summary>        /// 获取工作表名称        /// </summary>        /// <param name="strFileName"></param>        /// <param name="com"></param>        public static void getSheetName(String strFileName, System.Windows.Forms.ComboBox com)        {            DataTable dt = getDataSheetName(strFileName);            com.DataSource = dt;            com.DisplayMember = "name";            com.ValueMember = "id";            com.AutoCompleteMode = AutoCompleteMode.Suggest;            com.AutoCompleteSource = AutoCompleteSource.ListItems;            KillExcelProceed();        }          /// <summary>        ///        /// </summary>        /// <param name="strFileName">文件</param>        /// <param name="inumber">第几个工作表</param>        /// <returns></returns>        public static System.Data.DataTable ReadExcel(String strFileName, int inumber)        {            Aspose.Cells.Workbook book = new Aspose.Cells.Workbook(strFileName);// 7.0            //Workbook book = new Workbook();            //book.Open(strFileName); // 4.0 过时            //book.Worksheets.Count;            Worksheet sheet = book.Worksheets[inumber];            Cells cells = sheet.Cells;            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);        }