/// <summary> /// Sets the font color of ExcelColumn from a Color object /// </summary> /// <param name="column"></param> /// <param name="fontColor"></param> /// <returns></returns> public static ExcelColumn SetFontColor(this ExcelColumn column, Color fontColor) { column.Style.SetFontColor(fontColor); return(column); }
/// <summary> /// Sets the horizontal alignment of ExcelColumn /// </summary> /// <param name="column"></param> /// <param name="horizontalAlignment"></param> /// <returns></returns> public static ExcelColumn SetHorizontalAlignment(this ExcelColumn column, ExcelHorizontalAlignment horizontalAlignment) { column.Style.SetHorizontalAlignment(horizontalAlignment); return(column); }
public ColumnBuilder(ExcelColumn column) { Column = column; }
public static void LoadExcelData() { ExcelBusy = true; SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); string path; using (var dialog = new OpenFileDialog()) { dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); dialog.Filter = @"Excel files (*.xlsx)|*.xlsx"; dialog.FilterIndex = 1; if (dialog.ShowDialog() != DialogResult.OK) { return; } path = dialog.FileName; } var workbook = ExcelFile.Load(path); var worksheet = workbook.Worksheets[0]; ExcelColumn nameColumn = null, phoneNumberColumn = null, birthDateColumn = null; foreach (var cell in worksheet.GetUsedCellRange(true)) { if (cell.ValueType == CellValueType.Null) { continue; } if (cell.StringValue.Contains("Participant Name")) { nameColumn = cell.Column; } else if (cell.StringValue.Contains("Participants' Birth Date")) { birthDateColumn = cell.Column; } else if (cell.StringValue.Contains("Phones")) { phoneNumberColumn = cell.Column; } if (nameColumn != null && phoneNumberColumn != null && birthDateColumn != null) { break; } } if (nameColumn == null || phoneNumberColumn == null || birthDateColumn == null) { throw new NoNullAllowedException(); } var nameStrings = (from nameCell in nameColumn.Cells where nameCell.ValueType != CellValueType.Null && !nameCell.StringValue.Contains("Participant Name") select nameCell.StringValue).ToList(); var phoneStrings = (from phoneCell in phoneNumberColumn.Cells where phoneCell.ValueType != CellValueType.Null && !phoneCell.StringValue.Contains("Phones") select phoneCell.StringValue).ToList(); var birthStrings = (from birthCell in birthDateColumn.Cells where birthCell.ValueType != CellValueType.Null && !birthCell.StringValue.Contains("Participants' Birth Date") select birthCell.StringValue).ToList(); using (var db = new BirthdayEntriesContext()) { db.Database.ExecuteSqlCommand("TRUNCATE TABLE [BirthdayEntries]"); for (int i = 0; i < nameStrings.Count; i++) { if (!string.IsNullOrWhiteSpace(nameStrings[i]) && !string.IsNullOrWhiteSpace(phoneStrings[i]) && !string.IsNullOrWhiteSpace(birthStrings[i])) { db.BirthdayEntries.Add(new BirthdayEntry { Name = nameStrings[i], Number = ProcessPhone(phoneStrings[i]), Birthday = DateTime.Parse(ProcessDates(birthStrings[i])), SmsDate = DateTime.Parse(SqlDateTime.MinValue.ToString()) }); } } db.SaveChanges(); } ExcelBusy = false; }
internal static void DeleteColumn(ExcelWorksheet ws, int columnFrom, int columns) { ValidateColumn(columnFrom, columns); lock (ws) { //Set previous column Max to Row before if it spans the deleted column range. ExcelColumn col = ws.GetValueInner(0, columnFrom) as ExcelColumn; if (col == null) { var r = 0; var c = columnFrom; if (ws._values.PrevCell(ref r, ref c)) { col = ws.GetValueInner(0, c) as ExcelColumn; if (col._columnMax >= columnFrom) { col.ColumnMax = columnFrom - 1; } } } var delRange = new ExcelAddressBase(1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1); WorksheetRangeHelper.ConvertEffectedSharedFormulasToCellFormulas(ws, delRange); DeleteCellStores(ws, 0, columnFrom, 0, columns); foreach (var wsToUpdate in ws.Workbook.Worksheets) { FixFormulasDeleteColumn(wsToUpdate, columnFrom, columns, ws.Name); } WorksheetRangeHelper.FixMergedCellsColumn(ws, columnFrom, columns, true); foreach (var tbl in ws.Tables) { if (columnFrom >= tbl.Address.Start.Column && columnFrom <= tbl.Address.End.Column) { var node = tbl.Columns[0].TopNode.ParentNode; var ix = columnFrom - tbl.Address.Start.Column; for (int i = 0; i < columns; i++) { if (node.ChildNodes.Count > ix) { node.RemoveChild(node.ChildNodes[ix]); } } tbl._cols = new ExcelTableColumnCollection(tbl); } tbl.Address = tbl.Address.DeleteColumn(columnFrom, columns); foreach (var ptbl in ws.PivotTables) { if (ptbl.Address.Start.Column > columnFrom + columns) { ptbl.Address = ptbl.Address.DeleteColumn(columnFrom, columns); } if (ptbl.CacheDefinition.SourceRange.Start.Column > columnFrom + columns) { ptbl.CacheDefinition.SourceRange.Address = ptbl.CacheDefinition.SourceRange.DeleteColumn(columnFrom, columns).Address; } } } //Adjust DataValidation foreach (ExcelDataValidation dv in ws.DataValidations) { var addr = dv.Address; if (addr.Start.Column > columnFrom + columns) { var newAddr = addr.DeleteColumn(columnFrom, columns).Address; if (addr.Address != newAddr) { dv.SetAddress(newAddr); } } } var range = ws.Cells[1, columnFrom, ExcelPackage.MaxRows, columnFrom + columns - 1]; var effectedAddress = GetEffectedRange(range, eShiftTypeDelete.Left); DeleteFilterAddress(range, effectedAddress, eShiftTypeDelete.Left); DeleteSparkLinesAddress(range, eShiftTypeDelete.Left, effectedAddress); //Adjust drawing positions. WorksheetRangeHelper.AdjustDrawingsColumn(ws, columnFrom, -columns); } }
public void RemoveColumn(ExcelColumn <T> column) { RemoveColumn(Columns.IndexOf(column)); }
public static void GenerovatPrehledXLS(this STAG_Classes.STAG_Database data, string path) { // Kramář W.I.P. FileInfo file = new FileInfo(path); if (file.Exists) { file.Delete(); } using (var excel = new ExcelPackage(file)) { ExcelWorksheet sheet; for (int j = 0; j < 3; j++) { if (j == 0) { sheet = excel.Workbook.Worksheets.Add("ZS"); } else if (j == 1) { sheet = excel.Workbook.Worksheets.Add("LS"); } else { sheet = excel.Workbook.Worksheets.Add("ZS+LS"); } sheet.Cells[1, 1].Value = "Osobni cislo"; sheet.Cells[1, 2].Value = "Rocnik"; sheet.Cells[1, 3].Value = "St. program"; sheet.Cells[1, 4].Value = "Forma"; var katedry = data.HiearchiePracovist[data.Fakulta]; for (int i = 0; i < katedry.Count; i++) { sheet.Cells[1, i + 5].Value = katedry.Keys.ElementAt(i); } sheet.Cells[1, katedry.Count + 5].Value = "jiná"; // st.program // forma // foreach (katedry) // debug - vsechny predmety studenta int row = 2; Dictionary <string, double> podiKatedry; foreach (var student in data.Students) { sheet.Cells[row, 1].Value = student.OsCislo; sheet.Cells[row, 2].Value = student.Rocnik; sheet.Cells[row, 3].Value = student.KodSp; sheet.Cells[row, 4].Value = student.FormaSp; if (j == 0) { podiKatedry = student.PodilKatedryZS; } else if (j == 1) { podiKatedry = student.PodilKatedryLS; } else { podiKatedry = student.PodilKatedry; } for (int i = 0; i < katedry.Count; i++) { if (podiKatedry.ContainsKey(katedry.Keys.ElementAt(i))) { sheet.Cells[row, i + 5].Value = Math.Round(podiKatedry[katedry.Keys.ElementAt(i)], 2); } else { sheet.Cells[row, i + 5].Value = 0; } } sheet.Cells[row, katedry.Count + 5].Value = (from x in podiKatedry where !katedry.Keys.Contains(x.Key) select x.Value).Sum(); row++; } for (int i = 1; i <= 4; i++) { ExcelColumn col = sheet.Column(i); col.AutoFit(); col.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; } } excel.Save(); } }
internal OpenXmlExcelColumn(ExcelColumn obj) { _obj = obj; }
/// <summary> /// 读取Excel文档结构 /// </summary> private void InitExcelStructure() { try { _excelStructure = new ExcelStructure() { Name = _fileName, }; var sheets = _excelStructure.Sheets; var excelTablesName = GetExcelTablesName(); //获取excel表头 if (excelTablesName == null || !excelTablesName.Any()) //1.0、无工作表 { _checkReport.ExcelStructureError("无工作表", "没有找到工作表,请检查Excel是否有效!"); } else { var sheetIndex = -1; foreach (var sheetName in excelTablesName) { sheetIndex++; if (string.IsNullOrWhiteSpace(sheetName)) _checkReport.ExcelStructureError("工作表名称无效", $"----第{sheetIndex + 1}个工作表名称无效!"); else { var aSheet = new ExcelSheet() { SheetIndex = sheetIndex, SheetName = sheetName }; var columns = aSheet.Columns; var sheet = _workbook.GetSheet(sheetName); var headerrow = sheet.GetRow(0); if (headerrow == null) { _checkReport.ExcelStructureError("存在空工作表", $"----第{sheetIndex + 1}个工作表,为空工作表!"); } else { var lastCellNum = headerrow.LastCellNum; var continueNullHeader = 0; for (int colIndex = 0; colIndex < lastCellNum; colIndex++) { try { var cell = headerrow.GetCell(colIndex); if (cell == null || cell.ToString().Trim() == "") //1.3 存在为空列名称 { //aCol.ColumnName = NullHeader + colIndex; //cols.Add(aCol); 空列头不加入到表结构中 continueNullHeader++; if (continueNullHeader > 2) break; } else { continueNullHeader = 0; var value = cell.CellType == CellType.String ? cell.StringCellValue.Trim() : cell.ToString().Trim(); if (columns.Any(f => f.ColumnName == Convert.ToString(value))) //1.4存在重复列名 { _checkReport.ExcelStructureError("列名称重复", $"----[{sheetName}]工作表,存在多个名称为[{value}]的列!"); } else { var aCol = new ExcelColumn() { ColumnIndex = colIndex, ColumnName = value.Replace(" ", "").Replace("\n", "").Replace("\r", "") }; columns.Add(aCol); } } } catch (Exception) { _checkReport.ExcelStructureError("列名称读取失败", $"----[{sheetName}]工作表,第{colIndex + 1},列名称读取失败!"); } } } sheets.Add(aSheet); } } } } catch (Exception ex) { _checkReport.ExcelStructureError("读取表结构出现错误", $"----[错误信息]{ex.Message}"); } }
/// <summary> /// Vygenerovani prehledu zateze ziskane analyzou SRA. /// Vystupem je xlsx soubor. /// </summary> /// <param name="data"></param> /// <param name="name">Nazev souboru bez pripony!</param> public static void GenerovatSRAPrehledXLS(this STAG_Classes.STAG_Database data, string name) { FileInfo file; int number = 2; if ((file = new FileInfo(name + ".xlsx")).Exists) { while ((file = new FileInfo(name + number + ".xlsx")).Exists) { number++; } } using (var excel = new ExcelPackage(file)) { ExcelWorksheet sheet; var deprts = data.HiearchiePracovist[data.Fakulta]; double per; int row = 2; int col = 5; sheet = excel.Workbook.Worksheets.Add("sheet"); sheet.Cells[1, 1].Value = "Osobni cislo"; sheet.Cells[1, 2].Value = "Rocnik"; sheet.Cells[1, 3].Value = "St. program"; sheet.Cells[1, 4].Value = "Forma"; foreach (var dep in deprts) { sheet.Cells[1, col++].Value = dep.Key; } sheet.Cells[1, col].Value = "jiná"; foreach (var student in data.zatezNaStudenta.GetAll()) { sheet.Cells[row, 1].Value = student.Item1.OsCislo; sheet.Cells[row, 2].Value = student.Item1.Rocnik; sheet.Cells[row, 3].Value = student.Item1.KodSp; sheet.Cells[row, 4].Value = student.Item1.FormaSp; col = 5; foreach (var dep in deprts) { if (student.Item2.TryGetValue(dep.Key, out per)) { sheet.Cells[row, col++].Value = per; } else { sheet.Cells[row, col++].Value = 0; } } sheet.Cells[row, col].Value = (from cizi in student.Item2 where !deprts.Keys.Contains(cizi.Key) select cizi.Value).Sum(); row++; } for (int i = 1; i <= 4; i++) { ExcelColumn coll = sheet.Column(i); coll.AutoFit(); coll.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; } excel.Save(); } }
public ExcelCell(int colIndex, int rowIndex, string value) { _column = new ExcelColumn(colIndex); _row = new ExcelRow(rowIndex); _value = value; }
protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { try { Int32 ColumnaDetalle = 1; Int32 Contador = 0; DataTable Data = AtencionPeluqueriaBuss.BuscarCanil(0, Request.QueryString["txtfechaIni"], Request.QueryString["txtFechaFinal"], Request.QueryString["cboRecurso"], Request.QueryString["cboEstado"]); DataTable Data2 = AtencionPeluqueriaBuss.BuscarSector(0, Request.QueryString["txtfechaIni"], Request.QueryString["txtFechaFinal"], Request.QueryString["cboRecurso"], Request.QueryString["cboEstado"]); string Name = Convert.ToString(DateTime.Now.Ticks); using (ExcelPackage package = new ExcelPackage()) { ExcelWorksheet WorkSheet = package.Workbook.Worksheets.Add("BASE"); Int32 Fila = 2; if (Data.Rows.Count > 0 && (Request.QueryString["cboRecurso"] == "0" || Request.QueryString["cboRecurso"] == "" || Request.QueryString["cboRecurso"] == "1")) { ExcelRange range = WorkSheet.Cells[2, 1, 2, 6]; range.Merge = true; WorkSheet.Cells[Fila, 1].Style.Font.SetFromFont(new Font("Calibri", 16, FontStyle.Bold)); WorkSheet.Cells[Fila, 1].Style.Font.Color.SetColor(Color.Black); WorkSheet.Cells[Fila, 1].Value = "Listado de Canil"; range.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; Fila = Fila + 2; WorkSheet.Cells[Fila, 1].Value = "CODIGO"; WorkSheet.Cells[Fila, 2].Value = "NOMBRE"; WorkSheet.Cells[Fila, 3].Value = "TAMAÑO"; WorkSheet.Cells[Fila, 4].Value = "ESTADO"; WorkSheet.Cells[Fila, 5].Value = "ESPECIE"; WorkSheet.Cells[Fila, 6].Value = "OBSERVACIONES"; ExcelRange RangoDeCeldasNameT2 = WorkSheet.Cells[Fila, 1, Fila, 6]; RangoDeCeldasNameT2.Style.Border.Top.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Border.Left.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Border.Right.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT2.Style.Font.Color.SetColor(Color.Black); RangoDeCeldasNameT2.Style.Fill.PatternType = ExcelFillStyle.Solid; RangoDeCeldasNameT2.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FACA2C")); RangoDeCeldasNameT2.Style.Font.Bold = true; RangoDeCeldasNameT2.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; RangoDeCeldasNameT2.Style.WrapText = true; ColumnaDetalle = 1; Contador = 0; foreach (DataRow dr2 in Data.Rows) { Fila = Fila + 1; ColumnaDetalle = 1; foreach (DataColumn dr in Data.Columns) { if (ColumnaDetalle <= 6) { WorkSheet.Cells[Fila, ColumnaDetalle].Value = dr2[dr.ColumnName]; } ColumnaDetalle = ColumnaDetalle + 1; Contador = Contador + 1; } ExcelRange rangeCab6 = WorkSheet.Cells[Fila, 1, Fila, 6]; rangeCab6.Style.Border.Top.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.Left.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.Right.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; rangeCab6.Style.Border.BorderAround(ExcelBorderStyle.Thin); rangeCab6.Style.Font.Color.SetColor(Color.Black); rangeCab6.Style.Font.Bold = false; rangeCab6.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; rangeCab6.Style.WrapText = true; } Fila = Fila + 2; } if (Data2.Rows.Count > 0 && (Request.QueryString["cboRecurso"] == "0" || Request.QueryString["cboRecurso"] == "" || Request.QueryString["cboRecurso"] == "2")) { ExcelRange range5 = WorkSheet.Cells[Fila, 1, Fila, 6]; range5.Merge = true; WorkSheet.Cells[Fila, 1].Style.Font.SetFromFont(new Font("Calibri", 16, FontStyle.Bold)); WorkSheet.Cells[Fila, 1].Style.Font.Color.SetColor(Color.Black); WorkSheet.Cells[Fila, 1].Value = "Listado de Sectores"; range5.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; Fila = Fila + 2; WorkSheet.Cells[Fila, 1].Value = "CODIGO"; WorkSheet.Cells[Fila, 2].Value = "NOMBRE"; WorkSheet.Cells[Fila, 3].Value = "SERVICIO"; WorkSheet.Cells[Fila, 4].Value = "ESTADO"; WorkSheet.Cells[Fila, 5].Value = "OBSERVACIONES"; ExcelRange RangoDeCeldasName5 = WorkSheet.Cells[Fila, 5, Fila, 6]; RangoDeCeldasName5.Merge = true; ExcelRange RangoDeCeldasNameT4 = WorkSheet.Cells[Fila, 1, Fila, 6]; RangoDeCeldasNameT4.Style.Border.Top.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Border.Left.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Border.Right.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; RangoDeCeldasNameT4.Style.Font.Color.SetColor(Color.Black); RangoDeCeldasNameT4.Style.Fill.PatternType = ExcelFillStyle.Solid; RangoDeCeldasNameT4.Style.Fill.BackgroundColor.SetColor(System.Drawing.ColorTranslator.FromHtml("#FACA2C")); RangoDeCeldasNameT4.Style.Font.Bold = true; RangoDeCeldasNameT4.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; RangoDeCeldasNameT4.Style.WrapText = true; ColumnaDetalle = 1; Contador = 0; foreach (DataRow dr3 in Data2.Rows) { Fila = Fila + 1; ColumnaDetalle = 1; foreach (DataColumn dr4 in Data2.Columns) { if (ColumnaDetalle <= 5) { WorkSheet.Cells[Fila, ColumnaDetalle].Value = dr3[dr4.ColumnName]; if (ColumnaDetalle == 5) { ExcelRange RangoDeCeldasNamecol5 = WorkSheet.Cells[Fila, 5, Fila, 6]; RangoDeCeldasNamecol5.Merge = true; } } ColumnaDetalle = ColumnaDetalle + 1; Contador = Contador + 1; } ExcelRange RangeCab8 = WorkSheet.Cells[Fila, 1, Fila, 6]; RangeCab8.Style.Border.Top.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.Left.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.Right.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.Bottom.Style = ExcelBorderStyle.Thin; RangeCab8.Style.Border.BorderAround(ExcelBorderStyle.Thin); RangeCab8.Style.Font.Color.SetColor(Color.Black); RangeCab8.Style.Font.Bold = false; RangeCab8.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous; RangeCab8.Style.WrapText = true; } } ExcelColumn Col1 = WorkSheet.Column(1); ExcelColumn Col2 = WorkSheet.Column(2); ExcelColumn Col3 = WorkSheet.Column(3); ExcelColumn Col4 = WorkSheet.Column(4); ExcelColumn Col5 = WorkSheet.Column(5); ExcelColumn Col6 = WorkSheet.Column(6); Col1.Width = 20; Col2.Width = 20; Col3.Width = 20; Col4.Width = 20; Col5.Width = 20; Col6.Width = 40; Response.Clear(); Response.ContentType = "application/xlsx"; Response.AddHeader("content-disposition", "attachment; filename=Reporte_Canil_Sectores.xlsx"); Response.BinaryWrite(package.GetAsByteArray()); Response.End(); } } catch (Exception ex) { lblmsg.Text = "Error interno en el sistema contacte a su administrador " + " Detalle:" + ex.Message; msgError.Clear(); msgError.AppendLine("Fecha:" + DateTime.Now.ToString()); msgError.AppendLine("Descripción:" + ex.Message); msgError.AppendLine("Detalle:" + ex.StackTrace); log.Error(msgError.ToString()); } } }
/// <summary> /// 读取hdr表说明 转换成list 导入数据库 与数据库中的列 进行比较 判断excel与表结构是否一致 /// </summary> /// <param name="cells"></param> /// <returns></returns> public IList <ExcelColumn> Process(Cells cells) { List <ExcelColumn> list = new List <ExcelColumn>(); string TableName = string.Empty; //表名 (organization(机构信息)) string Schema = string.Empty; //业务域(mdm(主数据)) string TableComment = string.Empty; //表说明 (机构信息,如:医疗机构、科研机构等) for (int row = 0; row < cells.Rows.Count; row++) { Row current_row = cells.CheckRow(row); if (current_row == null || current_row.FirstDataCell == null) { continue; } //表名 Cell cel = current_row[CellsHelper.ColumnNameToIndex("A")]; if (cel.IsMerged) { //业务域 及 表说明 var range = cel.GetMergedRange(); if (range.CellCount == 9) {//表的第一行 TableName = cel.GetStringValue(CellValueFormatStrategy.None); } if (range.CellCount == 2) {//第二行 Schema = current_row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None); TableComment = current_row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None); } continue; } else if (cel.GetStringValue(CellValueFormatStrategy.None) == "序号") //列头 { continue; } else //表列 { ExcelColumn ec = new ExcelColumn(); var tableNamearray = TableName.Split(new string[4] { "(", ")", "(", ")" }, StringSplitOptions.None); ec.TableName = tableNamearray[0];//表名 (organization(机构信息)) ec.TableNameCh = string.Join("", tableNamearray.Where(p => p != tableNamearray[0]).ToArray()); var schemaarray = Schema.Split(new string[4] { "(", ")", "(", ")" }, StringSplitOptions.None); ec.Schema = schemaarray[0]; //业务域(mdm(主数据)) ec.SchemaCh = string.Join("", schemaarray.Where(p => p != schemaarray[0]).ToArray()); //业务域(mdm(主数据)) ec.TableComment = TableComment; //表说明 (机构信息,如:医疗机构、科研机构等) ec.SerialNumber = cel.GetStringValue(CellValueFormatStrategy.None); //序号 ec.ColumnName = current_row[CellsHelper.ColumnNameToIndex("B")].GetStringValue(CellValueFormatStrategy.None); //列名 ec.ColumnComment = current_row[CellsHelper.ColumnNameToIndex("C")].GetStringValue(CellValueFormatStrategy.None); //中文名 ec.DataType = current_row[CellsHelper.ColumnNameToIndex("D")].GetStringValue(CellValueFormatStrategy.None); //字段类型 ec.IsNull = current_row[CellsHelper.ColumnNameToIndex("E")].GetStringValue(CellValueFormatStrategy.None); //允许空 ec.ForeignKey = current_row[CellsHelper.ColumnNameToIndex("F")].GetStringValue(CellValueFormatStrategy.None); //外键(关联字段) ec.CodeSystem = current_row[CellsHelper.ColumnNameToIndex("G")].GetStringValue(CellValueFormatStrategy.None); //字典系统(值域) ec.IsStandard = current_row[CellsHelper.ColumnNameToIndex("H")].GetStringValue(CellValueFormatStrategy.None); //字典表标准化 ec.Description = current_row[CellsHelper.ColumnNameToIndex("I")].GetStringValue(CellValueFormatStrategy.None); //说明 list.Add(ec); continue; } } return(list); }
public Column(ExcelColumn column) { _column = column; }
private void SetStyleAddress(StyleBase sender, StyleChangeEventArgs e, ExcelAddressBase address, ExcelWorksheet ws, ref Dictionary <int, int> styleCashe) { if (address.Start.Column == 0 || address.Start.Row == 0) { throw new Exception("error address"); } //Columns if (address.Start.Row == 1 && address.End.Row == ExcelPackage.MaxRows) { ExcelColumn column; int col = address.Start.Column, row = 0; bool isNew; //Get the startcolumn object o = null; if (!ws.ExistsValueInner(0, address.Start.Column, ref o)) { column = ws.Column(address.Start.Column); isNew = true; } else { //column = (ExcelColumn)ws.GetValueInner(0, address.Start.Column); column = (ExcelColumn)o; isNew = false; } var prevColumnMax = column.ColumnMax; while (column.ColumnMin <= address.End.Column) { if (column.ColumnMin > prevColumnMax + 1) { ExcelColumn newColumn = ws.Column(prevColumnMax + 1); newColumn.ColumnMax = column.ColumnMin - 1; AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID); } if (column.ColumnMax > address.End.Column) { ExcelColumn newCol = ws.CopyColumn(column, address.End.Column + 1, column.ColumnMax); column.ColumnMax = address.End.Column; } var s = ws.GetStyleInner(0, column.ColumnMin); AddNewStyleColumn(sender, e, ws, styleCashe, column, s); //index++; prevColumnMax = column.ColumnMax; if (!ws._values.NextCell(ref row, ref col) || row > 0) { if (column._columnMax == address.End.Column) { break; } if (isNew) { column._columnMax = address.End.Column; } else { ExcelColumn newColumn = ws.Column(column._columnMax + 1); newColumn.ColumnMax = address.End.Column; AddNewStyleColumn(sender, e, ws, styleCashe, newColumn, newColumn.StyleID); column = newColumn; } break; } column = ws.GetValueInner(0, col) as ExcelColumn; } if (column._columnMax < address.End.Column) { ExcelColumn newCol = ws.Column(column._columnMax + 1); newCol._columnMax = address.End.Column; var s = ws.GetStyleInner(0, column.ColumnMin); if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(0, column.ColumnMin, newId); } column._columnMax = address.End.Column; } //Set for individual cells in the span. We loop all cells here since the cells are sorted with columns first. var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, address._fromCol, address._toRow, address._toCol); while (cse.Next()) { if (cse.Column >= address.Start.Column && cse.Column <= address.End.Column && cse.Value._styleId != 0) { if (styleCashe.ContainsKey(cse.Value._styleId)) { ws.SetStyleInner(cse.Row, cse.Column, styleCashe[cse.Value._styleId]); } else { ExcelXfs st = CellXfs[cse.Value._styleId]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(cse.Value._styleId, newId); ws.SetStyleInner(cse.Row, cse.Column, newId); } } } if (!(address._fromCol == 1 && address._toCol == ExcelPackage.MaxColumns)) { //Update cells with styled columns cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 1, 0, address._toRow, 0); while (cse.Next()) { if (cse.Value._styleId == 0) { continue; } for (var c = address._fromCol; c <= address._toCol; c++) { if (!ws.ExistsStyleInner(cse.Row, c)) { if (styleCashe.ContainsKey(cse.Value._styleId)) { ws.SetStyleInner(cse.Row, c, styleCashe[cse.Value._styleId]); } else { ExcelXfs st = CellXfs[cse.Value._styleId]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(cse.Value._styleId, newId); ws.SetStyleInner(cse.Row, c, newId); } } } } } } //Rows else if (address.Start.Column == 1 && address.End.Column == ExcelPackage.MaxColumns) { for (var rowNum = address.Start.Row; rowNum <= address.End.Row; rowNum++) { var s = ws.GetStyleInner(rowNum, 0); if (s == 0) { //iterate all columns and set the row to the style of the last column var cse = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, ExcelPackage.MaxColumns); while (cse.Next()) { s = cse.Value._styleId; if (s == 0) { continue; } var c = ws.GetValueInner(cse.Row, cse.Column) as ExcelColumn; if (c != null && c.ColumnMax < ExcelPackage.MaxColumns) { for (var col = c.ColumnMin; col < c.ColumnMax; col++) { if (!ws.ExistsStyleInner(rowNum, col)) { ws.SetStyleInner(rowNum, col, s); } } } } ws.SetStyleInner(rowNum, 0, s); cse.Dispose(); } if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(rowNum, 0, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(rowNum, 0, newId); } } //Update individual cells var cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, address._fromRow, address._fromCol, address._toRow, address._toCol); while (cse2.Next()) { var s = cse2.Value._styleId; if (s == 0) { continue; } if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(cse2.Row, cse2.Column, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(cse2.Row, cse2.Column, newId); } } //Update cells with styled rows cse2 = new CellsStoreEnumerator <ExcelCoreValue>(ws._values, 0, 1, 0, address._toCol); while (cse2.Next()) { if (cse2.Value._styleId == 0) { continue; } for (var r = address._fromRow; r <= address._toRow; r++) { if (!ws.ExistsStyleInner(r, cse2.Column)) { var s = cse2.Value._styleId; if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(r, cse2.Column, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(r, cse2.Column, newId); } } } } } else //Cell range { var tmpCache = styleCashe; var rowCache = new Dictionary <int, int>(address.End.Row - address.Start.Row + 1); var colCache = new Dictionary <int, ExcelCoreValue>(address.End.Column - address.Start.Column + 1); ws._values.SetRangeValueSpecial(address.Start.Row, address.Start.Column, address.End.Row, address.End.Column, (list, index, row, column, args) => { // Optimized GetStyleID var s = list[index]._styleId; if (s == 0 && !ws.ExistsStyleInner(row, 0, ref s)) { // get row styleId with cache if (!rowCache.ContainsKey(row)) { rowCache.Add(row, ws._values.GetValue(row, 0)._styleId); } s = rowCache[row]; if (s == 0) { // get column styleId with cache if (!colCache.ContainsKey(column)) { colCache.Add(column, ws._values.GetValue(0, column)); } s = colCache[column]._styleId; if (s == 0) { int r = 0, c = column; if (ws._values.PrevCell(ref r, ref c)) { //var val = ws._values.GetValue(0, c); if (!colCache.ContainsKey(c)) { colCache.Add(c, ws._values.GetValue(0, c)); } ExcelCoreValue val = colCache[c]; var colObj = (ExcelColumn)val._value; if (colObj != null && colObj.ColumnMax >= column) //Fixes issue 15174 { s = val._styleId; } } } } } if (tmpCache.ContainsKey(s)) { //ws.SetStyleInner(row, column, tmpCache[s]); list[index] = new ExcelCoreValue { _value = list[index]._value, _styleId = tmpCache[s] }; } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); tmpCache.Add(s, newId); //ws.SetStyleInner(row, column, newId); list[index] = new ExcelCoreValue { _value = list[index]._value, _styleId = newId }; } }, e); } }
/// <summary> /// /// </summary> /// <param name="column"></param> /// <param name="tempColumn"></param> public static void CopyFromTemplate(this ExcelColumn column, ExcelColumn tempColumn) { column.Width = tempColumn.Width; }
public string Apply(ExcelColumn <object> column, string value) { var formulaChars = new char[] { '=', '+', '-', '@', '|' }; return(value.OrEmpty().TrimStart(formulaChars)); }
public override int GetHashCode() { return(ExcelColumn.GetHashCode() + DbColumn.GetHashCode()); }
public List <ExcelSheet> Parse(ExcelUpload uploadModel) { List <ExcelSheet> returnSheets = new List <ExcelSheet>(); using (MemoryStream ms = new MemoryStream(uploadModel.ExcelFile)) { SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(ms, false); WorkbookPart wbPart = spreadsheet.WorkbookPart; var sheets = wbPart.Workbook.Descendants <Sheet>(); foreach (Sheet sheet in sheets) { ExcelSheet newReturnSheet = new ExcelSheet(); WorksheetPart wsPart = (WorksheetPart)(wbPart.GetPartById(sheet.Id)); Worksheet worksheet = wsPart.Worksheet; SheetData sheetData = worksheet.GetFirstChild <SheetData>(); newReturnSheet.SheetName = sheet.Name; var firstRowCells = worksheet.Descendants <Cell>().Where(y => GetCellRow(y.CellReference.Value) == 1); foreach (var firstRowCell in firstRowCells) { ExcelColumn newReturnColumn = new ExcelColumn() { Letter = GetCellLetter(firstRowCell.CellReference.Value), Name = GetCellLetter(firstRowCell.CellReference.Value) }; if (uploadModel.FirstRowIsHeader) { newReturnColumn.Name = GetCellValue(firstRowCell, wbPart); } newReturnSheet.Columns.Add(newReturnColumn); } var rows = sheetData.Descendants <Row>(); foreach (var row in rows) { ExcelRow newReturnRow = new ExcelRow() { RowNumber = (int)row.RowIndex.Value }; if (uploadModel.FirstRowIsHeader && newReturnRow.RowNumber == 1) { continue; } foreach (ExcelColumn column in newReturnSheet.Columns) { var cellReference = string.Format("{0}{1}", column.Letter, newReturnRow.RowNumber); Cell cell = worksheet.Descendants <Cell>().SingleOrDefault(y => y.CellReference.Value == cellReference); ExcelCell newReturnCell = new ExcelCell() { Letter = column.Letter, Value = GetCellValue(cell, wbPart) }; newReturnRow.Cells.Add(newReturnCell); } newReturnSheet.Rows.Add(newReturnRow); } returnSheets.Add(newReturnSheet); } } return(returnSheets); }
public void SetExcelColumnWidth(ExcelColumn column, double width) { excel.SetColumnWidth(column, width); }
private void SetHeaderColumn(ExcelColumn column, object columnData) { column.Style.Numberformat.Format = GetFormattingByColumnName(columnData); column.Width = GetColumnWidthByColumnName(columnData); }
/// <summary> /// 设置表格列伸缩适应单元格文字长度 /// </summary> /// <param name="column"></param> public static void SetFit(this ExcelColumn column) { column.AutoFit(); }
/// <summary> /// Process file stream to html string /// </summary> /// <param name="stream">Excel file stream</param> /// <returns>Html string</returns> public string Process(Stream stream) { ExcelPackage p = new ExcelPackage(stream); // Get the first sheet var sheet = p.Workbook.Worksheets.FirstOrDefault(); if (sheet == null) { throw new Exception("No worksheets found"); } var numberColumns = sheet.Dimension.End.Column; var numberRows = sheet.Dimension.End.Row; StringBuilder s = new StringBuilder(); string[] tableStyles = new string[] { "width: 100%", "table-layout: fixed", "border-collapse: collapse" }; s.Append($"<table style='{string.Join("; ", tableStyles)}'>"); // For each row for (int i = 1; i <= numberRows; i++) { ExcelRow row = sheet.Row(i); s.Append($"<tr style='height: {Utils.FormatNumber(row.Height)}pt'>"); // For each column for (int j = 1; j <= numberColumns; j++) { ExcelColumn column = sheet.Column(j); ExcelRange cell = sheet.Cells[i, j]; int colspan = 1; int rowspan = 1; if (!cell.Merge || (cell.Merge && IsFirstMergeRange(sheet, cell.Address, ref colspan, ref rowspan))) { StylesParser styles = new StylesParser(cell); //styles.Add($"width: {Utils.FormatNumber(sheet.Column(j).Width)}pt;"); if (options.Debug) { styles.Add("border: 1px solid black;"); } s.Append($"<td rowspan='{rowspan}' colspan='{colspan}' style='{styles.Parse()}'>"); if (cell.Value != null) { s.Append(cell.Value.ToString()); } s.Append("</td>"); } } s.Append("</tr>"); } s.Append("</table>"); return(FormatHtml(s.ToString())); }
/// <summary> /// 设置表格列伸缩适应单元格文字长度 /// </summary> /// <param name="column"></param> /// <param name="width"></param> public static void SetFit(this ExcelColumn column, double width) { column.AutoFit(width); }
/// <summary> /// Sets the font of ExcelColumn from a Font object /// </summary> /// <param name="column"></param> /// <param name="font"></param> /// <returns></returns> public static ExcelColumn SetFont(this ExcelColumn column, Font font) { column.Style.SetFont(font); return(column); }
/// <summary> /// 设置列宽 /// </summary> /// <param name="col"></param> /// <param name="width"></param> public static void SetColumnWidth(this ExcelColumn col, int width = 15) { col.Width = width; }
/// <summary> /// Sets the background color of ExcelColumn from a Color object /// </summary> /// <param name="column"></param> /// <param name="backgroundColor"></param> /// <param name="fillStyle"></param> /// <returns></returns> public static ExcelColumn SetBackgroundColor(this ExcelColumn column, Color backgroundColor, ExcelFillStyle fillStyle = ExcelFillStyle.Solid) { column.Style.SetBackgroundColor(backgroundColor, fillStyle); return(column); }
private void AddNewStyleColumn(StyleBase sender, StyleChangeEventArgs e, ExcelWorksheet ws, Dictionary <int, int> styleCashe, ExcelColumn column, int s) { if (styleCashe.ContainsKey(s)) { ws.SetStyleInner(0, column.ColumnMin, styleCashe[s]); } else { ExcelXfs st = CellXfs[s]; var newId = st.GetNewID(CellXfs, sender, e.StyleClass, e.StyleProperty, e.Value); styleCashe.Add(s, newId); ws.SetStyleInner(0, column.ColumnMin, newId); } }
/// <summary> /// Sets the vertical alignment of ExcelColumn /// </summary> /// <param name="column"></param> /// <param name="verticalAlignment"></param> /// <returns></returns> public static ExcelColumn SetVerticalAlignment(this ExcelColumn column, ExcelVerticalAlignment verticalAlignment) { column.Style.SetVerticalAlignment(verticalAlignment); return(column); }
public EpExcelColumn(ExcelWorksheet sheet, int columnNum) { _epWorkSheet = sheet; _columnNum = columnNum; _epColumn = sheet.Column(columnNum); }