首页 > 代码库 > asp.net DataTable导出 excel的方法记录(第三方)
asp.net DataTable导出 excel的方法记录(第三方)
官网:http://npoi.codeplex.com/
简单应用,主要是可以实现我们想要的简单效果,呵呵
需要引入dll,可以在官网下载,也可在下面下载
C#代码
- protected void getExcel(DataTable dt)
- {
- NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
- NPOI.SS.UserModel.Sheet sheet = book.CreateSheet("test_01");
- NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
- for (int i = 0; i < dt.Columns.Count; i++)
- {
- row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
- }
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
- for (int j = 0; j < dt.Columns.Count; j++)
- row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
- }
- //写入到客户端
- System.IO.MemoryStream ms = new System.IO.MemoryStream();
- book.Write(ms);
- Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
- Response.BinaryWrite(ms.ToArray());
- book = null;
- ms.Close();
- ms.Dispose();
- }
EXCEL导入
C#代码
- HSSFWorkbook hssfworkbook;
- #region
- public DataTable ImportExcelFile(string filePath)
- {
- #region//初始化信息
- try
- {
- using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
- {
- hssfworkbook = new HSSFWorkbook(file);
- }
- }
- catch (Exception e)
- {
- throw e;
- }
- #endregion
- NPOI.SS.UserModel.Sheet sheet = hssfworkbook.GetSheetAt(0);
- System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
- DataTable dt = new DataTable();
- for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
- {
- dt.Columns.Add(Convert.ToChar(((int)‘A‘) + j).ToString());
- }
- while (rows.MoveNext())
- {
- HSSFRow row = (HSSFRow)rows.Current;
- DataRow dr = dt.NewRow();
- for (int i = 0; i < row.LastCellNum; i++)
- {
- NPOI.SS.UserModel.Cell cell = row.GetCell(i);
- if (cell == null)
- {
- dr[i] = null;
- }
- else
- {
- dr[i] = cell.ToString();
- }
- }
- dt.Rows.Add(dr);
- }
- return dt;
- }
- #endregion
这样就可以读取一个EXCEL的文件了返回一个Datatable
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。