/*
* 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);
}