<rp id="wnpn7"><ruby id="wnpn7"></ruby></rp>
<progress id="wnpn7"><track id="wnpn7"><rt id="wnpn7"></rt></track></progress>
<ruby id="wnpn7"></ruby>
<ruby id="wnpn7"><blockquote id="wnpn7"><div id="wnpn7"></div></blockquote></ruby>

    1. <em id="wnpn7"><ruby id="wnpn7"><input id="wnpn7"></input></ruby></em>
      1. <button id="wnpn7"><acronym id="wnpn7"></acronym></button><button id="wnpn7"><acronym id="wnpn7"></acronym></button>

        <rp id="wnpn7"><acronym id="wnpn7"></acronym></rp>

        <li id="wnpn7"><object id="wnpn7"><u id="wnpn7"></u></object></li>
        VB.net 2010 視頻教程 VB.net 2010 視頻教程 python基礎視頻教程
        SQL Server 2008 視頻教程 c#入門經典教程 Visual Basic從門到精通視頻教程
        當前位置:
        首頁 > 編程開發 > .net教程 >
        • ASP.net教程之EPPlus

        EPPlus

         
        復制代碼
         public class EPPlus
            {
                public static string ExcelContentType
                {
                    get
                    {
                        return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    }
                }
        
                #region 示例
                //public FileContentResult Export()
                //{
                //    var queryJson = Request.Params["queryJson"] + "";
                //    DataTable dt = new EternalService().GetMenuList(JObject.Parse(queryJson));
                //    goto ddd;
                //    ddd:
                //    byte[] filecontent = ExcelExportHelper.ExportExcel(dt, "", false);
                //    return File(filecontent, ExcelExportHelper.ExcelContentType, "MyMenu.xlsx");
                //}
                #endregion
                /// <summary>
                /// 導出Excel
                /// </summary>
                /// <param name="dataTable">數據源</param>
                /// <param name="heading">工作簿Worksheet</param>
                /// <param name="showSrNo">//是否顯示行編號</param>
                /// <param name="columnsToTake">要導出的列</param>
                /// <returns></returns>
                public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
                {
                    //建立Excel
                    byte[] result = null;
                    using (ExcelPackage package = new ExcelPackage())
                    {
                        ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(string.Format("{0}Data", heading));
                        //workSheet.Workbook.Properties.Title = "inventory";//設置excel的標題
                        workSheet.Workbook.Properties.Author = "涼生";//作者
                        //workSheet.Workbook.Properties.Comments = "this is a test";//備注
                        workSheet.Workbook.Properties.Company = "xin";//公司
        
                        int startRowFrom = string.IsNullOrEmpty(heading) ? 1 : 3;  //開始的行
                        //是否顯示行編號
                        if (showSrNo)
                        {
                            DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
                            dataColumn.SetOrdinal(0);
                            int index = 1;
                            foreach (DataRow item in dataTable.Rows)
                            {
                                item[0] = index;
                                index++;
                            }
                        }
        
                        //在Excel文件中添加內容
                        workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
                        // 小單元格的內容自動調整寬度
                        int columnIndex = 1;
                        foreach (DataColumn item in dataTable.Columns)
                        {
                            ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row + 1, columnIndex, workSheet.Dimension.End.Row, columnIndex];
                            string maxValue = columnCells.Max(cell => (cell.Value ?? "").ToString());
                            int maxLength = maxValue.Count();
                            if (maxLength < 150) workSheet.Column(columnIndex).AutoFit();//自動調整寬度
                            else workSheet.Column(columnIndex).Style.WrapText = true; //自動換行
                            if (Regex.IsMatch(maxValue, @"^[+-]?\d*[.]?\d*$"))//驗證是否為數字字符串
                            {
                                workSheet.Column(columnIndex).Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中
                            }
                            if (item.DataType.Equals(Type.GetType("System.DateTime")))//驗證是否是時間
                            {
                                workSheet.Column(columnIndex).AutoFit();
                                workSheet.Column(columnIndex).Style.Numberformat.Format = "yyyy-MM-dd HH:mm:ss";
                            }
                            columnIndex++;
                        }
                        // 格式標題-粗體,黃色的黑色
                        using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
                        {
                            r.Style.Font.Color.SetColor(System.Drawing.Color.White);
                            r.Style.Font.Bold = true;
                            r.Style.Fill.PatternType = ExcelFillStyle.Solid;
                            r.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#1fb5ad"));
                        }
        
                        // 格式化單元格-添加邊框 
                        using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
                        {
                            r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
                            r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                            r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
                            r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
        
                            r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
                            r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
                            r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
                            r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
                        }
        
                        // 刪除忽略的列
                        for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
                        {
                            if (i == 0 && showSrNo)
                            {
                                continue;
                            }
                            if (columnsToTake.Contains(dataTable.Columns[i].ColumnName))
                            {
                                workSheet.DeleteColumn(i + 1);
                            }
                        }
        
                        if (!String.IsNullOrEmpty(heading))
                        {
                            workSheet.Cells["A1"].Value = heading;
                            workSheet.Cells["A1"].Style.Font.Size = 20;
        
                            workSheet.InsertColumn(1, 1);
                            workSheet.InsertRow(1, 1);
                            workSheet.Column(1).Width = 5;
                        }
        
                        result = package.GetAsByteArray();
        
                    }
                    return result;
                }
        
        
                #region 示例
        
                // public ActionResult UploadExcle(HttpPostedFileBase file)
                //{
                //    if (file == null) return Content("沒有文件!", "text/plain");
                //    var sourcePath = Path.Combine(Server.MapPath("~/tempFiles/"), Path.GetFileName(file.FileName));
                //    //建立臨時傳輸文件夾
                //    if (!Directory.Exists(Path.GetDirectoryName(sourcePath)))
                //        Directory.CreateDirectory(sourcePath);
                //    file.SaveAs(sourcePath);
                //    DataSet dt = ExcelToTable.ReadExcelToDataSet(sourcePath);
                //    //處理數據
                //    //。。。。。。
                //    //刪除文件
                //    System.IO.File.Delete(sourcePath);
                //    return Content("");
                //} 
                #endregion
                /// <summary>
                /// 導入
                /// </summary>
                /// <param name="filePath"></param>
                /// <returns></returns>
                public static DataSet ReadExcelToDataSet(string filePath)
                {
                    DataSet ds = new DataSet("ds");
                    DataRow dr;
                    object objCellValue;
                    string cellValue;
                    using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
                    using (ExcelPackage package = new ExcelPackage())
                    {
                        package.Load(fs);
                        foreach (var sheet in package.Workbook.Worksheets)
                        {
                            if (sheet.Dimension == null) continue;
                            var columnCount = sheet.Dimension.End.Column;//獲取worksheet的列數
                            var rowCount = sheet.Dimension.End.Row; //獲取worksheet的行數
                            if (rowCount > 0)
                            {
                                DataTable dt = new DataTable(sheet.Name);
                                for (int j = 0; j < columnCount; j++)//設置DataTable列名  
                                {
                                    objCellValue = sheet.Cells[1, j + 1].Value;
                                    cellValue = objCellValue == null ? "" : objCellValue.ToString();
                                    dt.Columns.Add(cellValue, typeof(string));
                                }
                                for (int i = 2; i <= rowCount; i++)
                                {
                                    dr = dt.NewRow();
                                    for (int j = 1; j <= columnCount; j++)
                                    {
                                        objCellValue = sheet.Cells[i, j].Value;
                                        cellValue = objCellValue == null ? "" : objCellValue.ToString();
                                        dr[j - 1] = cellValue;
                                    }
                                    dt.Rows.Add(dr);
                                }
                                ds.Tables.Add(dt);
                            }
                        }
                    }
                    return ds;
        
                }
            }
        復制代碼

        相關教程
                
        免费看成年人视频大全_免费看成年人视频在线观看