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));
}
internal CloseXMLExcelCell(XLCell obj)
{
_cell = obj;
}
public void Add(XLSheetPoint sheetPoint, XLCell cell)
{
Add(sheetPoint.Row, sheetPoint.Column, cell);
}
