Excel 导入导出类

来源：互联网发布：淘宝禁止出售药方编辑：程序博客网时间：2024/06/10 16:55

  #region 导出到Excel        /// <summary>        /// 导出到Excel        /// </summary>        /// <param name="table">数据源</param>        /// <param name="title">标题</param>        /// <returns></returns>        public static System.IO.MemoryStream ToExcel(DataTable table, string title)        {            IWorkbook workBook = new HSSFWorkbook();            ISheet sheet = workBook.CreateSheet("sheet1");            //处理表格标题            IRow row = sheet.CreateRow(0);            row.CreateCell(0).SetCellValue(title);            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));            row.Height = 500;            ICellStyle cellStyle = workBook.CreateCellStyle();            IFont font = workBook.CreateFont();            font.FontName = "微软雅黑";            font.FontHeightInPoints = 17;            cellStyle.SetFont(font);            cellStyle.VerticalAlignment = VerticalAlignment.Center;            cellStyle.Alignment = HorizontalAlignment.Center;            row.Cells[0].CellStyle = cellStyle;            //处理表格列头            row = sheet.CreateRow(1);            for (int i = 0; i < table.Columns.Count; i++)            {                var curr = row.CreateCell(i);                curr.SetCellValue(table.Columns[i].ColumnName);                curr.CellStyle.VerticalAlignment = VerticalAlignment.Center;                curr.CellStyle.Alignment = HorizontalAlignment.Center;                row.Height = 350;                sheet.AutoSizeColumn(i);            }            //处理数据内容            for (int i = 0; i < table.Rows.Count; i++)            {                row = sheet.CreateRow(2 + i);                row.Height = 250;                for (int j = 0; j < table.Columns.Count; j++)                {                    row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());                    sheet.SetColumnWidth(j, 256 * 15);                }            }            System.IO.MemoryStream ms = new System.IO.MemoryStream();            workBook.Write(ms);            ms.Seek(0, SeekOrigin.Begin);            return ms;        }        #endregion

 #region Oledb方式读取EXCEL        /// <summary>        /// Oledb方式读取EXCEL        /// </summary>        /// <param name="fileNamePath">文件路径</param>        /// <returns></returns>        private DataTable ReadExcelByOledb(string fileNamePath)        {            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + fileNamePath;            //只读第一个表            OleDbConnection oledbconn1 = new OleDbConnection(connStr);            oledbconn1.Open();            DataTable _table = oledbconn1.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });            string strTableName = string.Empty;            if (_table.Rows.Count > 0)            {                strTableName = _table.Rows[0]["TABLE_NAME"].ToString().Trim();                string sql = string.Format("SELECT * FROM [{0}]", strTableName);                _table = new DataTable();                OleDbDataAdapter da = new OleDbDataAdapter(sql, oledbconn1);                da.Fill(_table);            }            oledbconn1.Close();            return _table;        }        #endregion

0 0