Question
Uddipto Banerji on Mon, 17 Jul 2017 19:44:45
Hi All,
As I have an issue with the colouring of sheet cell on condition using DocumentFormat.Spreadsheet.Now I can create the colour for the 1<sup style="color:#2f5496;">st</sup> time but I need to open the same excel sheet and based on the condition I need to colour a particular cell. E.g. if cell D which can be yellow or green.e.g.
|
Name |
IpAddress |
Region |
Details |
|
Switch |
10.1.1.1 |
EMEA |
Based on Condition the cell would be coloured |
|
Switch |
10.1.1.2 |
AMER |
Based on Condition the cell would be coloured |
|
Switch |
10.1.1.3 |
APAC |
Based on Condition the cell would be coloured |
|
Switch |
10.1.1.2 |
AMER |
Based on Condition the cell would be coloured |
|
Switch |
10.1.1.2 |
AMER |
Based on Condition the cell would be coloured |
I’m writing each row at a time. I want to open existing excel sheet which I have created and would pass the value i.e. _valuecolour and based its value it would colour the particular cell along with the alignment of the other cells.I need to know how can I achieve this. Any help would be really appreciated.I have used the code below.
public static bool InsertRowExcel(string filepath, string _sb, string _switchinput){
int i = 0; int k = 0; bool bl = false; string[] _arr = _switchinput.Split(',');
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filepath, true)){
//Get workbookpart
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorkbookStylesPart stylePart = workbookPart.WorkbookStylesPart;
Row row = new Row();
//then access to the worksheet part
IEnumerable<WorksheetPart> worksheetPart = workbookPart.WorksheetParts;
foreach (WorksheetPart WSP in worksheetPart){//find sheet data
IEnumerable<SheetData> sheetData = WSP.Worksheet.Elements<SheetData>();
// Iterate through every sheet inside Excel sheet
foreach (SheetData SD in sheetData){
IEnumerable<Row> rows = SD.Elements<Row>(); // Get the row IEnumerator
i = (rows.Count()); // Will give you the count of rows
do{ row = new Row();
row.Append(
ConstructCell(_arr[0], CellValues.String),
ConstructCell(_arr[1], CellValues.String),
ConstructCell(_arr[2], CellValues.String),
ConstructCell(_sb, CellValues.String,2U)
);}while (k > 0);
/* HERE I NEED TO ADD STYLE TO THE CELL on condition. */
}} bl = true; }
return bl;}
private static Stylesheet GenerateStylesheet(bool _valuecolour){
Stylesheet styleSheet = null;
Fills fills = new Fills();
if (_valuecolour)
{
fills = new Fills(
new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } })
{ PatternType = PatternValues.Solid })
);}
else{
fills = new Fills(
new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "008000" } })
{ PatternType = PatternValues.Solid }) // Index 2 - body
);}
CellFormats cellFormats = new CellFormats(
new CellFormat(), // default
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 0, BorderId = 1, ApplyAlignment = true },
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true }){ FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true }); // header
styleSheet = new Stylesheet(fills, cellFormats);
return styleSheet;
}
private static Cell ConstructCell(string value, CellValues dataType,uint styleIndex = 0){return new Cell(){
CellValue = new CellValue(value),
DataType = new EnumValue<CellValues>(dataType),
StyleIndex = styleIndex};}
Replies
Chenchen Li on Tue, 18 Jul 2017 09:25:18
Hello,
There is no need to get the sheetData collection because there is only one sheet data element.
So you could use:
SheetData sheetData = WSP.Worksheet.Elements<SheetData>().First(); IEnumerable<Row> rows = sheetData.Elements<Row>(); // Get the row IEnumerator
The code doesn't append the new row in the sheetdata.
Please use
do
{
row = new Row();
row.Append(
ConstructCell(_arr[0], CellValues.String),
ConstructCell(_arr[1], CellValues.String),
ConstructCell(_arr[2], CellValues.String),
ConstructCell(_sb, CellValues.String,2U)
);
sheetData.Append(row);
} while (k > 0);
For your code to create the stylesheet, do you want to replace the original stylesheet into yours?
If you want to generate your own stylesheet, I suggest you create whole elements:
I would suggest you keep the old stylesheet and append new elements instead.
Or you could refer to https://blogs.msdn.microsoft.com/chrisquon/2009/11/30/stylizing-your-excel-worksheets-with-open-xml-2-0/
Regards,
Celeste
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to
MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Uddipto Banerji on Tue, 18 Jul 2017 10:51:13
Thanks Celeste, I have followed the blog of Chris and I created the style sheet but unfortunately its not working.
I want to colour specific cell based on the string empty or not for Details column in excel cell where by I have used the snippet below. I could able append text but without colour in the Details columns.
/* Here is the calling condition */
if (_sb != string.Empty)
{
;
}
else
{
;
//stylePart.Stylesheet = GenerateStylesheet(false);
//stylePart.Stylesheet.Save();
}
private static Stylesheet GenerateStylesheet(bool _valuecolour){
Stylesheet styleSheet = null;
Fills fills = new Fills();
if (_valuecolour)
{
fills = new Fills(
new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } })
{ PatternType = PatternValues.Solid })
);}
else{
fills = new Fills(
new Fill(new PatternFill() { PatternType = PatternValues.None }), // Index 0 - default
new Fill(new PatternFill() { PatternType = PatternValues.Gray125 }), // Index 1 - default
new Fill(new PatternFill(new ForegroundColor { Rgb = new HexBinaryValue() { Value = "008000" } })
{ PatternType = PatternValues.Solid }) // Index 2 - body
);}
CellFormats cellFormats = new CellFormats(
new CellFormat(), // default
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 0, BorderId = 1, ApplyAlignment = true },
new CellFormat(new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true }){ FontId = 1, FillId = 2, BorderId = 1, ApplyFill = true }); // header
styleSheet = new Stylesheet(fills, cellFormats);
return styleSheet;
}
Chenchen Li on Wed, 19 Jul 2017 09:52:00
Hello,
If this is your current stylesheet, you don't create any Font and Border but you append these attribute in cellformat. Please add default Font and Border.
008000 is invalid, please use FF008000.
So you could test the following stylesheet.
public static Stylesheet GenerateStylesheetDefault(bool _valuecolour)
{
Stylesheet stylesheet1 = new Stylesheet();
Fonts fonts = new Fonts(
new Font(
new FontSize() { Val = 11D },
new Color() { Theme = (UInt32Value)1U },
new FontName() { Val = "Calibri" },
new FontFamilyNumbering() { Val = 2 },
new FontScheme() { Val = FontSchemeValues.Minor }
)
);
Borders borders = new Borders(
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder())
);
Fills fills = new Fills();
if (_valuecolour)
{
fills = new Fills(
new Fill(
new PatternFill() { PatternType = PatternValues.None }),
new Fill(
new PatternFill() { PatternType = PatternValues.Gray125 }),
new Fill(
new PatternFill(
new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } })
{ PatternType = PatternValues.Solid })
);
}
else
{
fills = new Fills(
new Fill(
new PatternFill() { PatternType = PatternValues.None }),
new Fill(
new PatternFill() { PatternType = PatternValues.Gray125 }),
new Fill(
new PatternFill(
new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FF008000" } })
{ PatternType = PatternValues.Solid })
);
}
CellStyleFormats cellStyleFormats = new CellStyleFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }
);
CellFormats cellFormats = new CellFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U },
new CellFormat(
new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
new CellFormat(
new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }
);
stylesheet1.Append(fonts);
stylesheet1.Append(fills);
stylesheet1.Append(borders);
stylesheet1.Append(cellStyleFormats);
stylesheet1.Append(cellFormats);
return stylesheet1;
}
Besides, I would suggest you download Open XML SDK Tool from https://www.microsoft.com/en-us/download/details.aspx?id=30425. Then you could check if your document is valid.
Regards,
Celeste
Uddipto Banerji on Tue, 25 Jul 2017 16:03:43
I have modified the code but the colouring is not restricted to individual cell but the entire Details column. It is applying to previous cells. Could you please suggest where I did mistake.
public static bool InsertRowExcel(string filepath, string _sb, string _switchinput)
{
int i = 0;
string[] _arr = _switchinput.Split(',');
bool bl = false;
int k = 0;
try
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filepath, true))
{
//Get workbookpart
WorkbookPart workbookPart = myDoc.WorkbookPart;
Row row = new Row();
//then access to the worksheet part
IEnumerable<WorksheetPart> worksheetPart = workbookPart.WorksheetParts;
WorkbookStylesPart stylesheet = workbookPart.WorkbookStylesPart;
foreach (WorksheetPart WSP in worksheetPart)
{
//find sheet data
IEnumerable<SheetData> sheetData = WSP.Worksheet.Elements<SheetData>();
// Iterate through every sheet inside Excel sheet
foreach (SheetData SD in sheetData)
{
IEnumerable<Row> rows = SD.Elements<Row>(); // Get the row IEnumerator
i = (rows.Count()); // Will give you the count of rows
do
{
row = new Row();
row.Append(
ConstructCell(_arr[0], CellValues.String, 1U),
ConstructCell(_arr[1], CellValues.String, 1U),
ConstructCell(_arr[2], CellValues.String, 1U),
ConstructCell(_sb, CellValues.String, 2U)
);
SD.AppendChild(row);
}
while (k > 0);
break;
}
break;
}
if (_sb != string.Empty)
stylesheet.Stylesheet = GenerateStylesheetDefault(true);
else
stylesheet.Stylesheet = GenerateStylesheetDefault(false);
stylesheet.Stylesheet.Save();
bl = true;
}
}
catch (Exception ex)
{
bl = false;
//LogWrite(ex.StackTrace.ToString());
throw ex;
}
return bl;
}
public static Stylesheet GenerateStylesheetDefault(bool _valuecolour)
{
Stylesheet stylesheet1 = new Stylesheet();
Fonts fonts = new Fonts(
new Font(
new FontSize() { Val = 11D },
new Color() { Theme = (UInt32Value)1U },
new FontName() { Val = "Calibri" },
new FontFamilyNumbering() { Val = 2 },
new FontScheme() { Val = FontSchemeValues.Minor }
)
);
Borders borders = new Borders(
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder())
);
Fills fills = new Fills();
if (_valuecolour)
{
fills = new Fills(
new Fill(
new PatternFill() { PatternType = PatternValues.None }),
new Fill(
new PatternFill() { PatternType = PatternValues.Gray125 }),
new Fill(
new PatternFill(
new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } })
{ PatternType = PatternValues.Solid })
);
}
else
{
fills = new Fills(
new Fill(
new PatternFill() { PatternType = PatternValues.None }),
new Fill(
new PatternFill() { PatternType = PatternValues.Gray125 }),
new Fill(
new PatternFill(
new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FF008000" } })
{ PatternType = PatternValues.Solid })
);
}
CellStyleFormats cellStyleFormats = new CellStyleFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }
);
CellFormats cellFormats = new CellFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U },
new CellFormat(
new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
new CellFormat(
new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true }
);
stylesheet1.Append(fonts);
stylesheet1.Append(fills);
stylesheet1.Append(borders);
stylesheet1.Append(cellStyleFormats);
stylesheet1.Append(cellFormats);
return stylesheet1;
}
Uddipto Banerji on Tue, 25 Jul 2017 16:10:51
It is either entire yellow or entire green for the details columns.
Chenchen Li on Wed, 26 Jul 2017 05:49:28
Hello,
Please test the code below:
string fileName = @"D:\Desktop\T1.xlsx";
if (InsertRowExcel(fileName, "test", "test1,test2,test3"))
{
Console.WriteLine("Done");
}
if (InsertRowExcel(fileName, "", "test4,test5,test6"))
{
Console.WriteLine("Done");
}
public static bool InsertRowExcel(string filepath, string _sb, string _switchinput)
{
int i = 0; int k = 0; bool bl = false; string[] _arr = _switchinput.Split(',');
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filepath, true))
{
//Get workbookpart
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorkbookStylesPart stylePart = workbookPart.WorkbookStylesPart;
Row row = new Row();
stylePart.Stylesheet = GenerateStylesheetDefault();
stylePart.Stylesheet.Save();
//then access to the worksheet part
IEnumerable<WorksheetPart> worksheetPart = workbookPart.WorksheetParts;
foreach (WorksheetPart WSP in worksheetPart)
{//find sheet data
SheetData sheetData = WSP.Worksheet.Elements<SheetData>().First();
IEnumerable<Row> rows = sheetData.Elements<Row>(); // Get the row IEnumerator
i = (rows.Count()); // Will give you the count of rows
/* HERE I NEED TO ADD STYLE TO THE CELL on condition. */
do
{
row = new Row();
row.Append(
ConstructCell(_arr[0], CellValues.String),
ConstructCell(_arr[1], CellValues.String),
ConstructCell(_arr[2], CellValues.String)
);
if (_sb != string.Empty)
{
row.Append(
ConstructCell(_sb, CellValues.String, 2U));
}
else
{
row.Append(
ConstructCell(_sb, CellValues.String, 3U));
}
sheetData.Append(row);
} while (k > 0);
}
bl = true;
}
return bl;
}
public static Stylesheet GenerateStylesheetDefault()
{
Stylesheet stylesheet1 = new Stylesheet();
Fonts fonts = new Fonts(
new Font(
new FontSize() { Val = 11D },
new Color() { Theme = (UInt32Value)1U },
new FontName() { Val = "Calibri" },
new FontFamilyNumbering() { Val = 2 },
new FontScheme() { Val = FontSchemeValues.Minor }
)
);
Borders borders = new Borders(
new Border(
new LeftBorder(),
new RightBorder(),
new TopBorder(),
new BottomBorder(),
new DiagonalBorder())
);
Fills fills = new Fills();
fills = new Fills(
new Fill(
new PatternFill() { PatternType = PatternValues.None }), //0
new Fill(
new PatternFill() { PatternType = PatternValues.Gray125 }),//1
new Fill(
new PatternFill(
new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FFFFFF00" } }) //2 -yellow
{ PatternType = PatternValues.Solid }),
new Fill(
new PatternFill(
new ForegroundColor { Rgb = new HexBinaryValue() { Value = "FF008000" } })// 3 -green
{ PatternType = PatternValues.Solid })
);
CellStyleFormats cellStyleFormats = new CellStyleFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U }
);
CellFormats cellFormats = new CellFormats(
new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U },
new CellFormat(
new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 0, BorderId = 0, ApplyAlignment = true },
new CellFormat(
new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 2, BorderId = 0, ApplyFill = true },
new CellFormat(
new Alignment() { Horizontal = HorizontalAlignmentValues.Left, Vertical = VerticalAlignmentValues.Top, WrapText = true })
{ FontId = 0, FillId = 3, BorderId = 0, ApplyFill = true }
);
stylesheet1.Append(fonts);
stylesheet1.Append(fills);
stylesheet1.Append(borders);
stylesheet1.Append(cellStyleFormats);
stylesheet1.Append(cellFormats);
return stylesheet1;
}
Result:
Regards,
Celeste
Uddipto Banerji on Wed, 26 Jul 2017 06:30:28
Thank you so much Celeste.
Chenchen Li on Wed, 26 Jul 2017 07:12:57
Thank you so much Celeste.
Hello,
If the issue has been resolved, please make as answer to close the thread. If you have any other question about the issue, please let me know.
If you have new issues, please post new threads.
Regards,
Celeste