private void populateMentees(string path, RoleType rType) { OfficeOpenXml.ExcelWorksheet ws = readExcel(path); for (int rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)//foreach mentee { var wsRow = ws.Cells[rowNum, ws.Dimension.End.Column]; if (wsRow["B" + rowNum].Text.Trim() == string.Empty) { continue; } Mentee objMentee = new Mentee(); objMentee.Name = wsRow["B" + rowNum].Text.Trim(); objMentee.Dept = setDept(wsRow["C" + rowNum].Text.Trim()); objMentee.UscId = wsRow["D" + rowNum].Text.Trim(); objMentee.Contact = wsRow["E" + rowNum].Text.Trim(); if (!_dicMenteeDepartment.ContainsKey(objMentee.Dept)) { List <Mentee> lstMentees = new List <Mentee>(); lstMentees.Add(objMentee); _dicMenteeDepartment.Add(objMentee.Dept, lstMentees); } else { List <Mentee> lstMentees = _dicMenteeDepartment[objMentee.Dept]; lstMentees.Add(objMentee); } } }
private void cmdImport_tabTILSOFTFile_Click(object sender, EventArgs e) { string uniqueFileName = Path.GetTempPath() + @"\" + System.Guid.NewGuid().ToString().Replace("-", "") + ".xlsx"; string pointer = string.Empty; System.IO.FileInfo epFInfo = new System.IO.FileInfo(uniqueFileName); OfficeOpenXml.ExcelPackage ePackagae = new OfficeOpenXml.ExcelPackage(epFInfo); OfficeOpenXml.ExcelWorksheet pWS = ePackagae.Workbook.Worksheets.Add("Result"); List <DTO.FilePointer> result = new List <DTO.FilePointer>(); foreach (FileInfo fInfo in (new DirectoryInfo(FrameworkSetting.Setting.AbsoluteReportTmpFolder)).GetFiles()) { pointer = string.Empty; DTO.FilePointer fPointer = new DTO.FilePointer() { PhysicalFileName = fInfo.Name }; try { fPointer.FilePointerUD = fwFactory.CreateFilePointer(FrameworkSetting.Setting.AbsoluteReportTmpFolder, fInfo.Name, string.Empty); fwFactory.CreateImageCache(fPointer.FilePointerUD, 120, 120, false); result.Add(fPointer); } catch (Exception ex) { } } pWS.Cells["A1"].LoadFromCollection(result, true); ePackagae.Save(); System.Diagnostics.Process.Start(uniqueFileName); }
public static void VeLogo(string name, OfficeOpenXml.ExcelWorksheet ws) { var p = ws.Drawings.AddPicture(name, Resources.CNS); p.SetPosition(0, 0, 0, 0); p.SetSize(100); }
private List <ImportModel.rawImport> ReadInputFile() { List <ImportModel.rawImport> recordsIn = new List <ImportModel.rawImport>(); var fred = TempData["path"].ToString(); //var filesData = Directory.GetFiles(@fred); string path = Server.MapPath("~/App_Data/" + fred); //string path = Server.MapPath(fred.ToString()); var package = new OfficeOpenXml.ExcelPackage(new FileInfo(path)); OfficeOpenXml.ExcelWorksheet workSheet = package.Workbook.Worksheets[1]; for (int row = workSheet.Dimension.Start.Row; row <= workSheet.Dimension.End.Row; row++) { if (ExcelData.HasData(workSheet, row)) { decimal price = 0.0m; ImportModel.rawImport obj = new ImportModel.rawImport(); obj.Sku = ExcelData.GetPBSKU(workSheet, row); obj.FormSizeCode = ExcelData.GetPBFSCOde(workSheet, row); obj.Name = ExcelData.GetName(workSheet, row); obj.FormSize = ExcelData.GetFSDecription(workSheet, row); price = ExcelData.GetPrice(workSheet, row); obj.FromDate = null; // obj.FromDate = ExcelData.GetDate(workSheet, row); obj.Price = price * 100; obj.Location = "PB"; recordsIn.Add(obj); } } return(recordsIn); }
public void Export(TreeGenerative tree, string path) { FileInfo fi = new FileInfo(path); if (fi.Exists) { fi.Delete(); } _featureNames = tree.Root.Set.GetFeatureNames(); _resolutionFeatureName = tree.ResolutionFeatureName; using (OfficeOpenXml.ExcelPackage xls = new OfficeOpenXml.ExcelPackage(fi)) { OfficeOpenXml.ExcelWorksheet sheet = xls.Workbook.Worksheets.Add("Classified"); for (int j = 0; j < _featureNames.Count; j++) { sheet.Cells[1, j + 1].Value = _featureNames[j]; } sheet.Cells[1, _featureNames.Count + 1].Value = "Category"; sheet.Cells[1, _featureNames.Count + 2].Value = string.Format("Delta({0})", _resolutionFeatureName); ExportRecursion(tree.Root, sheet); xls.Save(); } var exportCompleted = ExportCompleted; if (exportCompleted != null) { exportCompleted(this, fi.Name); } }
public static void ExportExcelByEPPlus(HttpContext context, DataTable dt, string fileName, string headerName) { using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage()) { string sheetName = string.IsNullOrEmpty(dt.TableName) ? "sheet1" : dt.TableName; OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add(sheetName); ws.Cells[2, 1].LoadFromDataTable(dt, true);//从A4的单元格加载datatable中的数据 OfficeOpenXml.Style.ExcelBorderStyle borderStyle = OfficeOpenXml.Style.ExcelBorderStyle.Thin; System.Drawing.Color borderColor = System.Drawing.Color.FromArgb(0, 0, 0); using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, dt.Rows.Count + 2, dt.Columns.Count]) { rng.Style.Font.Name = "宋体"; rng.Style.Font.Size = 11; rng.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255)); rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; rng.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; rng.Style.Border.Top.Style = borderStyle; rng.Style.Border.Top.Color.SetColor(borderColor); rng.Style.Border.Bottom.Style = borderStyle; rng.Style.Border.Bottom.Color.SetColor(borderColor); rng.Style.Border.Right.Style = borderStyle; rng.Style.Border.Right.Color.SetColor(borderColor); } //Format the header row using (OfficeOpenXml.ExcelRange rng = ws.Cells[1, 1, 1, dt.Columns.Count]) //1行1列到1行n列 { rng.Merge = true; //合并单元格 rng.Style.Font.Bold = true; rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255)); rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0)); rng.Value = headerName; } using (OfficeOpenXml.ExcelRange rng = ws.Cells[2, 1, 2, dt.Columns.Count])//1行1列到1行n列 { rng.Style.Font.Bold = true; rng.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(255, 255, 255)); rng.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(0, 0, 0)); } //返回到客户端 context.Response.Clear(); context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; context.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}.xlsx", HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8))); context.Response.ContentEncoding = System.Text.Encoding.UTF8; context.Response.BinaryWrite(pck.GetAsByteArray()); context.Response.Flush(); context.ApplicationInstance.CompleteRequest();//解决捕获的“由于代码已经过优化...”的try catch异常 //HttpContext.Current.Response.End(); } }
public ExcelImport(OfficeOpenXml.ExcelWorksheet workSheet, ContentTypeData typeData) { InitializeComponent(); this.WorkSheet = workSheet; this.TypeData = typeData; Load += ExcelImport_Load; }
public byte[] ExportCSV(string path, List <ProductDTO> data) { string uniqueFileName = FrameworkSetting.Setting.AbsoluteReportTmpFolder + System.Guid.NewGuid().ToString().Replace("-", "") + ".xlsx"; System.IO.FileInfo fInfo = new System.IO.FileInfo(uniqueFileName); OfficeOpenXml.ExcelPackage ePackagae = new OfficeOpenXml.ExcelPackage(fInfo); OfficeOpenXml.ExcelWorksheet pWS = ePackagae.Workbook.Worksheets.Add("Product"); // add data to product sheet var products = from o in data select new { sku = o.ArticleCode , name = o.ModelNM + " (" + o.MaterialColorNM + ")" , description = o.Description , product_type = o.ProductTypeNM , type = o.ProductTypeNM == "SET" ? "bundle" : "simple" , weight = o.NetWeight , price = o.UnitPrice , article_code = o.ArticleCode , sub_ean_code = o.SubEANCode , material_fs = o.MaterialNM , color_fs = o.MaterialColorNM , material_type = o.MaterialTypeNM , frame_material = o.FrameMaterialNM , frame_material_color = o.FrameMaterialColorNM , back_cushion = o.BackCushionNM , seat_cushion = o.SeatCushionNM , cushion_color = o.CushionColorNM , height = o.OverallDimH + "cm" , length = o.OverallDimL + "cm" , width = o.OverallDimW + "cm" , seat_height = o.SeatCushionDimH , seat_length = o.SeatCushionDimL , seat_width = o.SeatCushionDimW , wxlxh_cm = o.OverallDimW + "cm x " + o.OverallDimL + "cm x " + o.OverallDimH + "cm" , carton_box_length = o.CartonBoxDimL , carton_box_height = o.CartonBoxDimH , carton_box_width = o.CartonBoxDimW , qty = o.StockQnt }; pWS.Cells["A1"].LoadFromCollection(products.ToList(), true); ePackagae.Save(); List <Library.DTO.ZipFileDTO> toBeZippedFiles = new List <Library.DTO.ZipFileDTO>(); toBeZippedFiles.Add(new Library.DTO.ZipFileDTO() { FileName = "product", FilePath = uniqueFileName }); data.Where(o => !string.IsNullOrEmpty(o.FileLocation)).ToList().ForEach(o => toBeZippedFiles.Add(new Library.DTO.ZipFileDTO() { FileName = o.ArticleCode, FilePath = FrameworkSetting.Setting.AbsoluteFileFolder + o.FileLocation })); Library.FileHelper.ZipManager zMng = new Library.FileHelper.ZipManager(FrameworkSetting.Setting.AbsoluteReportTmpFolder); return(File.ReadAllBytes(zMng.CreateZipFile(toBeZippedFiles))); }
private object getExcelGateValue(OfficeOpenXml.ExcelWorksheet pWorkSheet, int intRow, enumExcelGateColumn pCol) { if (pWorkSheet == null) { return(null); } return(pWorkSheet.GetValue(intRow, (int)pCol)); }
public static void SaveExcelFromDataTable(string path, DataTable dataTable) { // https://stackoverflow.com/questions/13669733/export-datatable-to-excel-with-epplus using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage(new FileInfo(path))) { OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts"); ws.Cells["A1"].LoadFromDataTable(dataTable, true); pck.Save(); } }
/// <summary> /// 创建修订日志sheet /// </summary> /// <param name="epck"></param> /// <param name="sheetName"></param> /// <param name="tables"></param> private static void CreateLogSheet(OfficeOpenXml.ExcelPackage epck, string sheetName, List <TableDto> tables) { OfficeOpenXml.ExcelWorksheet overviewTbWorksheet = epck.Workbook.Worksheets.Add(sheetName); int row = 1; overviewTbWorksheet.Cells[row, 1, row, 5].Merge = true; //overviewTbWorksheet.Cells[row, 1].Value = "总表数量"; //overviewTbWorksheet.Cells[row, 2].Value = tables.Count + ""; //overviewTbWorksheet.Cells[row, 4].Value = "密码等级"; //overviewTbWorksheet.Cells[row, 5].Value = "秘密"; row++; // 行号+1 overviewTbWorksheet.Cells[row, 1].Value = "版本号"; overviewTbWorksheet.Cells[row, 2].Value = "修订日期"; overviewTbWorksheet.Cells[row, 3].Value = "修订内容"; overviewTbWorksheet.Cells[row, 4].Value = "修订人"; overviewTbWorksheet.Cells[row, 5].Value = "审核人"; overviewTbWorksheet.Cells[row, 1, row, 5].Style.Font.Bold = true; overviewTbWorksheet.Cells[row, 1, row, 5].Style.Font.Size = 10; overviewTbWorksheet.Row(1).Height = 20; // 行高 // TODO 循环日志记录 row++; // 行号+1 for (var i = 0; i < 16; i++) { // TODO 添加列标题 overviewTbWorksheet.Cells[row, 1].Value = ""; overviewTbWorksheet.Cells[row, 2].Value = ""; overviewTbWorksheet.Cells[row, 3].Value = ""; overviewTbWorksheet.Cells[row, 4].Value = ""; overviewTbWorksheet.Cells[row, 5].Value = ""; overviewTbWorksheet.Row(row).Height = 20; // 行高 row++; // 行号+1 } // TODO 水平居中 overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // TODO 垂直居中 overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // TODO 上下左右边框线 overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Cells[1, 1, row - 1, 5].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Column(1).Width = 25; overviewTbWorksheet.Column(2).Width = 25; overviewTbWorksheet.Column(3).Width = 50; overviewTbWorksheet.Column(4).Width = 25; overviewTbWorksheet.Column(5).Width = 25; }
public override void Input(OfficeOpenXml.ExcelWorksheet worksheet, ExcelInputDTO inputInfo) { worksheet.Cells["B2"].Value = inputInfo.Region; worksheet.Cells["B3"].Value = inputInfo.Province; worksheet.Cells["B4"].Value = inputInfo.City; worksheet.Cells["B5"].Value = inputInfo.Market; worksheet.Cells["B6"].Value = inputInfo.StoreName; worksheet.Cells["B7"].Value = inputInfo.USCode; worksheet.Cells["B8"].Value = inputInfo.OpenDate.ToString("yyyy-MM-dd"); worksheet.Cells["B9"].Value = inputInfo.LeaseExpirationDate.ToString("yyyy-MM-dd"); worksheet.Cells["B10"].Value = inputInfo.Priority; }
public static string ReadCellAsString(OfficeOpenXml.ExcelWorksheet worksheet, int rowNum, int columnNum) { object cellValue = ReadCell(worksheet, rowNum, columnNum); if (cellValue == null) { return(""); } else { return(cellValue.ToString()); } }
public ActionResult ExportToExcel() { T_Expense ObjPurchaseOrder = new T_Expense(); var vObjList = _blExpense.GetList(ObjPurchaseOrder); // ExcelPackageF dll Need OfficeOpenXml.ExcelPackage Ep = new OfficeOpenXml.ExcelPackage(); OfficeOpenXml.ExcelWorksheet workSheet = Ep.Workbook.Worksheets.Add("Report"); string sEndColumn = "E1"; workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Font.Bold = true; workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightSeaGreen); var allCells = workSheet.Cells[string.Format("A1:{0}", sEndColumn)]; var cellFont = allCells.Style.Font; workSheet.Cells["A1"].Value = "Date"; //sheet["A3:A4"].Merge(); workSheet.Cells["B1"].Value = "Enpense Type"; workSheet.Cells["C1"].Value = "Amount"; workSheet.Cells["D1"].Value = "Remark"; workSheet.Cells["E1"].Value = "Create Date"; string dateformat = "dd-MM-yyyy"; int row = 2; foreach (var item in vObjList) { workSheet.Cells[string.Format("A{0}", row)].Value = item.EventDate; workSheet.Cells[string.Format("A{0}", row)].Style.Numberformat.Format = dateformat; workSheet.Cells[string.Format("B{0}", row)].Value = item.ExpenseTypeName; workSheet.Cells[string.Format("C{0}", row)].Value = item.Amount; workSheet.Cells[string.Format("D{0}", row)].Value = item.Remark; workSheet.Cells[string.Format("E{0}", row)].Value = item.CreatedDate; workSheet.Cells[string.Format("E{0}", row)].Style.Numberformat.Format = dateformat; row++; } workSheet.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "Expense.csv" + DateTime.Now.ToString("s")); Response.BinaryWrite(Ep.GetAsByteArray()); Response.End(); return(View()); }
public override void Input(OfficeOpenXml.ExcelWorksheet worksheet, ExcelInputDTO inputInfo) { string strRow = (3 + RowNumber).ToString(); worksheet.Cells["A" + strRow].Value = (RowNumber + 1).ToString(); worksheet.Cells["B" + strRow].Value = inputInfo.McdParticipants; worksheet.Cells["C" + strRow].Value = inputInfo.Content; worksheet.Cells["D" + strRow].Value = inputInfo.LLParticipants; worksheet.Cells["E" + strRow].Value = inputInfo.Topic; worksheet.Cells["F" + strRow].Value = inputInfo.Location; worksheet.Cells["G" + strRow].Value = inputInfo.MeetingDate; worksheet.Cells["H" + strRow].Value = inputInfo.CreateDate; RowNumber += 1; }
public static void GhiThongTinTongcty(OfficeOpenXml.ExcelWorksheet ws, string address, int sR1, int sC1, int sizeTCty, int sR2, int sC2, int sizeDiaChi, int sR3, int sC3) { XL.FormatCells(ws.Cells[sR1 + 0, sC1], "TỔNG CÔNG TY CÔNG NGHIỆP SÀI GÒN", VeBorder: false, size: sizeTCty); XL.FormatCells(ws.Cells[sR1 + 1, sC1], "TRÁCH NHIỆM HỮU HẠN MỘT THÀNH VIÊN", VeBorder: false, size: sizeTCty); XL.FormatCells(ws.Cells[sR1 + 2, sC1], "NHÀ MÁY THUỐC LÁ KHÁNH HỘI", VeBorder: false, bold: true, size: sizeTCty); XL.FormatCells(ws.Cells[sR2 + 0, sC2], "Lô 26, đường số 3, KCN Tân Tạo, Quận Bình Tân, Thành phố Hồ Chí Minh", VeBorder: false, size: sizeDiaChi); XL.FormatCells(ws.Cells[sR2 + 1, sC2], "Tel: +848 37507282 - FAX: 37507784", VeBorder: false, size: sizeDiaChi); XL.FormatCells(ws.Cells[sR2 + 2, sC2], "Email: [email protected] Website: http://khcig.com", VeBorder: false, size: sizeDiaChi); var tphcm = "Thành phố Hồ Chí Minh, ngày {0} tháng {1} năm {2}"; tphcm = string.Format(tphcm, DateTime.Today.Day, DateTime.Today.Month, DateTime.Today.Year); XL.FormatCells(ws.Cells[sR3, sC3], tphcm, VeBorder: false, size: sizeDiaChi); }
public static string GetValue(OfficeOpenXml.ExcelWorksheet worksheet, int row, int col) { if (worksheet.Cells[row, col].Value != null) { var value = worksheet.Cells[row, col].Value; Type t = value.GetType(); if (t.Equals(typeof(DateTime))) { DateTime date = (DateTime)value; var strDate = date.Day + "/" + date.Month + "/" + date.Year + " " + date.Hour + ":" + date.Minute; return(strDate); } else { return(worksheet.Cells[row, col].Value.ToString().Trim()); } } return(string.Empty); }
public static string CreateExcelFileWithEPPlus <Unknow>(List <Unknow> data) { string result = string.Empty; string uniqueFileName = System.Guid.NewGuid().ToString().Replace("-", "") + ".xlsx"; System.IO.FileInfo f = new System.IO.FileInfo(Setting.AbsoluteReportTmpFolder + uniqueFileName); if (f.Exists) { f.Delete(); } using (OfficeOpenXml.ExcelPackage ep = new OfficeOpenXml.ExcelPackage(f)) { OfficeOpenXml.ExcelWorksheet eSheet = ep.Workbook.Worksheets.Add("Data"); eSheet.Cells[2, 1].LoadFromCollection(data); ep.Save(); } result = uniqueFileName; return(result); }
private byte[] CreateExcelStream() { System.IO.MemoryStream ms = new System.IO.MemoryStream(); using (System.IO.FileStream fs = System.IO.File.OpenRead(System.AppDomain.CurrentDomain.BaseDirectory.ToString() + "../PlanilhaDeEntrada.xlsx")) { using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(fs)) { OfficeOpenXml.ExcelWorkbook excelWorkBook = excelPackage.Workbook; OfficeOpenXml.ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First(); excelWorksheet.Cells[15, 2].Value = "Ou 'B1'"; excelWorksheet.Cells[15, 3].Value = "Ou 'B2'"; excelWorksheet.Cells[15, 4].Value = "Ou 'B3'"; excelWorksheet.Cells[15, 5].Value = "Ou 'B4'"; excelWorksheet.Cells[15, 6].Value = "Ou 'B5'"; excelWorksheet.Cells[15, 7].Value = "Ou 'B6'"; excelPackage.SaveAs(ms); // This is the important part. return(ms.ToArray()); } } }
/// <summary> /// 创建表目录sheet /// </summary> /// <param name="epck"></param> /// <param name="sheetName"></param> /// <param name="tables"></param> private static void CreateOverviewSheet(OfficeOpenXml.ExcelPackage epck, string sheetName, List <TableDto> tables) { OfficeOpenXml.ExcelWorksheet overviewTbWorksheet = epck.Workbook.Worksheets.Add(sheetName); int row = 1; overviewTbWorksheet.Cells[row, 1].Value = "序号"; overviewTbWorksheet.Cells[row, 2].Value = "表名"; overviewTbWorksheet.Cells[row, 3].Value = "注释/说明"; overviewTbWorksheet.Cells[row, 1, row, 3].Style.Font.Bold = true; overviewTbWorksheet.Cells[row, 1, row, 3].Style.Font.Size = 16; overviewTbWorksheet.Row(1).Height = 30; // 行高 // TODO 循环数据库表名 row++; foreach (var table in tables) { // TODO 数据库名称 // TODO 添加列标题 overviewTbWorksheet.Cells[row, 1].Value = table.TableOrder; overviewTbWorksheet.Cells[row, 2].Value = table.TableName; overviewTbWorksheet.Cells[row, 3].Value = (!string.IsNullOrWhiteSpace(table.Comment) ? table.Comment : ""); overviewTbWorksheet.Row(row).Height = 30; // 行高 row++; // 行号+1 } // TODO 水平居中 overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // TODO 垂直居中 overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // TODO 上下左右边框线 overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Cells[1, 1, row - 1, 3].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; overviewTbWorksheet.Column(1).Width = 10; overviewTbWorksheet.Column(2).Width = 50; overviewTbWorksheet.Column(3).Width = 50; }
private void ExportRecursion(NodeGenerative node, OfficeOpenXml.ExcelWorksheet sheet) { if (node == null) { return; } if (node.IsTerminal) { _isColored = !_isColored; foreach (var item in node.Set.Items()) { for (int i = 0; i < _featureNames.Count; i++) { sheet.Cells[_rowNo, i + 1].Value = item.GetValue(_featureNames[i]); } sheet.Cells[_rowNo, _featureNames.Count + 1].Value = node.Category; double d = node.Average - item.GetValue(_featureNames[0]); sheet.Cells[_rowNo, _featureNames.Count + 2].Value = d; if (d < 0) { sheet.Cells[_rowNo, _featureNames.Count + 2].Style.Font.Color.SetColor(System.Drawing.Color.Red); } else if (d > 0) { sheet.Cells[_rowNo, _featureNames.Count + 2].Style.Font.Color.SetColor(System.Drawing.Color.Green); } if (_isColored) { sheet.Cells[_rowNo, 1, _rowNo, _featureNames.Count + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; sheet.Cells[_rowNo, 1, _rowNo, _featureNames.Count + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray); } _rowNo++; } return; } ExportRecursion(node.Left, sheet); ExportRecursion(node.Right, sheet); }
/// <summary> /// This method is used to export the data to excel /// </summary> /// <param name="dataTable"></param> /// <param name="filePath"></param> /// <param name="sheetName"></param> /// <returns></returns> public static bool ExportToExcel(System.Data.DataTable dataTable, string filePath, string sheetName) { try { using (OfficeOpenXml.ExcelPackage xlPackage = new OfficeOpenXml.ExcelPackage()) { if (File.Exists(filePath)) { using (var stream = File.OpenRead(filePath)) { xlPackage.Load(stream); } //delete existing worksheet if worksheet with same name already exist OfficeOpenXml.ExcelWorksheet excelWorksheet = xlPackage.Workbook.Worksheets.FirstOrDefault(x => x.Name == sheetName); if (excelWorksheet != null) { xlPackage.Workbook.Worksheets.Delete(sheetName); } excelWorksheet = xlPackage.Workbook.Worksheets.Add(sheetName); excelWorksheet.Cells["A1"].LoadFromDataTable(dataTable, true); File.WriteAllBytes(filePath, xlPackage.GetAsByteArray()); } else { var ws = xlPackage.Workbook.Worksheets.Add(sheetName); ws.Cells["A1"].LoadFromDataTable(dataTable, true); xlPackage.SaveAs(new FileInfo(filePath)); } } return(true); } catch (Exception ex) { Reporter.ToLog(eLogLevel.ERROR, "Error Occurred while exporting to excel", ex); return(false); } }
private void populateMentors(string path, RoleType rType) { OfficeOpenXml.ExcelWorksheet ws = readExcel(path); for (int rowNum = 2; rowNum <= ws.Dimension.End.Row; rowNum++)//foreach mentor { var wsRow = ws.Cells[rowNum, ws.Dimension.End.Column]; Mentor objMentor = new Mentor(); objMentor.Name = wsRow["B" + rowNum].Text.Trim(); objMentor.Dept = setDept(wsRow["G" + rowNum].Text.Trim()); objMentor.UscId = wsRow["D" + rowNum].Text.Trim(); objMentor.Contact = wsRow["E" + rowNum].Text.Trim(); if (!_dicMentorDepartment.ContainsKey(objMentor.Dept)) { List <Mentor> lstMentors = new List <Mentor>(); lstMentors.Add(objMentor); _dicMentorDepartment.Add(objMentor.Dept, lstMentors); } else { List <Mentor> lstMentors = _dicMentorDepartment[objMentor.Dept]; lstMentors.Add(objMentor); } } }
public static void SaveExcel(IEnumerable items, OfficeOpenXml.ExcelWorksheet sheet, Type elementType, string schemeAndAuthorityBase) { //var table = sheet.Tables.Single(); var headerRow = sheet.Row(1); var fields = GetFields(elementType); var nextRow = 2; var col = 1; foreach (var field in fields) { var cell = sheet.Cells[1, col]; cell.Value = field.Name; col++; var ft = field.Type; var ti = ft.GetTypeInfo(); if (ti.IsGenericType && ft.GetGenericTypeDefinition() == typeof(Nullable <>)) { ft = ft.GetGenericArguments()[0]; } sheet.Column(col - 1).Width = ti.IsPrimitive || NumericTypes.Contains(ft) || ti.IsEnum || ft == typeof(DateTime) || ft == typeof(DateTimeOffset) ? 10 : 25; } foreach (var item in items) { var row = nextRow; nextRow++; col = 1; foreach (var prop in fields) { var cell = sheet.Cells[row, col]; var val = prop.Get(item); #if !STANDALONE var es = val as EntitySet; var ent = val as Entity; if (es != null) { var url = (schemeAndAuthorityBase + Utils.GetHateosPath(es)).AsUri(); cell.Hyperlink = url; cell.Value = url; } else if (ent != null) { var url = (schemeAndAuthorityBase + Utils.GetHateosPath(ent)).AsUri(); cell.Hyperlink = url; cell.Value = ent.ToStringOrIdOrDefault(ent.EntityType.Name); } else #endif if (val is Uri) { var url = (Uri)val; cell.Hyperlink = url; cell.Value = url.AbsoluteUri; //cell.Style.sy } #if !STANDALONE else if (val is Money) { var money = (Money)val; cell.Value = money.CurrencyValue; if (money.CurrencyType != Currency.None) { cell.Style.Numberformat.Format = Money.GetExcelFormat(money.CurrencyType); } } #endif else { cell.Value = val; if (val is DateTime || val is DateTimeOffset) { cell.Style.Numberformat.Format = "yyyy-mm-dd HH hh:mm"; } } col++; } } var t = sheet.Tables.Add(new OfficeOpenXml.ExcelAddressBase(1, 1, nextRow - 1, col - 1), sheet.Name + "-Items"); t.TableStyle = OfficeOpenXml.Table.TableStyles.None; }
public static IEnumerable <T> SaveExcel <T>(this IEnumerable <T> items, OfficeOpenXml.ExcelWorksheet sheet) { SaveExcel(items, sheet, typeof(T), null); return(items); }
public override void Input(OfficeOpenXml.ExcelWorksheet worksheet, ExcelInputDTO inputInfo) { worksheet.Cells["B2"].Value = inputInfo.USCode; worksheet.Cells["B3"].Value = inputInfo.StoreNameEN; worksheet.Cells["B4"].Value = inputInfo.StoreNameCN; worksheet.Cells["B5"].Value = worksheet.Cells["B12"].Value = inputInfo.MinimarketPriority; worksheet.Cells["B6"].Value = inputInfo.TADesrability; worksheet.Cells["B7"].Value = inputInfo.MMTADescription; worksheet.Cells["B8"].Value = inputInfo.MajorGenerators; worksheet.Cells["B9"].Value = worksheet.Cells["B16"].Value = inputInfo.SitePortfolioType; worksheet.Cells["B10"].Value = inputInfo.SiteRerating; worksheet.Cells["B11"].Value = worksheet.Cells["B18"].Value = inputInfo.SiteDescription; worksheet.Cells["B13"].Value = inputInfo.TADesrabilityNew; worksheet.Cells["B14"].Value = inputInfo.MMTADescriptionNew; worksheet.Cells["B15"].Value = inputInfo.MajorGeneratorsNew; worksheet.Cells["B17"].Value = inputInfo.SiteRERatingFuture; worksheet.Cells["B19"].Value = inputInfo.LeasedArea; worksheet.Cells["B20"].Value = inputInfo.OperationSize; worksheet.Cells["B21"].Value = inputInfo.Floors; worksheet.Cells["B22"].Value = inputInfo.Seats; worksheet.Cells["B23"].Value = inputInfo.BEType; worksheet.Cells["B24"].Value = inputInfo.LeaseTenureAndTerm; worksheet.Cells["B25"].Value = inputInfo.FreeRentalPeriod; worksheet.Cells["B26"].Value = inputInfo.RentStructure; worksheet.Cells["B27"].Value = inputInfo.MFStructure; worksheet.Cells["B28"].Value = inputInfo.LeasedAreaNew; worksheet.Cells["B29"].Value = inputInfo.OperationSizeNew; worksheet.Cells["B30"].Value = inputInfo.FloorsNew; worksheet.Cells["B31"].Value = inputInfo.SeatsNew; worksheet.Cells["B32"].Value = inputInfo.BETypeNew; worksheet.Cells["B33"].Value = inputInfo.LeaseTenureAndTermNew; worksheet.Cells["B34"].Value = inputInfo.FreeRentalPeriodNew; worksheet.Cells["B35"].Value = inputInfo.RentStructureNew; worksheet.Cells["B36"].Value = inputInfo.MFStructureNew; worksheet.Cells["B37"].Value = inputInfo.DRMFLastTY; worksheet.Cells["B38"].Value = inputInfo.DRMFLastTYSales; worksheet.Cells["B39"].Value = inputInfo.DRMF1stTY; worksheet.Cells["B40"].Value = inputInfo.DRMF1stTYSales; worksheet.Cells["B41"].Value = inputInfo.SRMFLastTY; worksheet.Cells["B42"].Value = inputInfo.SRMFLastTYSales; worksheet.Cells["B43"].Value = inputInfo.SRMF1stTY; worksheet.Cells["B44"].Value = inputInfo.SRMF1stTYSales; worksheet.Cells["B45"].Value = inputInfo.FairMarketRentAmount; worksheet.Cells["B46"].Value = inputInfo.FairMarketRentAgent; worksheet.Cells["B47"].Value = inputInfo.DR1stTYAmount; worksheet.Cells["B48"].Value = inputInfo.AnnualSOILastTY; worksheet.Cells["B49"].Value = inputInfo.AnnualSOIAvg; worksheet.Cells["B50"].Value = inputInfo.CashROILastTY; worksheet.Cells["B51"].Value = inputInfo.CashROIAvg; worksheet.Cells["B52"].Value = inputInfo.LastRemodeling; worksheet.Cells["B53"].Value = inputInfo.OriginalInvestment; worksheet.Cells["B54"].Value = inputInfo.NBV; worksheet.Cells["B55"].Value = inputInfo.AdditionalInvestmentCost; worksheet.Cells["B56"].Value = inputInfo.ExclusivityClause; worksheet.Cells["B57"].Value = inputInfo.McDsEarlyTerminationRight; worksheet.Cells["B58"].Value = inputInfo.McDsEPRight; worksheet.Cells["B59"].Value = inputInfo.LandlordEntity; worksheet.Cells["B60"].Value = inputInfo.SpecialClauses; worksheet.Cells["B61"].Value = inputInfo.OtherIssues; worksheet.Cells["B62"].Value = inputInfo.ExclusivityClauseNew; worksheet.Cells["B63"].Value = inputInfo.McDsEarlyTerminationRightNew; worksheet.Cells["B64"].Value = inputInfo.McDsEPRightNew; worksheet.Cells["B65"].Value = inputInfo.LandlordEntityNew; worksheet.Cells["B66"].Value = inputInfo.SpecialClausesNew; worksheet.Cells["B67"].Value = inputInfo.OtherIssuesNew; worksheet.Cells["B68"].Value = inputInfo.DecisionLogic; worksheet.Cells["B69"].Value = inputInfo.SpecialApprovalRequired; }
public override void Parse(OfficeOpenXml.ExcelWorksheet worksheet, int currRow) { }
private void Import2() { if (!string.IsNullOrEmpty(_pathImport)) { DataSet ds = null; try { if (Path.GetExtension(_pathImport).Contains("xlsx")) // 2010 { FileInfo newFile = new FileInfo(_pathImport); using (OfficeOpenXml.ExcelPackage pck = new OfficeOpenXml.ExcelPackage(newFile)) { OfficeOpenXml.ExcelWorksheet ws = pck.Workbook.Worksheets[1]; string sheetName = ws.Name; ds = _db.ImportExcelToDS(_pathImport, sheetName); } } else if (Path.GetExtension(_pathImport).Contains("csv")) { ds = _db.ImportCSVToDS(_pathImport); } else { ds = _db.ImportExcelToDS(_pathImport); } if (ds != null) { bool bColumnOk = true; for (int j = 0; j < ds.Tables[0].Columns.Count; j++) { string colName = ds.Tables[0].Columns[j].ColumnName; if (colName.Contains("#")) { ds.Tables[0].Columns[j].ColumnName = colName.Replace("#", "."); } if (!System.Text.RegularExpressions.Regex.IsMatch(ds.Tables[0].Columns[j].ColumnName, "^[\\sa-zA-Z0-9_\\.\\-%&/'()]*$")) { bColumnOk = false; } } if (!bColumnOk) { MessageBox.Show(ResStrings.DbColumnForm_Importerror, ResStrings.DbColumnForm_toolStripButtonSubmit_Click_Updtate_Column_Info, MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } _dt.Rows.Clear(); int nIndex = 0; foreach (DataColumn colName in ds.Tables[0].Columns) { //remove sheet _dt.Rows.Add(nIndex.ToString(), colName, colName, "STRING", "NONE", 1, 0); nIndex++; } toolStripButtonSubmit.Enabled = true; _bComeFromImport = true; } } catch (IOException) { MessageBox.Show(ResStrings.DbColumnForm_Import_fileopen, ResStrings.strInfo, MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { ErrorMessage.ExceptionMessageBox.Show(ex); } } }
public ActionResult ExportToExcel(string Id) { if (string.IsNullOrWhiteSpace(Id)) { return(null); } T_PurchaseOrder ObjItem = new T_PurchaseOrder() { Id = Guid.Parse(Id) }; var vObjList = _blPurchaseOrder.GetPurchaseOrderDetailList(ObjItem); // ExcelPackageF dll Need OfficeOpenXml.ExcelPackage Ep = new OfficeOpenXml.ExcelPackage(); OfficeOpenXml.ExcelWorksheet workSheet = Ep.Workbook.Worksheets.Add("Report"); string sEndColumn = "M1"; workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Font.Bold = true; workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; workSheet.Cells[string.Format("A1:{0}", sEndColumn)].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightSeaGreen); var allCells = workSheet.Cells[string.Format("A1:{0}", sEndColumn)]; var cellFont = allCells.Style.Font; workSheet.Cells["A1"].Value = "Slno"; //sheet["A3:A4"].Merge(); workSheet.Cells["B1"].Value = "Item"; workSheet.Cells["C1"].Value = "Brand"; workSheet.Cells["D1"].Value = "Guage"; workSheet.Cells["E1"].Value = "Quantity"; workSheet.Cells["F1"].Value = "UOM"; workSheet.Cells["G1"].Value = "Buying Rate"; workSheet.Cells["H1"].Value = "Buying Price"; workSheet.Cells["I1"].Value = "Selling Rate"; workSheet.Cells["J1"].Value = "Selling Price"; workSheet.Cells["K1"].Value = "Profit"; workSheet.Cells["L1"].Value = "Remark"; workSheet.Cells["M1"].Value = "Created Dated"; string dateformat = "dd-MM-yyyy"; int row = 2; foreach (var item in vObjList) { //workSheet.Cells[string.Format("A{0}", row)].Value = item.EventDate; //workSheet.Cells[string.Format("A{0}", row)].Style.Numberformat.Format = dateformat; workSheet.Cells[string.Format("A{0}", row)].Value = item.SlNo; workSheet.Cells[string.Format("B{0}", row)].Value = item.ItemsName; workSheet.Cells[string.Format("C{0}", row)].Value = item.BrandName; workSheet.Cells[string.Format("D{0}", row)].Value = item.GuageName; workSheet.Cells[string.Format("E{0}", row)].Value = item.Quantity; workSheet.Cells[string.Format("F{0}", row)].Value = item.UomName; workSheet.Cells[string.Format("G{0}", row)].Value = item.BuyingRate; workSheet.Cells[string.Format("H{0}", row)].Value = item.BuyingPrice; workSheet.Cells[string.Format("I{0}", row)].Value = item.SellingRate; workSheet.Cells[string.Format("J{0}", row)].Value = item.SellingPrice; workSheet.Cells[string.Format("K{0}", row)].Value = item.DifferenceAmount; workSheet.Cells[string.Format("L{0}", row)].Value = item.Remark; workSheet.Cells[string.Format("M{0}", row)].Value = item.CreatedDate; workSheet.Cells[string.Format("M{0}", row)].Style.Numberformat.Format = dateformat; row++; } workSheet.Cells["A:AZ"].AutoFitColumns(); Response.Clear(); Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment: filename=" + "Expense.csv" + DateTime.Now.ToString("s")); Response.BinaryWrite(Ep.GetAsByteArray()); Response.End(); return(View()); }
public void Update(HttpPostedFileBase file, string username, string metadatafield, bool deleteData) { excelPackage = new OfficeOpenXml.ExcelPackage(); excelPackage.Load(file.InputStream); workSheet = excelPackage.Workbook.Worksheets[1]; if (metadatafield == "AccessConstraints") UpdateRestriction(username); else if (metadatafield == "KeywordsNationalTheme") UpdateTheme(username); else if (metadatafield == "KeywordsNationalInitiative") UpdateKeywordsNationalInitiative(username, deleteData); else if (metadatafield == "KeywordsInspire") UpdateKeywordsInspire(username, deleteData); else if (metadatafield == "DistributionFormats") UpdateDistributionFormats(username, deleteData); else if (metadatafield == "ReferenceSystems") UpdateReferenceSystems(username, deleteData); excelPackage.Dispose(); }
/// <summary> /// 创建表结构sheet /// </summary> /// <param name="epck"></param> /// <param name="sheetName"></param> /// <param name="tables"></param> private static void CreateTableSheet(OfficeOpenXml.ExcelPackage epck, string sheetName, List <TableDto> tables) { OfficeOpenXml.ExcelWorksheet tbWorksheet = epck.Workbook.Worksheets.Add(sheetName); int rowNum = 1, fromRow = 0, count = 0; // 行号计数器 // TODO 循环数据库表名 foreach (var table in tables) { // TODO 数据库名称 tbWorksheet.Cells[rowNum, 1, rowNum, 10].Merge = true; tbWorksheet.Cells[rowNum, 1].Value = table.TableName + " " + (!string.IsNullOrWhiteSpace(table.Comment) ? table.Comment : ""); tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Font.Bold = true; tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Font.Size = 16; tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // TODO 注意:保存起始行号 fromRow = rowNum; rowNum++; // 行号+1 // tbWorksheet.Cells[int FromRow, int FromCol, int ToRow, int ToCol] // TODO 列标题字体为粗体 tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Font.Bold = true; // TODO 添加列标题 tbWorksheet.Cells[rowNum, 1].Value = "序号"; tbWorksheet.Cells[rowNum, 2].Value = "列名"; tbWorksheet.Cells[rowNum, 3].Value = "数据类型"; tbWorksheet.Cells[rowNum, 4].Value = "长度"; tbWorksheet.Cells[rowNum, 5].Value = "小数位"; tbWorksheet.Cells[rowNum, 6].Value = "主键"; tbWorksheet.Cells[rowNum, 7].Value = "自增"; tbWorksheet.Cells[rowNum, 8].Value = "允许空"; tbWorksheet.Cells[rowNum, 9].Value = "默认值"; tbWorksheet.Cells[rowNum, 10].Value = "列说明"; rowNum++; // 行号+1 // TODO 添加数据行,遍历数据库表字段 foreach (var column in table.Columns) { tbWorksheet.Cells[rowNum, 1].Value = column.ColumnOrder; tbWorksheet.Cells[rowNum, 2].Value = column.ColumnName; tbWorksheet.Cells[rowNum, 3].Value = column.ColumnTypeName; tbWorksheet.Cells[rowNum, 4].Value = column.Length; tbWorksheet.Cells[rowNum, 5].Value = column.Scale; tbWorksheet.Cells[rowNum, 6].Value = column.IsPK; tbWorksheet.Cells[rowNum, 7].Value = column.IsIdentity; tbWorksheet.Cells[rowNum, 8].Value = column.CanNull; tbWorksheet.Cells[rowNum, 9].Value = column.DefaultVal; tbWorksheet.Cells[rowNum, 10].Value = column.Comment; rowNum++; // 行号+1 } // TODO 水平居中 tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; // TODO 垂直居中 tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; // TODO 上下左右边框线 tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; tbWorksheet.Cells[fromRow, 1, rowNum - 1, 10].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; // TODO 处理空白行,分割用 if (count < tables.Count - 1) { //tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; //tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; tbWorksheet.Cells[rowNum, 1, rowNum, 10].Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; tbWorksheet.Cells[rowNum, 1, rowNum, 10].Merge = true; tbWorksheet.Cells[rowNum, 1, rowNum, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid; tbWorksheet.Cells[rowNum, 1, rowNum, 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.DodgerBlue); } rowNum++; // 行号+1 count++; // 计数器+1 } // TODO 设置表格样式 tbWorksheet.Cells.Style.WrapText = true; // 自动换行 tbWorksheet.Cells.Style.ShrinkToFit = true; // 单元格自动适应大小 }
public static void FontDefaults(ExcelWorksheet sheet) { sheet.Cells.Style.Font.Name = "Arial"; sheet.Cells.Style.Font.Size = 10; }
public static void FontDefaults(ExcelWorksheet sheet) { }