Hi All,
I am new to DocumentFormat.OpenXML SDK 2.0 version, since past two weeks i was trying to get proper data format into the excel sheet using code behind language as C#.Net. I was able to pass the data format by specifying the cell format type as follows:
private
void
GenerateWorkbookStylesPart1Content(WorkbookStylesPart workbookStylesPart1)
{
Stylesheet stylesheet1 = new
Stylesheet();
Fonts fonts1 = new
Fonts() { Count = (UInt32Value)0U };
Font font1 = new
Font();
FontSize fontSize1 = new
FontSize() { Val = 11D };
Color color1 = new
Color() { Theme = (UInt32Value)1U };
FontName fontName1 = new
FontName() { Val = "Calibri"
};
FontFamilyNumbering fontFamilyNumbering1 = new
FontFamilyNumbering() { Val = 2 };
FontScheme fontScheme1 = new
FontScheme() { Val = FontSchemeValues.Minor };
font1.Append(fontSize1);
font1.Append(color1);
font1.Append(fontName1);
font1.Append(fontFamilyNumbering1);
font1.Append(fontScheme1);
fonts1.Append(font1);
Fills fills1 = new
Fills() { Count = (UInt32Value)0U };
Fill fill1 = new
Fill();
PatternFill patternFill1 = new
PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
Fill fill2 = new
Fill();
PatternFill patternFill2 = new
PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
fills1.Append(fill1);
fills1.Append(fill2);
Borders borders1 = new
Borders() { Count = (UInt32Value)0U };
Border border1 = new
Border();
LeftBorder leftBorder1 = new
LeftBorder();
RightBorder rightBorder1 = new
RightBorder();
TopBorder topBorder1 = new
TopBorder();
BottomBorder bottomBorder1 = new
BottomBorder();
DiagonalBorder diagonalBorder1 = new
DiagonalBorder();
border1.Append(leftBorder1);
border1.Append(rightBorder1);
border1.Append(topBorder1);
border1.Append(bottomBorder1);
border1.Append(diagonalBorder1);
borders1.Append(border1);
CellStyleFormats cellStyleFormats1 = new
CellStyleFormats() { Count = (UInt32Value)0U };
CellFormat cellFormat1 = new
CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U };
cellStyleFormats1.Append(cellFormat1);
CellFormats cellFormats1 = new
CellFormats() { Count = (UInt32Value)6U };
CellFormat cellFormat2 = new
CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
cellFormats1.Append(cellFormat2);
//extra code for cell formatting by Ravindranath
CellFormat cellFormat3 = new
CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)1U, FormatId = (UInt32Value)0U, ApplyFont = true
, ApplyFill = true
, ApplyBorder = true
, ApplyAlignment = true
};
Alignment alignment1 = new
Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Center };
cellFormat3.Append(alignment1);
CellFormat cellFormat4 = new
CellFormat() { NumberFormatId = (UInt32Value)3U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
CellFormat cellFormat5 = new
CellFormat() { NumberFormatId = (UInt32Value)10U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
CellFormat cellFormat6 = new
CellFormat() { NumberFormatId = (UInt32Value)14U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyNumberFormat = true
};
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
CellStyles cellStyles1 = new
CellStyles() { Count = (UInt32Value)0U };
CellStyle cellStyle1 = new
CellStyle() { Name = "Normal"
, FormatId = (UInt32Value)0U, BuiltinId = (UInt32Value)0U };
cellStyles1.Append(cellStyle1);
DifferentialFormats differentialFormats1 = new
DifferentialFormats() { Count = (UInt32Value)0U };
TableStyles tableStyles1 = new
TableStyles() { Count = (UInt32Value)0U, DefaultTableStyle = "TableStyleMedium9"
, DefaultPivotStyle = "PivotStyleLight16"
};
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
workbookStylesPart1.Stylesheet = stylesheet1;
}
Also i did some minor change in other file i.e I have a method called WriteDataByCell which is as follows
public
void
WriteDataByCell(IList<ExcelCellData> excelData, string
sheetName)
{
// Open the document for editing.
using
(SpreadsheetDocument document = SpreadsheetDocument.Open(docName, true
))
{
IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName);
if
(sheets.Count() == 0)
{
// The specified worksheet does not exist.
return
;
}
WorksheetPart worksheetPart = GetWorksheetPartByName(document, sheetName);
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
SharedStringTablePart shareStringPart;
if
(document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)
{
shareStringPart = document.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();
}
else
{
shareStringPart = document.WorkbookPart.AddNewPart<SharedStringTablePart>();
}
if
(excelData != null
&& excelData.Count > 0)
{
UInt32Value cellStyleIndex = 0;
foreach
(ExcelCellData cellData in
excelData)
{
// int index = InsertSharedStringItem(cellData.Text, shareStringPart);
Cell cellRange = InsertCellInWorksheet(GetColumnName(cellData.FirstCell), GetRowIndex(cellData.FirstCell), worksheetPart);
if
(cellData.FirstCell.ToString() != cellData.LastCell.ToString())
{
if
(cellData.Style.MergerCells)
{
//Create merge cells
CreateMergeCells(worksheet, cellData.FirstCell, cellData.LastCell);
}
}
if
(cellData.Style != null
)
{
//cell formatting indexes
UInt32Value cellFillIndex = 0;
UInt32Value cellBorderIndex = 0;
UInt32Value cellFontIndex = 0;
styleSheet = document.WorkbookPart.WorkbookStylesPart.Stylesheet;
if
(cellData.Style.BackgroundColor != null
&& cellData.Style.BackgroundColor != System.Drawing.Color.White)
{
//set the background color style
cellFillIndex = CreateFill(styleSheet, cellData.Style.BackgroundColor);
}
if
(cellData.Style.Borders != null
)
{
//set border color
cellBorderIndex = CreateBorders(styleSheet, cellData.Style.Borders.Weight, cellData.Style.Borders.Color);
}
//set font properties
bool
isBold = cellData.Style.Font.IsBold;
bool
isItalic = cellData.Style.Font.IsItalic;
bool
isStrikeThrough = cellData.Style.Font.Strikethrough;
System.Drawing.Color fontColor;
HorizontalAlignmentValues horizontalAlignment = cellData.Style.HorizontalAlignment;
VerticalAlignmentValues verticalAlignment = cellData.Style.VerticalAlignment;
if
(cellData.Style.Font.Color != null
)
{
fontColor = cellData.Style.Font.Color;
}
else
{
fontColor = System.Drawing.Color.Black;
}
string
fontName = string
.Empty;
if
(!string
.IsNullOrEmpty(cellData.Style.Font.Name))
{
fontName = cellData.Style.Font.Name;
}
int
fontSize = cellData.Style.Font.Size;
// Create a custom stylesheet
//build the formatted header style
cellFontIndex = CreateFont(styleSheet, fontName, fontSize, isBold, fontColor, isItalic, isStrikeThrough);
//Create the cell styles
cellStyleIndex = CreateCellFormat(styleSheet, cellFontIndex, cellFillIndex, cellBorderIndex, horizontalAlignment, verticalAlignment, cellData.Style.WrapText);
//cellRange.StyleIndex = cellStyleIndex;
//Currently these properties are not implemented
//cellRange.Font.Subscript = cellData.Style.Font.Subscript;
//cellRange.Font.Superscript = cellData.Style.Font.Superscript;
//cellRange.WrapText = cellData.Style.WrapText;
}
//int index = InsertSharedStringItem(cellData.Text, shareStringPart);
//cellRange.CellValue = new CellValue(index.ToString());
//cellRange.DataType = new EnumValue<CellValues>(CellValues.SharedString);
//string cellCValue = GetCellValue(worksheetPart, shareStringPart, GetColumnName(cellData.FirstCell), GetRowIndex(cellData.FirstCell).ToString());
// Code which i modified to accept data formats in the excel sheet...
<br/>
int
intValue;
//string cellValue = "";
double
doubleValue;
DateTime dateValue;
int
index;
if
(double
.TryParse(cellData.Text.ToString(), out
doubleValue))
{
cellRange.StyleIndex = 2U;
//cellRange.StyleIndex = NumberFormatId = (UInt32Value)0U;
cellRange.CellValue = new
CellValue(doubleValue.ToString());
}
else
if
(int
.TryParse(cellData.Text.ToString(), out
intValue))
{
cellRange.CellValue = new
CellValue(intValue.ToString());
}
else
if
(DateTime.TryParse(cellData.Text.ToString(), out
dateValue))
{
//cellRange.StyleIndex = 4U;
//DateTime d = new DateTime(1900, 1, 1);
//d = d.AddDays(Int32.Parse(dateValue.ToString()) - 1);
//cellValue = d.ToShortDateString();
cellRange.CellValue = new
CellValue(dateValue.ToString());
}
else
if
(cellData.Text.EndsWith("%"
))
{
string
data = cellData.Text.Substring(0, cellData.Text.Length-1);
double
d;
Double.TryParse(data, out
d);
if
(cellData.Text == "0.00%"
|| cellData.Text == "0.00 %"
|| cellData.Text == "0%"
|| cellData.Text == "0 %"
|| d != 0)
{
d = d / 100;
cellRange.StyleIndex = 3U;
cellRange.CellValue = new
CellValue(d.ToString());
}
else
{
index = InsertSharedStringItem(cellData.Text, shareStringPart);
cellRange.StyleIndex = 1U;
cellRange.CellValue = new
CellValue(index.ToString());
cellRange.DataType = new
EnumValue<CellValues>(CellValues.SharedString);
}
}
else
{
index = InsertSharedStringItem(cellData.Text, shareStringPart);
cellRange.StyleIndex = 1U;
cellRange.CellValue = new
CellValue(index.ToString());
cellRange.DataType = new
EnumValue<CellValues>(CellValues.SharedString);
}
//cellRange.DataType = new EnumValue<CellValues>(CellValues.Number);
}
}
worksheet.Save();
}
}
The above block of code is written to accept the data type and write it to excel sheet.
My problem is data gets written correctly in the excel sheet but all the styles and fonts used in the sheet are messed up totally. i am not able to get to the solution why they are getting messed up. The code blocks above are according to the flow. In the
second code block u can see that i am calling a foreach loop inside which i am specifying the styles for each cell data, i have two functions namely, CreateFont and CreateCellFormat over here all the things get messed up. i am pasting the two functions below.
First CreateFont
private
UInt32Value CreateFont(Stylesheet styleSheet, string
fontName, Nullable<double
> fontSize, bool
isBold, System.Drawing.Color foreColor, bool
isItalic, bool
isStrikeThrough)
{
Font font = new
Font();
if
(isItalic)
{
Italic italicFont = new
Italic();
font.Italic = italicFont;
}
if
(!string
.IsNullOrEmpty(fontName))
{
FontName name = new
FontName() { Val = fontName };
font.Append(name);
}
if
(fontSize.HasValue)
{
DocumentFormat.OpenXml.Spreadsheet.FontSize size = new
DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = fontSize.Value };
font.Append(size);
}
if
(isBold)
{
Bold bold = new
Bold();
font.Append(bold);
}
if
(isStrikeThrough)
{
Strike strikeThrough = new
Strike();
font.Append(strikeThrough);
}
if
(foreColor != null
)
{
Color color = new
Color()
{
Rgb = new
HexBinaryValue()
{
Value = System.Drawing.ColorTranslator.ToHtml(System.Drawing.Color.FromArgb(foreColor.A, foreColor.R, foreColor.G, foreColor.B)).Replace("#"
, ""
)
}
};
font.Append(color);
}
styleSheet.Fonts.Append(font);
UInt32Value result = styleSheet.Fonts.Count;
styleSheet.Fonts.Count++;
return
result;
}
The second function CreateCellFormat
private UInt32Value CreateCellFormat(Stylesheet styleSheet, UInt32Value fontIndex, UInt32Value fillIndex, UInt32Value borders, HorizontalAlignmentValues horizontalAlignment, VerticalAlignmentValues verticalAlignment, bool wrapText)
{
CellFormat cellFormat = new CellFormat(new Alignment() { Horizontal = horizontalAlignment, Vertical = verticalAlignment, WrapText = wrapText });//, WrapText=true
if (fontIndex != null)
cellFormat.FontId = fontIndex;
if (fillIndex != null)
cellFormat.FillId = fillIndex;
if (borders != null)
cellFormat.BorderId = borders;
styleSheet.CellFormats.Append(cellFormat);
UInt32Value result = styleSheet.CellFormats.Count;
styleSheet.CellFormats.Count++;
return result;
}
this two functions are messing up my styles in the excel sheet.
The conclusion i came to is that the values of unsigned int are getting incremented continuously in the foreach loop of 2nd code block which i pasted. I also created a replica of the output file in which i inserted the values manually and compared
both the files with the OpneXML production tool, there i see major differences in the formats and fonts.
For example my original file shows
Fonts fonts1 = new Fonts(){ Count = (UInt32Value)7U };
My created file shows
Fonts fonts1 = new Fonts(){ Count = (UInt32Value)155U }; this is due to the loop of foreach in the second code block not able to figure out how to keep it to minimum.
Then my data formats are not consistent since i have hard coded the styleindex in the second code block, i dont know how to specify proper styleIndex to each and every cell.
I am also doubt full of Datatypes being written in the cell.
Please help me asap, any help would be appreciated.
Thanks
Ravindranath
