.net项目中经常会要导入导出excel,ExcellLibrary是第三方开发的开源的一个操作excel的插件,项目中引用它在服务器上都不需要装office就能直接读出excel。
导出的code:
- public void ExportExcel(string excleFileName, DataSet dt)
- {
- HttpContext context = HttpContext.Current;
- context.Response.Clear();
- HttpContext.Current.Response.Charset = "GB2312";
- HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
- context.Response.AddHeader(
- "content-disposition", string.Format("attachment; filename={0}", excleFileName+".xls"));
- context.Response.ContentType = "application/ms-excel";
- MemoryStream m = new MemoryStream();
- ExcelLibrary.DataSetHelper.CreateWorkbook(m,dt);
- context.Response.BinaryWrite(m.ToArray());
- context.Response.End();
- }
导入的code
- /// <summary>
- /// 通过插件读取Excel内容到DataTable,服务器上不用装excel
- /// </summary>
- /// <param name="path">xls在服务器上的绝对路径</param>
- /// <param name="fields">要读取的字段,以,分割,至少要有一个字段</param>
- /// <param name="dateCulunms">标出时间类型的列,为空忽略,通过组件读出来的时间类型跟系统时间不一样,所以要指出来做一下转换, DateTime.FromOADate(t);</param>
- /// <param name="cueInfo"></param>
- /// <returns>DataTable,为null读取失败</returns>
- public static DataTable GetDataListFromExcelByExelLibrary(string path, string fields, ArrayList dateCulunms, ref string cueInfo)
- {
- DataTable dt = new DataTable();
- dt = ExcelLibrary.DataSetHelper.CreateDataTable(path, "Sheet1");
- if (dt == null || dt.Rows.Count <= 0)
- {
- cueInfo = "从Excel读取数据失败!";
- return null;
- }
- else
- {
- string[] fd = fields.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
- //检测要取的数据列,是否存在。
- foreach (string s in fd)
- {
- if (!dt.Columns.Contains(s))
- {
- cueInfo = string.Format("Excel不存在 {0} 列,读取数据失败!",s);
- return null;
- }
- }
- }
- //遍历DataTable,将时间类型的列做一下转化(组件读出来的时间类型的数据是一些数字,需要转换成系统时间格式)
- if (dateCulunms != null && dateCulunms.Count > 0)
- {
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- foreach (string timeName in dateCulunms)//遍历多遍
- {
- if (!dt.Columns.Contains(timeName))
- {
- continue;
- }
- object stime = dt.Rows[i][timeName];
- double t;
- bool flag = double.TryParse(stime + "", out t);
- DateTime dtime;
- if (flag)
- {
- dtime = DateTime.FromOADate(t);
- }
- else
- {
- if (!DateTime.TryParse(stime + "", out dtime))
- {
- cueInfo = "Excel日期格式有误,读取数据失败!";
- return null;
- }
- }
- dt.Rows[i][timeName] = dtime;
- //DateTime.FromOADate(Convert.ToDouble(dt.Rows[i]["发货日期"]));
- }
- }
- }
- return dt;
- }
excellibary.dll下载地址 点击打开链接