/* * Sunil Shenoi, 8-25-2008 * * Assuming cell has a valid string vlaue, find the font record for a given characterIndex * into the stringValue of the cell */ public static FONT getFontForCharacter(Cell cell, UInt16 charIndex) { FONT f = null; int index = cell.Style.RichTextFormat.CharIndexes.BinarySearch(charIndex); List<UInt16> fontIndexList = cell.Style.RichTextFormat.FontIndexes; if (index >= 0) { // found the object, return the font record f = getFontRecord(cell.SharedResource, fontIndexList[index]); //Console.WriteLine("for charIndex={0}, fontIndex={1})", charIndex, fontIndexList[index]); //Console.WriteLine("Object: {0} found at [{1}]", o, index); } else { // would have been inserted before the returned value, so insert just before it if (~index == 0) { //f = getFontRecord(sheet,fontIndexList[0]); //Console.WriteLine("for charIndex={0}, fontIndex=CELL", charIndex); } else { f = getFontRecord(cell.SharedResource, fontIndexList[(~index) - 1]); //Console.WriteLine("for charIndex={0}, fontIndex={1})", charIndex, fontIndexList[(~index) - 1]); } //Console.WriteLine("Object: {0} not found. " // + "Next larger object found at [{1}].", o, ~index); } return f; }
public Cell CreateCell(int row, int col, object value, int XFindex) { XF xf = SharedResource.ExtendedFormats[XFindex]; CellFormat foramt = SharedResource.CellFormats[xf.FormatIndex]; Cell cell = new Cell(value, foramt); cell.SharedResource = this.SharedResource; cell.Style = CreateStyleFromXF(xf); this[row, col] = cell; return cell; }
public Cell CreateCell(int row, int col, object value, int XFindex) { XF xf = SharedResource.ExtendedFormats[XFindex]; var cell = new Cell(value) { SharedResource = SharedResource, CellFormat = CreateCellFormatFromXF(xf) }; this[row, col] = cell; return cell; }
private static void processSheet(DataTable table, Worksheet ws) { // Make the header. foreach (DataColumn dataColumn in table.Columns) { var headerCell = new Cell(string.IsNullOrEmpty(dataColumn.Caption) ? dataColumn.ColumnName : dataColumn.Caption, CellFormat.General); makeHeaderCell(headerCell); ws.Cells[0, dataColumn.Ordinal] = headerCell; } // -- // Make the body. var rowIndex = 1; foreach (DataRow sourceRow in table.Rows) { foreach (DataColumn sourceColumn in table.Columns) { var sourceValue = sourceRow[sourceColumn]; if (sourceValue == DBNull.Value) sourceValue = null; var destinationCell = new Cell(sourceValue); ws.Cells[rowIndex, sourceColumn.Ordinal] = destinationCell; if (!string.IsNullOrEmpty(sourceColumn.Namespace) && sourceColumn.Namespace == ReadOnlyFlag) { makeCellReadOnly(destinationCell); } } rowIndex++; } }
public void SetCell(int colIndex, Cell cell) { FirstColIndex = Math.Min(FirstColIndex, colIndex); LastColIndex = Math.Max(LastColIndex, colIndex); Cells[colIndex] = cell; }
private static CellValue EncodeCell(Cell cell, SharedResource sharedResource) { object value = cell.Value; if (value is int || value is short || value is uint || value is byte) { RK rk = new RK(); rk.Value = (uint)(Convert.ToInt32(value) << 2 | 2); return rk; } else if (value is decimal) { if (Math.Abs((decimal)value) <= (decimal)5368709.11) { RK rk = new RK(); rk.Value = (uint)((int)((decimal)value * 100) << 2 | 3); // integer and mul return rk; } else { NUMBER number = new NUMBER(); number.Value = (double)(decimal)value; return number; } } else if (value is double) { //RK rk = new RK(); //Int64 data = BitConverter.DoubleToInt64Bits((double)value); //rk.Value = (uint)(data >> 32) & 0xFFFFFFFC; //return rk; NUMBER number = new NUMBER(); number.Value = (double)value; return number; } else if (value is string) { LABELSST label = new LABELSST(); label.SSTIndex = sharedResource.GetSSTIndex((string)value); return label; } else if (value is DateTime) { NUMBER number = new NUMBER(); number.Value = sharedResource.EncodeDateTime((DateTime)value); return number; } else if (value is bool) { BOOLERR boolerr = new BOOLERR(); boolerr.ValueType = 0; boolerr.Value = Convert.ToByte((bool)value); return boolerr; } else if (value is ErrorCode) { BOOLERR boolerr = new BOOLERR(); boolerr.ValueType = 1; boolerr.Value = ((ErrorCode)value).Code; return boolerr; } else { throw new Exception("Invalid cell value."); } }
private static void makeHeaderCell(Cell cell) { //var colorHeader = ExcelHelper.SafeExcelColors.Green; ////var colorOddRows = ColorTranslator.FromHtml( @"#DBE5F1" ); //var colorLines = ExcelHelper.SafeExcelColors.Gray; //var colorHeaderLine = ExcelHelper.SafeExcelColors.Black; //cell.Style.ForegroundColor = colorHeader; //cell.Style.BackgroundColor = colorHeader; //cell.Style.Pattern = BackgroundType.Solid; //cell.Style.Font.Color = ExcelHelper.SafeExcelColors.White; //cell.Style.Font.IsBold = true; //cell.Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Thin; //cell.Style.Borders[BorderType.BottomBorder].Color = colorHeaderLine; //cell.Style.Borders[BorderType.TopBorder].LineStyle = CellBorderType.Thin; //cell.Style.Borders[BorderType.TopBorder].Color = colorLines; }
private static void makeCellReadOnly(Cell cell) { //cell.Style.IsLocked = true; }
public void SetCell(int colIndex, Cell cell) { this.FirstColIndex = Math.Min(this.FirstColIndex, colIndex); this.LastColIndex = Math.Max(this.LastColIndex, colIndex); this.Cells[colIndex] = cell; }
static void Main(string[] args) { var underTest = new Workbook(); var sheet = new Worksheet("Sheet 1"); var styleA = new CellFormat(); styleA.SetBackgroundColor(ExcelColor.Red); styleA.Border.DiagonalUp = true; styleA.Border.DiagonalDown = true; styleA.Border.DiagonalStyle = CellBorderStyle.Thin; var styleB = new CellFormat { Pattern = { Style = PatternStyle.LightDown, ForegroundColor = ExcelColor.Blue } }; var styleC = new CellFormat(); styleC.SetBackgroundColor(ExcelColor.Silver); styleC.Border = CellBorder.MediumBox; styleC.Font.Bold = true; for (var i = 0; i < 100; i++) { var cellA = new Cell("Abcde"); var cellB = new Cell(1234); var cellC = new Cell(string.Format("This is row {0:000}", i)); if (i%2 == 0) { cellA.CellFormat = styleA; cellB.CellFormat = styleB; cellC.CellFormat = styleC; } else { cellB.CellFormat.TextControl.RotationStyle = RotationStyle.CounterClockwise; cellB.CellFormat.TextControl.TextRotation = 45; cellC.VerticalAlignment = VerticalAlignStyle.Centered; cellC.CellFormat.Font.Name = "Times New Roman"; cellC.CellFormat.Font.Family = FontFamilyType.Roman; cellC.CellFormat.Font.Height = 240; } sheet.Cells[i, 0] = cellA; sheet.Cells[i, 1] = cellB; sheet.Cells[i, 2] = cellC; sheet.Cells.ColumnWidth[2] = 256 * 15; } underTest.Worksheets.Add(sheet); var filename = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "test.xls"); if (File.Exists(filename)) { File.Delete(filename); } underTest.Save(filename); }