private void btnStyles_Click(object sender, RoutedEventArgs e) { SaveBook(book => { // get the sheet that was created by default, give it a name var sheet = book.Sheets[0]; // create styles for odd and even values var styleOdd = new XLStyle(book); styleOdd.Font = new XLFont("Tahoma", 9, false, true); styleOdd.ForeColor = Colors.Blue; var styleEven = new XLStyle(book); styleEven.Font = new XLFont("Tahoma", 9, true, false); styleEven.ForeColor = Colors.Red; styleEven.BackColor = Colors.Yellow; // step 3: write content and format into some cells for (int i = 0; i < 30; i++) { XLCell cell = sheet[i, 0]; cell.Value = i + 1; cell.Style = ((i + 1) % 2 == 0) ? styleEven : styleOdd; } }); }
private void button1_Click(object sender, System.EventArgs e) { // step 1: create a new workbook C1XLBook book = new C1XLBook(); // step 2: get the sheet that was created by default, give it a name XLSheet sheet = book.Sheets[0]; sheet.Name = "Hello World"; // step 3: create styles for odd and even values XLStyle styleOdd = new XLStyle(book); styleOdd.Font = new Font("Tahoma", 9, FontStyle.Italic); styleOdd.ForeColor = Color.Blue; XLStyle styleEven = new XLStyle(book); styleEven.Font = new Font("Tahoma", 9, FontStyle.Bold); styleEven.ForeColor = Color.Red; // step 3: write content and format into some cells for (int i = 0; i < 100; i++) { XLCell cell = sheet[i, 0]; cell.Value = i + 1; cell.Style = ((i + 1) % 2 == 0)? styleEven: styleOdd; } // step 4: save the file string fileName = Application.StartupPath + @"\hello.xls"; book.Save(fileName); System.Diagnostics.Process.Start(fileName); }
private void _btnHello_Click(object sender, RoutedEventArgs e) { // step 1: create a new workbook _book = new C1XLBook(); // step 2: get the sheet that was created by default, give it a name XLSheet sheet = _book.Sheets[0]; sheet.Name = Strings.SheetName; // step 3: create styles for odd and even values XLStyle styleOdd = new XLStyle(_book); styleOdd.Font = new XLFont("Tahoma", 9, false, true); styleOdd.ForeColor = Color.FromArgb(255, 0, 0, 255); XLStyle styleEven = new XLStyle(_book); styleEven.Font = new XLFont("Tahoma", 9, true, false); styleEven.ForeColor = Color.FromArgb(255, 255, 0, 0); // step 4: write content and format into some cells for (int i = 0; i < 100; i++) { XLCell cell = sheet[i, 0]; cell.Value = i + 1; cell.Style = ((i + 1) % 2 == 0) ? styleEven : styleOdd; } // step 5: allow user to save the file _tbContent.Text = Strings.DataCreatedTip; RefreshView(); }
public void exportarConsolidado(DataTable dtDatos, string ruta) { this.oBook = new C1XLBook(); this.sheet = this.oBook.Sheets[0]; this.sheet.Name = "Migración de Inventarios"; this.crearEncabezados(dtDatos); string empty = string.Empty; for (int index1 = 0; index1 < dtDatos.Rows.Count; ++index1) { for (int index2 = 0; index2 < dtDatos.Columns.Count; ++index2) { string str = dtDatos.Rows[index1][index2].ToString(); new XLColumn().Width = 125; XLCell xlCell = this.sheet[index1 + 3, index2]; XLStyle xlStyle = new XLStyle(this.oBook); xlStyle.WordWrap = false; if (index2 == 3 || index2 == 4 || (index2 == 5 || index2 == 6) || index2 == 7 || index2 == 8) { xlStyle.Format = "0,0.0"; xlStyle.AlignHorz = XLAlignHorzEnum.Right; xlCell.Value = (object)(string.IsNullOrEmpty(str) ? Decimal.Zero : Convert.ToDecimal(str)); } else { xlStyle.AlignHorz = XLAlignHorzEnum.Left; xlCell.Value = (object)str; } xlCell.Style = xlStyle; } } this.AutoSizeColumns(this.sheet); this.oBook.Save(ruta); Process.Start(ruta); }
private void TransposeRange(int squareSide) { var cellsToInsert = new Dictionary <XLSheetPoint, XLCell>(); var cellsToDelete = new List <XLSheetPoint>(); var rngToTranspose = Worksheet.Range( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.RowNumber + squareSide - 1, RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); Int32 roCount = rngToTranspose.RowCount(); Int32 coCount = rngToTranspose.ColumnCount(); for (Int32 ro = 1; ro <= roCount; ro++) { for (Int32 co = 1; co <= coCount; co++) { var oldCell = rngToTranspose.Cell(ro, co); var newKey = rngToTranspose.Cell(co, ro).Address; // new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); newCell.CopyFrom(oldCell, true); cellsToInsert.Add(new XLSheetPoint(newKey.RowNumber, newKey.ColumnNumber), newCell); cellsToDelete.Add(new XLSheetPoint(oldCell.Address.RowNumber, oldCell.Address.ColumnNumber)); } } cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); }
public void Add(Int32 row, Int32 column, XLCell cell) { Count++; IncrementUsage(RowsUsed, row); IncrementUsage(ColumnsUsed, column); Dictionary <int, XLCell> columnsCollection; if (!rowsCollection.TryGetValue(row, out columnsCollection)) { columnsCollection = new Dictionary <int, XLCell>(); rowsCollection.Add(row, columnsCollection); } columnsCollection.Add(column, cell); if (row > MaxRowUsed) { MaxRowUsed = row; } if (column > MaxColumnUsed) { MaxColumnUsed = column; } HashSet <Int32> delHash; if (deleted.TryGetValue(row, out delHash)) { delHash.Remove(column); } }
private void LoadSheet(XLSheet sheet) { // load cells grdView.Sheets[1].RowCount = sheet.Rows.Count; //grdView.Sheets[0].ColumnCount = sheet.Columns.Count; for (int r = 0; r < sheet.Rows.Count; r++) { //String aaa = ""; //break; for (int c = 0; c < sheet.Columns.Count; c++) { // get cell XLCell cell = sheet.GetCell(r, c); if (cell == null) { continue; } // apply content //flex[r + frows, c + fcols] = cell.Value; grdView.Sheets[1].Cells[r, c].Value = cell.Value; if (c % 2 == 0) { grdView.Sheets[1].Rows[r].BackColor = Color.Yellow; } // apply style ////CellStyle cs = StyleFromExcel(flex, cell.Style); ////if (cs != null) //// flex.SetCellStyle(r + frows, c + fcols, cs); } } grdView.ActiveSheetIndex = 1; }
public string WriteExcle(FlexigridParam param, dynamic[] query, int from = 1) { var names = param.QType.GetString().Split(','); var cols = param.Cols.GetString().Split(','); C1XLBook _book = new C1XLBook(); string path = @"~/Files/a.xls"; string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path); _book.Load(xlsPath); string guid = Guid.NewGuid().ToString(); string saveFileName = xlsPath.Path(guid); XLSheet sheet = _book.Sheets[0]; XLCell cell = sheet[0, 0]; Dictionary <string, string> propertyName; PropertyInfo[] properties; for (int i = 0; i < names.Length; i++) { cell = sheet[0, i]; if (!string.IsNullOrWhiteSpace(names[i])) { cell.Value = names[i]; //列值 } } for (int i = 0; i < query.Length; i++) { propertyName = new Dictionary <string, string>(); if (query[i] == null) { continue; } Type type = query[i].GetType(); properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public); foreach (PropertyInfo property in properties) { object o = property.GetValue(query[i], null); if (!string.IsNullOrEmpty(property.Name) && o != null) { propertyName.Add(property.Name, o.ToString()); } } int j = 0; cols.All(a => { cell = sheet[i + from, j]; if (propertyName.ContainsKey(a)) //列名 { cell.Value = propertyName[a]; //列值 } j++; return(true); }); } _book.Save(saveFileName); LogClassModels.WriteServiceLog(LogType.Operation, string.Format("../../../Files/{0}.xls", guid) + ",", "导出数据", Result.Succeed); //写入日志 return(string.Format("../../../Files/{0}.xls", guid)); //path.Replace("b",guid); }
private void Initialize(XLCell cell) { Author = cell.Worksheet.Author; Container = this; Anchor = XLDrawingAnchor.MoveAndSizeWithCells; Style = new XLDrawingStyle(); Int32 pRow = cell.Address.RowNumber; Double pRowOffset = 0; if (pRow > 1) { pRow--; double prevHeight = cell.CellAbove().WorksheetRow().Height; if (prevHeight > 7) { pRowOffset = prevHeight - 7; } } Position = new XLDrawingPosition { Column = cell.Address.ColumnNumber + 1, ColumnOffset = 2, Row = pRow, RowOffset = pRowOffset }; ZOrder = cell.Worksheet.ZOrder++; Style .Margins.SetLeft(0.1) .Margins.SetRight(0.1) .Margins.SetTop(0.05) .Margins.SetBottom(0.05) .Margins.SetAutomatic() .Size.SetHeight(59.25) .Size.SetWidth(19.2) .ColorsAndLines.SetLineColor(XLColor.Black) .ColorsAndLines.SetFillColor(XLColor.FromArgb(255, 255, 225)) .ColorsAndLines.SetLineDash(XLDashStyle.Solid) .ColorsAndLines.SetLineStyle(XLLineStyle.Single) .ColorsAndLines.SetLineWeight(0.75) .ColorsAndLines.SetFillTransparency(1) .ColorsAndLines.SetLineTransparency(1) .Alignment.SetHorizontal(XLDrawingHorizontalAlignment.Left) .Alignment.SetVertical(XLDrawingVerticalAlignment.Top) .Alignment.SetDirection(XLDrawingTextDirection.Context) .Alignment.SetOrientation(XLDrawingTextOrientation.LeftToRight) .Properties.SetPositioning(XLDrawingAnchor.Absolute) .Protection.SetLocked() .Protection.SetLockText(); _cell = cell; ShapeId = cell.Worksheet.Workbook.ShapeIdManager.GetNext(); }
public void Add(Int32 row, Int32 column, XLCell cell) { Count++; IncrementUsage(RowsUsed, row); IncrementUsage(ColumnsUsed, column); _cellsDictionary.Add(new XLSheetPoint(row, column), cell); if (row > MaxRowUsed) MaxRowUsed = row; if (column > MaxColumnUsed) MaxColumnUsed = column; var sp = new XLSheetPoint(row, column); if (Deleted.Contains(sp)) Deleted.Remove(sp); }
public void CrearArchivo(DataTable dtDatos, string ruta, bool pVerCosto) { try { this.oBook = new C1XLBook(); this.sheet = this.oBook.Sheets[0]; this.sheet.Name = "Migración de Inventarios"; this.crearEncabezados(dtDatos); string empty = string.Empty; for (int index1 = 0; index1 < dtDatos.Rows.Count; ++index1) { for (int index2 = 0; index2 < dtDatos.Columns.Count; ++index2) { if ((index2 != 20 || pVerCosto) && (index2 != 21 || pVerCosto)) { string str = dtDatos.Rows[index1][index2].ToString(); new XLColumn().Width = 125; XLCell xlCell = this.sheet[index1 + 3, index2]; XLStyle xlStyle = new XLStyle(this.oBook); xlStyle.WordWrap = false; if (index2 == 2 || index2 == 3 || (index2 == 4 || index2 == 5) || index2 == 20 || index2 == 21) { xlStyle.Format = "0,0.0"; xlStyle.AlignHorz = XLAlignHorzEnum.Right; xlCell.Value = (object)(string.IsNullOrEmpty(str) ? Decimal.Zero : Convert.ToDecimal(str)); } else if (index2 == 22 || index2 == 23) { xlStyle.AlignHorz = XLAlignHorzEnum.Right; xlCell.Value = (object)(string.IsNullOrEmpty(str) ? 0 : Convert.ToInt32(str)); } else { xlStyle.AlignHorz = XLAlignHorzEnum.Left; xlCell.Value = (object)str; } xlCell.Style = xlStyle; } } } this.AutoSizeColumns(this.sheet); this.oBook.Save(ruta); Process.Start(ruta); } catch (Exception ex) { throw new Exception(ex.Message); } }
internal void AdjustFormulas(XLCell baseCell, XLCell targetCell) { var keys = Values.Keys.ToList(); foreach (var key in keys) { if (Values[key] == null || !Values[key].IsFormula) { continue; } var r1c1 = baseCell.GetFormulaR1C1(Values[key].Value); Values[key] = new XLFormula { _value = targetCell.GetFormulaA1(r1c1), IsFormula = true }; } }
private static EnumValue<CellValues> GetCellValue(XLCell xlCell) { switch (xlCell.DataType) { case XLCellValues.Text: { return xlCell.ShareString ? CvSharedString : CvInlineString; } case XLCellValues.Number: return CvNumber; case XLCellValues.DateTime: return CvDate; case XLCellValues.Boolean: return CvBoolean; case XLCellValues.TimeSpan: return CvNumber; default: throw new NotImplementedException(); } }
public void Add(Int32 row, Int32 column, XLCell cell) { Count++; IncrementUsage(RowsUsed, row); IncrementUsage(ColumnsUsed, column); Dictionary<int, XLCell> columnsCollection; if (!rowsCollection.TryGetValue(row, out columnsCollection)) { columnsCollection = new Dictionary<int, XLCell>(); rowsCollection.Add(row, columnsCollection); } columnsCollection.Add(column, cell); if (row > MaxRowUsed) MaxRowUsed = row; if (column > MaxColumnUsed) MaxColumnUsed = column; HashSet<Int32> delHash; if (deleted.TryGetValue(row, out delHash)) delHash.Remove(column); }
public void Add(Int32 row, Int32 column, XLCell cell) { Count++; IncrementUsage(RowsUsed, row); IncrementUsage(ColumnsUsed, column); _cellsDictionary.Add(new XLSheetPoint(row, column), cell); if (row > MaxRowUsed) { MaxRowUsed = row; } if (column > MaxColumnUsed) { MaxColumnUsed = column; } var sp = new XLSheetPoint(row, column); if (Deleted.Contains(sp)) { Deleted.Remove(sp); } }
/// <summary> /// Format a cell as a string before we pass to the type converter, so any formatting done in Excel /// is properly applied to the string result that is then sent to the users parser. /// </summary> /// <param name="cell">Cell in the Excel file being formatted</param> /// <param name="row">Row in the excel file</param> /// <param name="index">Column in the excel file</param> /// <param name="capitalBoolean">True to use capital boolean format, false to use C# style</param> /// <returns>Cell value formatted as a string, or the original value if not</returns> private string FormatValueAsString( XLCell cell, int row, int index, bool capitalBoolean) { var value = cell.Value; if (value != null) { // For compatibility with old PHP code, format TRUE and FALSE for boolean values if (value.GetType() == typeof(bool)) { if ((bool)value) { return(capitalBoolean ? "TRUE" : "True"); } return(capitalBoolean ? "FALSE" : "False"); } // Format value if cell has a style with format set if (value is IFormattable formatValue) { var style = cell.Style ?? (_sheet.Rows[row].Style ?? _sheet.Columns[index].Style); if (style != null && style.Format.Length > 0) { var fmt = XLStyle.FormatXLToDotNet(style.Format.ToUpperInvariant()); return(!string.IsNullOrWhiteSpace(fmt) ? formatValue.ToString(fmt, CultureInfo.CurrentCulture).Trim() : formatValue.ToString().Trim()); } } return(value.ToString().Trim()); } // Always return empty strings, not nulls return(string.Empty); }
private static StringValue GetCommentStyle(XLCell cell) { var c = cell.Comment; var sb = new StringBuilder("position:absolute; "); sb.Append("visibility:"); sb.Append(c.Visible ? "visible" : "hidden"); sb.Append(";"); sb.Append("width:"); sb.Append(Math.Round(c.Style.Size.Width * 7.5, 2).ToString(CultureInfo.InvariantCulture)); sb.Append("pt;"); sb.Append("height:"); sb.Append(Math.Round(c.Style.Size.Height, 2).ToString(CultureInfo.InvariantCulture)); sb.Append("pt;"); sb.Append("z-index:"); sb.Append(c.ZOrder.ToString()); return sb.ToString(); }
public XLComment(XLCell cell, XLFormattedText <IXLComment> defaultComment, IXLFontBase defaultFont, IXLDrawingStyle style) : base(defaultComment, defaultFont) { Initialize(cell, style); }
private static Vml.Stroke GetStroke(XLCell c) { var lineDash = c.Comment.Style.ColorsAndLines.LineDash; var stroke = new Vml.Stroke { LineStyle = c.Comment.Style.ColorsAndLines.LineStyle.ToOpenXml(), DashStyle = lineDash == XLDashStyle.RoundDot || lineDash == XLDashStyle.SquareDot ? "shortDot" : lineDash.ToString().ToCamel() }; if (lineDash == XLDashStyle.RoundDot) stroke.EndCap = Vml.StrokeEndCapValues.Round; if (c.Comment.Style.ColorsAndLines.LineTransparency < 1) stroke.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.LineTransparency), 2).ToString( CultureInfo.InvariantCulture); return stroke; }
private static Anchor GetAnchor(XLCell cell) { var c = cell.Comment; var cWidth = c.Style.Size.Width; var fcNumber = c.Position.Column - 1; var fcOffset = Convert.ToInt32(c.Position.ColumnOffset * 7.5); var widthFromColumns = cell.Worksheet.Column(c.Position.Column).Width - c.Position.ColumnOffset; var lastCell = cell.CellRight(c.Position.Column - cell.Address.ColumnNumber); while (widthFromColumns <= cWidth) { lastCell = lastCell.CellRight(); widthFromColumns += lastCell.WorksheetColumn().Width; } var lcNumber = lastCell.WorksheetColumn().ColumnNumber() - 1; var lcOffset = Convert.ToInt32((lastCell.WorksheetColumn().Width - (widthFromColumns - cWidth)) * 7.5); var cHeight = c.Style.Size.Height; //c.Style.Size.Height * 72.0; var frNumber = c.Position.Row - 1; var frOffset = Convert.ToInt32(c.Position.RowOffset); var heightFromRows = cell.Worksheet.Row(c.Position.Row).Height - c.Position.RowOffset; lastCell = cell.CellBelow(c.Position.Row - cell.Address.RowNumber); while (heightFromRows <= cHeight) { lastCell = lastCell.CellBelow(); heightFromRows += lastCell.WorksheetRow().Height; } var lrNumber = lastCell.WorksheetRow().RowNumber() - 1; var lrOffset = Convert.ToInt32(lastCell.WorksheetRow().Height - (heightFromRows - cHeight)); return new Anchor { Text = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", fcNumber, fcOffset, frNumber, frOffset, lcNumber, lcOffset, lrNumber, lrOffset ) }; }
//=========================================================================================== #region ** Save a C1FlexGrid into an XLSheet private void SaveSheet(C1FlexGrid flex, XLSheet sheet, bool fixedCells) { // account for fixed cells int frows = flex.Rows.Fixed; int fcols = flex.Cols.Fixed; if (fixedCells) { frows = fcols = 0; } // copy dimensions int lastRow = flex.Rows.Count - frows - 1; int lastCol = flex.Cols.Count - fcols - 1; if (lastRow < 0 || lastCol < 0) { return; } XLCell cell = sheet[lastRow, lastCol]; // set default properties sheet.Book.DefaultFont = flex.Font; sheet.DefaultRowHeight = C1XLBook.PixelsToTwips(flex.Rows.DefaultSize); sheet.DefaultColumnWidth = C1XLBook.PixelsToTwips(flex.Cols.DefaultSize); // prepare to convert styles _styles = new Hashtable(); // set row/column properties for (int r = frows; r < flex.Rows.Count; r++) { // size/visibility Row fr = flex.Rows[r]; XLRow xr = sheet.Rows[r - frows]; if (fr.Height >= 0) { xr.Height = C1XLBook.PixelsToTwips(fr.Height); } xr.Visible = fr.Visible; // style XLStyle xs = StyleFromFlex(fr.Style); if (xs != null) { xr.Style = xs; } } for (int c = fcols; c < flex.Cols.Count; c++) { // size/visibility Column fc = flex.Cols[c]; XLColumn xc = sheet.Columns[c - fcols]; if (fc.Width >= 0) { xc.Width = C1XLBook.PixelsToTwips(fc.Width); } xc.Visible = fc.Visible; // style XLStyle xs = StyleFromFlex(fc.Style); if (xs != null) { xc.Style = xs; } } // load cells for (int r = frows; r < flex.Rows.Count; r++) { for (int c = fcols; c < flex.Cols.Count; c++) { // get cell cell = sheet[r - frows, c - fcols]; // apply content cell.Value = flex[r, c]; // apply style XLStyle xs = StyleFromFlex(flex.GetCellStyle(r, c)); if (xs != null) { cell.Style = xs; } } } }
// VML Shape for Comment private static Vml.Shape GenerateShape(XLCell c, string shapeTypeId) { var rowNumber = c.Address.RowNumber; var columnNumber = c.Address.ColumnNumber; var shapeId = String.Format("_x0000_s{0}", c.Comment.ShapeId); // Unique per cell (workbook?), e.g.: "_x0000_s1026" var anchor = GetAnchor(c); var textBox = GetTextBox(c.Comment.Style); var fill = new Vml.Fill {Color2 = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2)}; if (c.Comment.Style.ColorsAndLines.FillTransparency < 1) fill.Opacity = Math.Round(Convert.ToDouble(c.Comment.Style.ColorsAndLines.FillTransparency), 2).ToString( CultureInfo.InvariantCulture); var stroke = GetStroke(c); var shape = new Vml.Shape( fill, stroke, new Vml.Shadow {On = true, Color = "black", Obscured = true}, new Vml.Path {ConnectionPointType = ConnectValues.None}, textBox, new ClientData( new MoveWithCells(c.Comment.Style.Properties.Positioning == XLDrawingAnchor.Absolute ? "True" : "False"), // Counterintuitive new ResizeWithCells(c.Comment.Style.Properties.Positioning == XLDrawingAnchor.MoveAndSizeWithCells ? "False" : "True"), // Counterintuitive anchor, new HorizontalTextAlignment(c.Comment.Style.Alignment.Horizontal.ToString().ToCamel()), new Vml.Spreadsheet.VerticalTextAlignment(c.Comment.Style.Alignment.Vertical.ToString().ToCamel()), new AutoFill("False"), new CommentRowTarget {Text = (rowNumber - 1).ToString()}, new CommentColumnTarget {Text = (columnNumber - 1).ToString()}, new Locked(c.Comment.Style.Protection.Locked ? "True" : "False"), new LockText(c.Comment.Style.Protection.LockText ? "True" : "False"), new Visible(c.Comment.Visible ? "True" : "False") ) {ObjectType = ObjectValues.Note} ) { Id = shapeId, Type = "#" + shapeTypeId, Style = GetCommentStyle(c), FillColor = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2), StrokeColor = "#" + c.Comment.Style.ColorsAndLines.LineColor.Color.ToHex().Substring(2), StrokeWeight = String.Format("{0}pt", c.Comment.Style.ColorsAndLines.LineWeight), InsetMode = c.Comment.Style.Margins.Automatic ? InsetMarginValues.Auto : InsetMarginValues.Custom }; if (!XLHelper.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText)) shape.Alternate = c.Comment.Style.Web.AlternateText; return shape; }
public XLComment(XLCell cell, IXLFontBase defaultFont = null, int?shapeId = null) : base(defaultFont ?? XLFont.DefaultCommentFont) { Initialize(cell, shapeId: shapeId); }
// load sheet into grid private void LoadSheet(C1FlexGrid flex, XLSheet sheet, bool fixedCells) { // account for fixed cells int frows = flex.Rows.Fixed; int fcols = flex.Cols.Fixed; // copy dimensions flex.Rows.Count = sheet.Rows.Count + frows; flex.Cols.Count = sheet.Columns.Count + fcols; // initialize fixed cells if (fixedCells && frows > 0 && fcols > 0) { flex.Styles.Fixed.TextAlign = TextAlignEnum.CenterCenter; for (int r = 1; r < flex.Rows.Count; r++) { flex[r, 0] = r; } for (int c = 1; c < flex.Cols.Count; c++) { // string hdr = string.Format("{0}", (char)('A' + c - 1)); // flex[0, c] = hdr; flex[0, c] = c; } } // set default properties flex.Font = sheet.Book.DefaultFont; flex.Rows.DefaultSize = C1XLBook.TwipsToPixels(sheet.DefaultRowHeight); flex.Cols.DefaultSize = C1XLBook.TwipsToPixels(sheet.DefaultColumnWidth); // prepare to convert styles _styles = new Hashtable(); // set row/column properties for (int r = 0; r < sheet.Rows.Count; r++) { // size/visibility Row fr = flex.Rows[r + frows]; XLRow xr = sheet.Rows[r]; if (xr.Height >= 0) { fr.Height = C1XLBook.TwipsToPixels(xr.Height); } fr.Visible = xr.Visible; // style CellStyle cs = StyleFromExcel(flex, xr.Style); if (cs != null) { //cs.DefinedElements &= ~StyleElementFlags.TextAlign; // << need to fix the grid fr.Style = cs; } } for (int c = 0; c < sheet.Columns.Count; c++) { // size/visibility Column fc = flex.Cols[c + fcols]; XLColumn xc = sheet.Columns[c]; if (xc.Width >= 0) { fc.Width = C1XLBook.TwipsToPixels(xc.Width); } fc.Visible = xc.Visible; // style CellStyle cs = StyleFromExcel(flex, xc.Style); if (cs != null) { //cs.DefinedElements &= ~StyleElementFlags.TextAlign; // << need to fix the grid fc.Style = cs; } } // load cells for (int r = 0; r < sheet.Rows.Count; r++) { for (int c = 0; c < sheet.Columns.Count; c++) { // get cell XLCell cell = sheet.GetCell(r, c); if (cell == null) { continue; } // apply content flex[r + frows, c + fcols] = cell.Value; // apply style CellStyle cs = StyleFromExcel(flex, cell.Style); if (cs != null) { flex.SetCellStyle(r + frows, c + fcols, cs); } } } }
public XLComment(XLCell cell, IXLFontBase defaultFont) : base(defaultFont) { Initialize(cell); }
public static void Set_Cell(XLCell cell, String st, XLStyle style) { cell.Value = st; cell.Style = style; }
public XLComment(XLCell cell, String text, IXLFontBase defaultFont) : base(text, defaultFont) { Initialize(cell); }
public static void Set_CellNumber(XLCell cell, object obj, XLStyle style) { cell.Value = obj; cell.Style = style; }
public XLComment(XLCell cell, XLFormattedText <IXLComment> defaultComment, IXLFontBase defaultFont) : base(defaultComment, defaultFont) { Initialize(cell); }
public void Add(XLSheetPoint sheetPoint, XLCell cell) { Add(sheetPoint.Row, sheetPoint.Column, cell); }
private void Initialize(XLCell cell, IXLDrawingStyle style = null, int?shapeId = null) { style = style ?? XLDrawingStyle.DefaultCommentStyle; shapeId = shapeId ?? cell.Worksheet.Workbook.ShapeIdManager.GetNext(); Author = cell.Worksheet.Author; Container = this; Anchor = XLDrawingAnchor.MoveAndSizeWithCells; Style = new XLDrawingStyle(); Int32 previousRowNumber = cell.Address.RowNumber; Double previousRowOffset = 0; if (previousRowNumber > 1) { previousRowNumber--; if (cell.Worksheet.Internals.RowsCollection.TryGetValue(previousRowNumber, out XLRow previousRow)) { previousRowOffset = Math.Max(0, previousRow.Height - 7); } else { previousRowOffset = Math.Max(0, cell.Worksheet.RowHeight - 7); } } Position = new XLDrawingPosition { Column = cell.Address.ColumnNumber + 1, ColumnOffset = 2, Row = previousRowNumber, RowOffset = previousRowOffset }; ZOrder = cell.Worksheet.ZOrder++; Style .Margins.SetLeft(style.Margins.Left) .Margins.SetRight(style.Margins.Right) .Margins.SetTop(style.Margins.Top) .Margins.SetBottom(style.Margins.Bottom) .Margins.SetAutomatic(style.Margins.Automatic) .Size.SetHeight(style.Size.Height) .Size.SetWidth(style.Size.Width) .ColorsAndLines.SetLineColor(style.ColorsAndLines.LineColor) .ColorsAndLines.SetFillColor(style.ColorsAndLines.FillColor) .ColorsAndLines.SetLineDash(style.ColorsAndLines.LineDash) .ColorsAndLines.SetLineStyle(style.ColorsAndLines.LineStyle) .ColorsAndLines.SetLineWeight(style.ColorsAndLines.LineWeight) .ColorsAndLines.SetFillTransparency(style.ColorsAndLines.FillTransparency) .ColorsAndLines.SetLineTransparency(style.ColorsAndLines.LineTransparency) .Alignment.SetHorizontal(style.Alignment.Horizontal) .Alignment.SetVertical(style.Alignment.Vertical) .Alignment.SetDirection(style.Alignment.Direction) .Alignment.SetOrientation(style.Alignment.Orientation) .Alignment.SetAutomaticSize(style.Alignment.AutomaticSize) .Properties.SetPositioning(style.Properties.Positioning) .Protection.SetLocked(style.Protection.Locked) .Protection.SetLockText(style.Protection.LockText); _cell = cell; ShapeId = shapeId.Value; }
internal CloseXMLExcelCell(XLCell obj) { _cell = obj; }
private void TransposeRange(int squareSide) { var cellsToInsert = new Dictionary<XLSheetPoint, XLCell>(); var cellsToDelete = new List<XLSheetPoint>(); var rngToTranspose = Worksheet.Range( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.FirstAddress.RowNumber + squareSide - 1, RangeAddress.FirstAddress.ColumnNumber + squareSide - 1); Int32 roCount = rngToTranspose.RowCount(); Int32 coCount = rngToTranspose.ColumnCount(); for (Int32 ro = 1; ro <= roCount; ro++) { for (Int32 co = 1; co <= coCount; co++) { var oldCell = rngToTranspose.Cell(ro, co); var newKey = rngToTranspose.Cell(co, ro).Address; // new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); newCell.CopyFrom(oldCell, true); cellsToInsert.Add(new XLSheetPoint(newKey.RowNumber, newKey.ColumnNumber), newCell); cellsToDelete.Add(new XLSheetPoint(oldCell.Address.RowNumber, oldCell.Address.ColumnNumber)); } } cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c)); cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); }