internal static void RearrangeWorksheets(ExcelWorksheets worksheets, int sourcePositionId, int targetPositionId, bool before) { if (sourcePositionId == targetPositionId) { return; } lock (worksheets) { var sourceSheet = worksheets[sourcePositionId]; var targetSheet = worksheets[targetPositionId]; var index = targetSheet._package._worksheetAdd; worksheets._worksheets.Move(sourcePositionId - index, targetPositionId - index, before); var from = Math.Min(sourcePositionId, targetPositionId); var to = Math.Max(sourcePositionId, targetPositionId); for (int i = from; i <= to; i++) { worksheets[i].PositionId = i; } MoveTargetXml(worksheets, sourceSheet, targetSheet, before); } }
public int readExcel(String fileName) { int workSheets = 0; try { ExcelPackage excel = new ExcelPackage(new System.IO.FileInfo(fileName)); ExcelWorksheets wrkSheets = excel.Workbook.Worksheets; foreach (ExcelWorksheet wrkSheet in wrkSheets) { for (int i = wrkSheet.Dimension.Start.Row; i <= wrkSheet.Dimension.End.Row; i++) { for (int j = wrkSheet.Dimension.Start.Column; j <= wrkSheet.Dimension.End.Column; j++) { object cellValue = wrkSheet.Cells[j, i].Value; Console.Write(" " + cellValue.ToString() + " "); } Console.WriteLine(""); } } } catch (Exception ex) { } return(workSheets); }
/// <summary> /// 读取Excel /// </summary> /// <param name="FileName">文件名</param> /// <param name="WhichTable">第几张表</param> /// <param name="Whichline">第几行</param> /// <param name="HowManyColumns">第几列</param> public static string ReadExcel(string FileName, int WhichTable, int Whichline, int HowManyColumns) { string path = Application.streamingAssetsPath + "/Excel/" + FileName + ".xlsx"; using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read)) { using (ExcelPackage excel = new ExcelPackage(fs)) { ExcelWorksheets workSheets = excel.Workbook.Worksheets; if (!(WhichTable >= 1 && WhichTable <= workSheets.Count)) { Debug.LogErrorFormat("工作表不存在 FileName = {0}; WhichTable = {1}; Whichline = {2}; HowManyColumns = {3}", FileName, WhichTable, Whichline, HowManyColumns); } ExcelWorksheet workSheet = workSheets[WhichTable]; int rowCount = workSheet.Dimension.End.Row; int colCount = workSheet.Dimension.End.Column; if (!(HowManyColumns >= 1 && HowManyColumns <= colCount)) { Debug.LogErrorFormat("列越界 FileName = {0}; WhichTable = {1}; Whichline = {2}; HowManyColumns = {3}", FileName, WhichTable, Whichline, HowManyColumns); } if (!(Whichline >= 1 && Whichline <= rowCount)) { Debug.LogErrorFormat("行越界 FileName = {0}; WhichTable = {1}; Whichline = {2}; HowManyColumns = {3}", FileName, WhichTable, Whichline, HowManyColumns); } var text = workSheet.Cells[Whichline, HowManyColumns].Text ?? ""; return(text); } } }
private void FormatAddress(FileInfo f) { using (ExcelPackage ep = new ExcelPackage(f)) { ExcelWorkbook ewb = ep.Workbook; ExcelWorksheets ews = ewb.Worksheets; foreach (var _ews in ews) { if (_ews.Name == "Address" || _ews.Name == "Mailing Address") { SetColumnHeader(_ews); char col = 'B'; for (int i = 0; i < _ews.Dimension.Rows - 1; i++) { string fullAddress = _ews.Cells[$"{ col }{ i + 2 }"].Text; var splitAddress = SplitAddress(fullAddress); foreach (var a in splitAddress) { _ews.Cells[$"{ col }{ i + 2 }"].Value = a; col++; } col = 'B'; } ep.Save(); } } } }
public async Task <int> UploadFileAsync(string fileName, string pathTmp) { try { FileInfo file = new FileInfo(pathTmp); ExcelPackage excel = new ExcelPackage(file); ExcelWorksheets workSheets = excel.Workbook.Worksheets; var fileId = Guid.NewGuid().ToString(); _unit.RepFile.Create(new Data.Dto.File { Id = fileId, UploadDateTime = DateTime.UtcNow, Name = fileName, NameTmp = pathTmp }); foreach (ExcelWorksheet workSheet in workSheets) { var workSheetId = Guid.NewGuid().ToString(); _unit.RepWorkSheet.Create(new Data.Dto.WorkSheet { Id = workSheetId, FileId = fileId, Number = workSheet.Index, Name = workSheet.Name }); for (var rowNumber = 1; rowNumber <= workSheet.Dimension.End.Row; rowNumber++) { var rowId = Guid.NewGuid().ToString(); _unit.RepRow.Create(new Data.Dto.Row { Id = rowId, WorkSheetId = workSheetId, Number = rowNumber, }); for (int cellNumber = 1; cellNumber <= workSheet.Dimension.End.Column; cellNumber++) { string cellValue = workSheet.Cells[rowNumber, cellNumber].Value.ToString(); _unit.RepCol.Create(new Data.Dto.Col { Id = Guid.NewGuid().ToString(), RowId = rowId, Name = $"col{cellNumber}", Value = cellValue }); } } } return(await _unit.CommitAsync()); } catch (Exception ex) { throw new Exception("Не удалось сохранить файл В БД", ex); } }
public JsonResult UploadFile() { DtoRisk dto = new DtoRisk(); if (Request.Files.Count > 0) { try { object[,] obj = null; int noOfCol = 0; int noOfRow = 0; HttpFileCollectionBase file = Request.Files; if ((file != null) && (file.Count > 0)) { byte[] fileBytes = new byte[Request.ContentLength]; int data = Request.InputStream.Read(fileBytes, 0, Convert.ToInt32(Request.ContentLength)); using (ExcelPackage package = new ExcelPackage(Request.InputStream)) { ExcelWorksheets worksheets = package.Workbook.Worksheets; ExcelWorksheet workSheet = worksheets.Single(x => x.Name.Equals(dto.WorksheetName)); noOfCol = workSheet.Dimension.End.Column; noOfRow = workSheet.Dimension.End.Row; obj = new object[noOfRow, noOfCol]; obj = (object[, ])workSheet.Cells.Value; } } return(Json(new { data = obj, row = noOfRow, col = noOfCol }, JsonRequestBehavior.AllowGet)); } catch (Exception ex) { } } return(Json("", JsonRequestBehavior.AllowGet)); }
static void LoadExcel() { string path = Application.dataPath + "/Excel/test.xlsx"; //读取Excel文件 using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { using (ExcelPackage excel = new ExcelPackage(fs)) { ExcelWorksheets workSheets = excel.Workbook.Worksheets; //遍历所有worksheets for (int i = 1; i <= workSheets.Count; i++) { ExcelWorksheet workSheet = workSheets [i]; int colCount = workSheet.Dimension.End.Column; //获取当前sheet名 Debug.LogFormat("Sheet {0}", workSheet.Name); for (int row = 1, count = workSheet.Dimension.End.Row; row <= count; row++) { for (int col = 1; col <= colCount; col++) { //读取每个格子中的数据 var text = workSheet.Cells [row, col].Text ?? ""; Debug.LogFormat("下标:{0},{1} 内容:{2}", row, col, text); } } } } } }
public void LoadFile(string filePath) { FileInfo file = new FileInfo(filePath); ExcelPackage pkg = new ExcelPackage(file); worksheets = pkg.Workbook.Worksheets; }
/// <summary> /// 新しい空のワークシートを追加します。 /// </summary> /// <param name="worksheets">ワークシートコレクション</param> /// <param name="sheetName">ワークシートの名前</param> /// <param name="fontName">フォント名</param> /// <returns>ワークシート</returns> public static ExcelWorksheet Add(this ExcelWorksheets worksheets, string sheetName, string fontName) { var worksheet = worksheets.Add(sheetName); worksheet.Cells.Style.Font.Name = fontName; return(worksheet); }
public List <Email> ReadExcelData(string FilePath) { Email _email; List <Email> _myemaillist = new List <Email>(); FileInfo existingFile = new FileInfo(FilePath); using (ExcelPackage package = new ExcelPackage(existingFile)) { //get the first worksheet in the workbook ExcelWorksheets worksheet = package.Workbook.Worksheets; foreach (ExcelWorksheet sheet in worksheet) { int colCount = sheet.Dimension.End.Column; //get Column Count int rowCount = sheet.Dimension.End.Row; //get row count for (int row = 2; row <= rowCount; row++) { if (sheet.Cells[row, 1].Value != null) { if (sheet.Cells[row, 1].Value.ToString().Trim().IndexOf("@") > 0) { _email = new Email(); _email.EmailAddress = sheet.Cells[row, 1].Value.ToString().Trim(); _email.Id = _myemaillist.Count + 1; _email.VerificationStatusId = Enum.VerificationStatus.Pending; _email.Verification = Enum.VerificationStatus.Pending.ToString(); _myemaillist.Add(_email); } } } } } return(_myemaillist); }
private DataTable ReadExcel(string path, bool hasHeader = true) { ExcelPackage package = new ExcelPackage(new FileInfo(path)); ExcelWorkbook wb = package.Workbook; ExcelWorksheets wsList = wb.Worksheets; ExcelWorksheet ws = wsList[1]; DataTable tbl = new DataTable(); foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column]) { tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column)); } var startRow = hasHeader ? 2 : 1; for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++) { var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column]; DataRow row = tbl.Rows.Add(); foreach (var cell in wsRow) { row[cell.Start.Column - 1] = cell.Text; } } return(tbl); }
public ExcelSheet Parse(String path) { // path to your excel file FileInfo fileInfo = new FileInfo(path); ExcelPackage package = new ExcelPackage(fileInfo); ExcelWorksheets worksheets = package.Workbook.Worksheets; ExcelSheet excelSheet = new ExcelSheet(); for (int i = 0; i < worksheets.Count; i++) { string sheetName = worksheets[i].Name; PropertyInfo prop = excelSheet.GetType().GetProperty(sheetName); Type type = prop.PropertyType; if (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(List <>)) { var newcollection = worksheets[i].ConvertSheetToObjects(type); prop.SetValue(excelSheet, newcollection, null); } else { var obj = worksheets[i].ConvertSheetToObject(type); prop.SetValue(excelSheet, obj, null); } } return(excelSheet); }
private static ExcelWorksheet getSheet(ExcelPackage package, String sheetName) { Console.WriteLine("Enter getSheet(): package={0}, sheet={1}\n", package, sheetName); if (package == null || sheetName == null) { Console.WriteLine("Input Error!\n"); return(null); } ExcelWorksheets sheets = package.Workbook.Worksheets; ExcelWorksheet wantedSheet = null; foreach (ExcelWorksheet sheet in sheets) { if (sheet.Name.Equals(sheetName)) { Console.WriteLine("{0} is found!\n", sheetName); wantedSheet = sheet; } } if (wantedSheet == null) { Console.WriteLine("NO SHEET FOUND !!!\n"); } Console.WriteLine("Exit getSheet()\n"); return(wantedSheet); }
public virtual ExcelWorksheet AddAsExcelSheet(ExcelWorksheets excelWorksheets, TableStyles tableStyle, string headerStyle) { var spreadSheet = excelWorksheets.Add(SpreadsheetName); var dataTable = ConvertToDataTables().First(); foreach (var column in dataTable.Columns) { var columnName = column.ToString(); var friendlyName = column.ToString() .ToFriendlyString(" ", a => a == '_', b => b.CapitalizeFirstLetter(), true); dataTable.Columns[column.ToString()].ColumnName = friendlyName; } spreadSheet.Cells[1, 1].LoadFromDataTable(dataTable, true, tableStyle); spreadSheet.Cells.AutoFitColumns(); //No rows of close approach data were added and the spreadsheet is empty, no need to include it. if (!spreadSheet.Cells.Any()) { excelWorksheets.Delete(spreadSheet); return(null); } return(spreadSheet); }
void ReadExcel(string path, List <ComboBoxItem> list) { FileInfo file = new FileInfo(path); //检测文件是否正在被使用 try { using (FileStream fs = file.Open(FileMode.Open, FileAccess.Read)) { fs.Close(); } } catch (IOException) { string errorText = string.Format("数据表{0}正在被使用,请将其关闭后再打开该软件。", file.Name); MessageBox.Show(errorText, "Error花生", MessageBoxButtons.OK, MessageBoxIcon.Error); Environment.Exit(1); } ExcelPackage package = new ExcelPackage(file); ExcelWorksheets sheets = package.Workbook.Worksheets; foreach (var sheet in sheets) { int rows = sheet.Dimension.Rows; list.Add(new ComboBoxItem("", string.Format("-------- {0} --------", sheet.Name), false)); for (int row = 2; row < rows; row++) { if (sheet.Cells[rows, 1, rows, 2].All(c => c.Value != null)) { list.Add(new ComboBoxItem(sheet.GetValue(row, 2).ToString(), sheet.GetValue(row, 1).ToString(), true)); } } } }
private async Task AppendSheetForEachSchool(ExcelWorksheets worksheets, School school) { var worksheet = worksheets.Add(school.Name); worksheet.AppendHeader(typeof(ActivityExportHeader), 1); IEnumerable <Entities.Class> efClasses = await this._unitOfWork.Classes.GetWithSchoolId(school.Id); List <Guid> classIds = efClasses.Select(c => c.Id).ToList(); IEnumerable <Entities.Track> efTracks = await this._unitOfWork.Tracks.GetFullInformationByIds(classIds); IEnumerable <Track> tracks = _mapper.Map <IEnumerable <Entities.Track>, IEnumerable <Track> >(efTracks); int rowIndex = 1; foreach (var track in tracks) { rowIndex++; worksheet.Cells[rowIndex, ActivityExportHeader.ChildName.ColumnIndex].Value = track.ChildName; worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckIn.ColumnIndex].Value = track.TimeCheckIn.ToLocalTime(); worksheet.Cells[rowIndex, ActivityExportHeader.TimeCheckOut.ColumnIndex].Value = track.TimeCheckOut.ToLocalTime(); worksheet.Cells[rowIndex, ActivityExportHeader.ClassName.ColumnIndex].Value = track.Class.Name; worksheet.Cells[rowIndex, ActivityExportHeader.TeacherName.ColumnIndex].Value = track.Class.Teacher.Name; } worksheet.AutoFixColumns(typeof(ActivityExportHeader)); }
private void ExcelToJson(string excelName) { if (!string.IsNullOrEmpty(excelName)) { string filepath = Application.dataPath + "/Resources/Excel/" + excelName; string headPath = $"{Application.dataPath}/{txt_JsonSavePath}"; using (FileStream fs = new FileStream(filepath, FileMode.Open, FileAccess.Read)) { using (ExcelPackage ep = new ExcelPackage(fs)) { //获得所有工作表 ExcelWorksheets workSheets = ep.Workbook.Worksheets; List <System.Object> lst = new List <object>(); //遍历所有工作表 for (int i = 1; i <= workSheets.Count; i++) { //当前工作表 ExcelWorksheet sheet = workSheets[i]; //初始化集合 lst.Clear(); int columnCount = sheet.Dimension.End.Column; int rowCount = sheet.Dimension.End.Row; //根据实体类创建对象集合序列化到json中 for (int z = 4; z <= rowCount; z++) { Assembly ab = Assembly.Load("Assembly-CSharp"); //要注意对面在那个程序集里面dll Type type = ab.GetType($"Table.{sheet.Name}"); if (type == null) { Debug.LogError("你还没有创建对应的实体类!"); return; } if (!Directory.Exists(headPath)) { Directory.CreateDirectory(headPath); } object o = ab.CreateInstance(type.ToString()); for (int j = 1; j <= columnCount; j++) { FieldInfo fieldInfo = type.GetField(sheet.Cells[i, j].Text); //先获得字段信息,方便获得字段类型 object value = Convert.ChangeType(sheet.Cells[z, j].Text, fieldInfo.FieldType); type.GetField(sheet.Cells[1, j].Text).SetValue(o, value); } lst.Add(o); } //写入json文件 string jsonPath = $"{headPath}/{sheet.Name}.json"; if (!File.Exists(jsonPath)) { File.Create(jsonPath).Dispose(); } File.WriteAllText(jsonPath, JsonConvert.SerializeObject(lst)); } } } AssetDatabase.Refresh(); } }
public ExcelWorksheet CreateAWorksheetFromTemplate(ExcelWorkbook current, string name) { ExcelWorksheets _sheets = LoadTemplates(); current.Worksheets.Add(name, _sheets[templateWorksheetName]); _sheets.Dispose(); return(current.Worksheets[name]); }
public static ExcelWorksheet ReadExcelSheet(string excelFloder, string sheetName) { if (mExcelSheetCaches == null) { mExcelSheetCaches = new Dictionary <string, ExcelWorksheet>(); } ExcelWorksheet sheet; if (!mExcelSheetCaches.TryGetValue(sheetName, out sheet)) { Dictionary <string, string> nameToPath = new Dictionary <string, string>(); var configSheetTPaths = File.ReadAllText(Path.Combine(excelFloder, NAME_TO_PATH)); var sheetToPaths = configSheetTPaths.Split(','); foreach (var sheetToPath in sheetToPaths) { if (string.IsNullOrEmpty(sheetToPath)) { continue; } var sheetAndPath = sheetToPath.Split('='); if (sheetAndPath != null && sheetAndPath.Length == 2) { nameToPath.Add(sheetAndPath[0], sheetAndPath[1]); } } string excelName = string.Empty; if (nameToPath.TryGetValue(sheetName, out excelName)) { var dirInfo = Directory.CreateDirectory(excelFloder); FileInfo[] fileInfos = dirInfo.GetFiles(); for (int i = 0; i < fileInfos.Length; i++) { FileInfo fileInfo = fileInfos[i]; if (fileInfo.Name == excelName) { ExcelPackage ep = new ExcelPackage(fileInfo); ExcelWorksheets worksheets = ep.Workbook.Worksheets; for (int j = 1; j <= worksheets.Count; j++) { ExcelWorksheet newSheet = worksheets[j]; mExcelSheetCaches.Add(newSheet.Name, newSheet); if (newSheet.Name == sheetName) { sheet = newSheet; } } } } } else { UnityEngine.Debug.LogErrorFormat("新增了配置表【{0}】, 请用菜单工具生成一下映射。", sheetName); } } return(sheet); }
void Start() { instance = this; cards = new List <Card>(); teamA = new Team(); teamB = new Team(); FileInfo newFile = new FileInfo(fileName); if (newFile.Exists) { ExcelPackage excel = new ExcelPackage(newFile); ExcelWorksheets sheets = excel.Workbook.Worksheets; LoadDefineValue(sheets["define"]); InitAllCards(sheets["card"]); Print(System.DateTime.Now.TimeOfDay.ToString(), 1); for (int tier = 1; tier < 30 && cards.Count > 0; tier++) { foreach (Card c in cards) { c.chart.per = 0f; c.chart.all = 0; c.chart.win = 0; } for (int i = 0; i < 10000; i++) { InitTeam(); Team teamwin = Battle(teamA, teamB); AddData(teamwin); } foreach (Card c in cards) { c.chart.per = ((float)c.chart.win) / c.chart.all; } List <Card> sortedList = cards.OrderByDescending(c => (1f - c.chart.per)).ToList(); cards = sortedList; int nRemove = Math.Min(4, cards.Count); for (int i = 0; i < nRemove; i++) { if (tier > 16) { sheets["card"].Cells[cards[0].id + 2, 19].Value = string.Format("Tier " + (tier - 16)); } else { sheets["card"].Cells[cards[0].id + 2, 19].Value = ""; } sheets["card"].Cells[cards[0].id + 2, 16].Value = cards[0].chart.win; sheets["card"].Cells[cards[0].id + 2, 17].Value = cards[0].chart.all; cards.Remove(cards[0]); } } Print(System.DateTime.Now.TimeOfDay.ToString(), 1); SaveData(sheets["card"]); excel.Save(); } }
public static void GenerateWorksheet(ExcelWorksheets worksheets, DataTable dataTable, string strWorksheetName) { try { ExcelWorksheet ws; if (worksheets.Count(w => w.Name == strWorksheetName) == 0) { ws = worksheets.Add(strWorksheetName); Log.LogMessage("Adding new Worksheet: " + strWorksheetName); } else { ws = worksheets[strWorksheetName]; ws.Cells.Clear(); Log.LogMessage("Clearing Values for Worksheet: " + strWorksheetName); } int cols = ws.Dimension?.Columns ?? dataTable.Columns.Count; object[] columns = dataTable.Rows[0].ItemArray; ws.Cells["A1"].LoadFromDataTable(dataTable, true); for (int x = 1; x <= columns.Count(); x++) { if (Config.Dates.Contains(ws.Cells[1, x].Value)) { ws.Column(x).Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern; } } Log.LogMessage("Added data to Worksheet: " + strWorksheetName); // format header cells using (var range = ws.Cells[1, 1, 1, cols]) { Log.LogMessage("Formating Header Cells for Worksheet: " + strWorksheetName); range.Style.Font.Bold = true; range.Style.Fill.PatternType = ExcelFillStyle.Solid; range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue); range.Style.Font.Color.SetColor(Color.White); } // Create autofilter for the range of cells ws.Cells[ws.Dimension?.Address].AutoFilter = true; // Autofit columns for all cells ws.Cells[ws.Dimension?.Address].AutoFitColumns(); Log.LogMessage("Created " + strWorksheetName + " Worksheet"); } catch (Exception ex) { Log.LogMessage("Error Creating " + strWorksheetName + ", Error: " + ex.Message); } }
public static ExcelWorksheet CopyWithoutContent(this ExcelWorksheets sheets, string name, string newName) { var sheet = sheets.Copy(name, newName); var dimension = sheet.Dimension; sheet.Cells[dimension.Start.Row, dimension.Start.Column, dimension.End.Row, dimension.End.Column].Value = null; return(sheet); }
public void SetUp() { if (File.Exists(Filename)) { File.Delete(Filename); } package = new ExcelPackage(new FileInfo(Filename)); doc = package.Workbook.Worksheets; }
private static void WriteChange(ExcelWorksheet newSheet) { ReadExcel(delegate(ExcelPackage package) { ExcelWorksheets sheets = package.Workbook.Worksheets; ExcelWorksheet sheet = sheets["Sheet1"]; int rowsCount = 26; int m_old_typeIndex = 0; for (int i = 1; i < rowsCount; i++) { object obj = sheet.Cells[1, i].Value; if (obj != null) { string tempValue = obj.ToString(); if (tempValue.Equals("子类型大小(MB)")) { m_old_typeIndex = i; } } } for (int i = 0; i < typeList.Count; i++) { if ((typeList[i].Equals("apk") || typeList[i].Equals("ab"))) { if (newSheet.Cells[i + 1, oldIndex].Value != null) { int iso_index = int.Parse(newSheet.Cells[i + 1, oldIndex].Value.ToString()); if (sheet.Cells[iso_index, m_old_typeIndex].Value != null) { float old_value = float.Parse(sheet.Cells[iso_index, m_old_typeIndex].Value.ToString()); float new_value = float.Parse(newSheet.Cells[i + 1, childSizeTypeIndex].Value.ToString()); if (old_value > new_value) { newSheet.Cells[i + 1, changeIndex].Value = "下降" + Math.Abs(new_value - old_value); //ExcelColor ec = new ExcelColor(); //newSheet.Cells[i + 1, changeIndex].Style. } else if (old_value < new_value) { newSheet.Cells[i + 1, changeIndex].Value = "上升" + Math.Abs(new_value - old_value); } else { newSheet.Cells[i + 1, changeIndex].Value = "无上升下降"; } UnityEngine.Debug.Log("iso_index:" + iso_index + " old_value:" + old_value + " new_value:" + new_value); } } } } package.Save(); }, @"E:\wendang文档\炫舞手游_2017年12月份版本\资源总量分析\资源总量分析_2017_10_23.xlsx"); }
/// <summary> /// Add a new sheet to the collection. Replace if already exists. /// </summary> /// <param name="sheets"></param> /// <param name="name"></param> /// <returns></returns> public static ExcelWorksheet AddOrReplace(this ExcelWorksheets sheets, string name) { if (sheets.Any(x => StringComparer.OrdinalIgnoreCase.Equals(x.Name, name))) { sheets.Delete(name); } return(sheets.Add(name)); }
private static void SetAnoMes(ExcelWorksheets wss, List <LancamentoViewModel> lancamentos) { var mes = $"{(DateTime)lancamentos.First().Data:MMM}".ToUpper(); var ano = $"{(DateTime)lancamentos.First().Data:yyyy}"; (wss["Resumo"].Drawings["Ano do Orçamento"] as ExcelShape).Text = $"{mes} {ano}"; (wss["Despesas"].Drawings["Ano do Orçamento"] as ExcelShape).Text = $"{mes} {ano}"; (wss["Receitas"].Drawings["Ano do Orçamento"] as ExcelShape).Text = $"{mes} {ano}"; }
private void SetChineseExcel(string path, Action <List <object> > action, List <object> arg) { using (ExcelPackage sourcePackage = new ExcelPackage(new FileInfo(path))) { //统计所有表中的中文,放入字典中 ExcelWorksheets sheets = sourcePackage.Workbook.Worksheets; foreach (ExcelWorksheet sheet in sheets) { //排除两个sheet if (sheet.Name == "列表" || sheet.Name == "样例" || sheet.Name == "MasterSystemVariable") { continue; } int rows = sheet.Dimension.Rows; int cols = sheet.Dimension.Columns; for (int j = 1; j <= cols; j++) { if (sheet.Cells[3, j].Value == null) { continue; } //第三行是值类型 string typeStr = sheet.Cells[3, j].Value.ToString(); //如果不是字符串则不作处理 if (!typeStr.StartsWith("varchar") && !typeStr.StartsWith("nvarchar")) { continue; } //第4行开始为正式数据 for (int i = 4; i <= rows; i++) { //如果第一行就为空,则代表后面没数据了,直接break if (sheet.Cells[i, 1].Value == null) { break; } //如果不是第一行为空,就continue if (sheet.Cells[i, j].Value == null) { continue; } if (action != null) { List <object> temp = new List <object>(); temp.AddRange(arg); temp.Add(sheet); temp.Add(i); temp.Add(j); action(temp); } } } //break; } } }
/// <summary> /// 添加数据 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="worksheets"></param> /// <param name="name"></param> /// <param name="list"></param> /// <param name="tableStyles"></param> public static void AddDataToWorkSheet <T>(this ExcelWorksheets worksheets, String name, IList <T> list, OfficeOpenXml.Table.TableStyles tableStyles = OfficeOpenXml.Table.TableStyles.Light8) { ExcelWorksheet worksheet = worksheets.Add(name); var dataRange = worksheet.Cells["A1"].LoadFromCollection( list, true, tableStyles); dataRange.AutoFitColumns(); return; }
public ExcelWorksheetsInstance(ObjectInstance prototype, ExcelWorksheets excelWorksheets) : this(prototype) { if (excelWorksheets == null) { throw new ArgumentNullException("excelWorksheets"); } m_excelWorksheets = excelWorksheets; }
public static void readAbPath(ExcelPackage package) { ExcelWorksheets sheets = package.Workbook.Worksheets; ExcelWorksheet sheet = sheets["Sheet1"]; ReadTypeAndPath(sheet, typeList, pathList); WritePIInfo(package, sheet); UnityEngine.Debug.Log("typeList:" + typeList.Count + " pathList:" + pathList.Count); }
public Workbook() { _Properties = new DocumentProperties(); _ExcelWorkbook = new ExcelWorkbookDetails(); _SheetStyles = new SheetStyles(); _Worksheets = new ExcelWorksheets(); _Names = new NamedRanges(); }