1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413
| using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace TestWriteWithOpenXML
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string filename = string.Format("{0}BulkResult_{1}{2:00}{3:00}{4:00}{5:00}{6:00}{7:000}.xlsx", System.IO.Path.GetTempPath(), DateTime.UtcNow.Year, DateTime.UtcNow.Month, DateTime.UtcNow.Day, DateTime.UtcNow.Hour, DateTime.UtcNow.Minute, DateTime.UtcNow.Second, DateTime.UtcNow.Millisecond);
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
workbookpart.Workbook.Save();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart1 = workbookpart.AddNewPart<WorksheetPart>();
worksheetPart1.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
string worksheetDivision = "Division";
Sheet sheet1 = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart1), SheetId = 1, Name = worksheetDivision };
sheets.Append(sheet1);
workbookpart.Workbook.Save();
//Handling SharedStringTable
WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
if (stringTablePart == null)
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
stringTablePart.SharedStringTable = new SharedStringTable();
//Handling Styles
// Stylesheet
WorkbookStylesPart workbookStylesPart = spreadsheetDocument.WorkbookPart.AddNewPart<WorkbookStylesPart>();
spreadsheetDocument.WorkbookPart.WorkbookStylesPart.Stylesheet = createStyleSheet();
workbookStylesPart.Stylesheet.Save();
//INSERT MY DATA HERE
wbPart = spreadsheetDocument.WorkbookPart;
UInt32Value normalFontIndex = 0;
UInt32Value boldFontIndex = 7;
UpdateValue(wbPart, worksheetDivision, "Z1", "toto", boldFontIndex, true);
UpdateValue(wbPart, worksheetDivision, "AA1", "titi", normalFontIndex, true);
spreadsheetDocument.Close();
System.Diagnostics.Process.Start(filename);
}
private Stylesheet createStyleSheet()
{
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
stylesheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
stylesheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");
DocumentFormat.OpenXml.Spreadsheet.Fonts fonts1 = new DocumentFormat.OpenXml.Spreadsheet.Fonts() { Count = (UInt32Value)1U, KnownFonts = true };
//Normal Font
DocumentFormat.OpenXml.Spreadsheet.Font font1 = new DocumentFormat.OpenXml.Spreadsheet.Font();
DocumentFormat.OpenXml.Spreadsheet.FontSize fontSize1 = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11D };
DocumentFormat.OpenXml.Spreadsheet.Color color1 = new DocumentFormat.OpenXml.Spreadsheet.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);
//Bold Font
DocumentFormat.OpenXml.Spreadsheet.Font bFont = new DocumentFormat.OpenXml.Spreadsheet.Font();
DocumentFormat.OpenXml.Spreadsheet.FontSize bfontSize = new DocumentFormat.OpenXml.Spreadsheet.FontSize() { Val = 11D };
DocumentFormat.OpenXml.Spreadsheet.Color bcolor = new DocumentFormat.OpenXml.Spreadsheet.Color() { Theme = (UInt32Value)1U };
FontName bfontName = new FontName() { Val = "Calibri" };
FontFamilyNumbering bfontFamilyNumbering = new FontFamilyNumbering() { Val = 2 };
FontScheme bfontScheme = new FontScheme() { Val = FontSchemeValues.Minor };
Bold bFontBold = new Bold();
bFont.Append(bfontSize);
bFont.Append(bcolor);
bFont.Append(bfontName);
bFont.Append(bfontFamilyNumbering);
bFont.Append(bfontScheme);
bFont.Append(bFontBold);
fonts1.Append(bFont);
Fills fills1 = new Fills() { Count = (UInt32Value)5U };
// FillId = 0
Fill fill1 = new Fill();
PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.None };
fill1.Append(patternFill1);
// FillId = 1
Fill fill2 = new Fill();
PatternFill patternFill2 = new PatternFill() { PatternType = PatternValues.Gray125 };
fill2.Append(patternFill2);
// FillId = 2,RED
Fill fill3 = new Fill();
PatternFill patternFill3 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = "FFB6C1" };
BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill3.Append(foregroundColor1);
patternFill3.Append(backgroundColor1);
fill3.Append(patternFill3);
// FillId = 3,GREEN
Fill fill4 = new Fill();
PatternFill patternFill4 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor2 = new ForegroundColor() { Rgb = "90EE90" };
BackgroundColor backgroundColor2 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill4.Append(foregroundColor2);
patternFill4.Append(backgroundColor2);
fill4.Append(patternFill4);
// FillId = 4,YELLO
Fill fill5 = new Fill();
PatternFill patternFill5 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor3 = new ForegroundColor() { Rgb = "FFFF00" };
BackgroundColor backgroundColor3 = new BackgroundColor() { Indexed = (UInt32Value)64U };
patternFill5.Append(foregroundColor3);
patternFill5.Append(backgroundColor3);
fill5.Append(patternFill5);
// FillId = 5,RED and BOLD Text
Fill fill6 = new Fill();
PatternFill patternFill6 = new PatternFill() { PatternType = PatternValues.Solid };
ForegroundColor foregroundColor4 = new ForegroundColor() { Rgb = "FFB6C1" };
BackgroundColor backgroundColor4 = new BackgroundColor() { Indexed = (UInt32Value)64U };
Bold bold1 = new Bold();
patternFill6.Append(foregroundColor4);
patternFill6.Append(backgroundColor4);
fill6.Append(patternFill6);
fills1.Append(fill1);
fills1.Append(fill2);
fills1.Append(fill3);
fills1.Append(fill4);
fills1.Append(fill5);
fills1.Append(fill6);
Borders borders1 = new Borders() { Count = (UInt32Value)1U };
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)1U };
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)4U };
CellFormat cellFormat2 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
CellFormat cellFormat3 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat4 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat5 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)0U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat6 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)2U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat7 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)3U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat8 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)4U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U, ApplyFill = true };
CellFormat cellFormat9 = new CellFormat() { NumberFormatId = (UInt32Value)0U, FontId = (UInt32Value)1U, FillId = (UInt32Value)0U, BorderId = (UInt32Value)0U, FormatId = (UInt32Value)0U };
cellFormats1.Append(cellFormat2);
cellFormats1.Append(cellFormat3);
cellFormats1.Append(cellFormat4);
cellFormats1.Append(cellFormat5);
cellFormats1.Append(cellFormat6);
cellFormats1.Append(cellFormat7);
cellFormats1.Append(cellFormat8);
cellFormats1.Append(cellFormat9);
CellStyles cellStyles1 = new CellStyles() { Count = (UInt32Value)1U };
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 = "TableStyleMedium2", DefaultPivotStyle = "PivotStyleMedium9" };
stylesheet1.Append(fonts1);
stylesheet1.Append(fills1);
stylesheet1.Append(borders1);
stylesheet1.Append(cellStyleFormats1);
stylesheet1.Append(cellFormats1);
stylesheet1.Append(cellStyles1);
stylesheet1.Append(differentialFormats1);
stylesheet1.Append(tableStyles1);
return stylesheet1;
}
// Given a Worksheet and an address (like "AZ254"), either return a cell reference, or
// create the cell reference and return it.
private Cell InsertCellInWorksheet(Worksheet ws, string addressName)
{
SheetData sheetData = ws.GetFirstChild<SheetData>();
Cell cell = null;
UInt32 rowNumber = GetRowIndex(addressName);
Row row = GetRow(sheetData, rowNumber);
// If the cell you need already exists, return it.
// If there is not a cell with the specified column name, insert one.
Cell refCell = row.Elements<Cell>().
Where(c => c.CellReference.Value == addressName).FirstOrDefault();
if (refCell != null)
{
cell = refCell;
}
else
{
cell = CreateCell(row, addressName);
}
return cell;
}
private Cell CreateCell(Row row, String address)
{
Cell cellResult;
Cell refCell = null;
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, address, true) > 0)
{
refCell = cell;
break;
}
}
cellResult = new Cell();
cellResult.CellReference = address;
row.InsertBefore(cellResult, refCell);
return cellResult;
}
private Row GetRow(SheetData wsData, UInt32 rowIndex)
{
var row = wsData.Elements<Row>().
Where(r => r.RowIndex.Value == rowIndex).FirstOrDefault();
if (row == null)
{
row = new Row();
row.RowIndex = rowIndex;
wsData.Append(row);
}
return row;
}
private UInt32 GetRowIndex(string address)
{
string rowPart;
UInt32 l;
UInt32 result = 0;
for (int i = 0; i < address.Length; i++)
{
if (UInt32.TryParse(address.Substring(i, 1), out l))
{
rowPart = address.Substring(i, address.Length - i);
if (UInt32.TryParse(rowPart, out l))
{
result = l;
break;
}
}
}
return result;
}
public bool UpdateValue(WorkbookPart wbPart, string sheetName, string addressName, string value, UInt32Value styleIndex, bool isString)
{
value = value != null ? value : "";
// Assume failure.
bool updated = false;
Sheet sheet = wbPart.Workbook.Descendants<Sheet>().Where((s) => s.Name == sheetName).FirstOrDefault();
if (sheet != null)
{
Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
Cell cell = InsertCellInWorksheet(ws, addressName);
if (isString)
{
// Either retrieve the index of an existing string,
// or insert the string into the shared string table
// and get the index of the new item.
int stringIndex = InsertSharedStringItem(wbPart, value);
cell.CellValue = new CellValue(stringIndex.ToString());
cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(value);
cell.DataType = new EnumValue<CellValues>(CellValues.Number);
}
if (styleIndex > 0)
cell.StyleIndex = styleIndex;
// Save the worksheet.
ws.Save();
updated = true;
}
return updated;
}
// Given the main workbook part, and a text value, insert the text into the shared
// string table. Create the table if necessary. If the value already exists, return
// its index. If it doesn't exist, insert it and return its new index.
private int InsertSharedStringItem(WorkbookPart wbPart, string value)
{
value = value != null ? value : "";
int index = 0;
bool found = false;
var stringTablePart = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something's wrong.
// Just return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTablePart == null)
{
// Create it.
stringTablePart = wbPart.AddNewPart<SharedStringTablePart>();
}
var stringTable = stringTablePart.SharedStringTable;
if (stringTable == null)
{
stringTable = new SharedStringTable();
}
// Iterate through all the items in the SharedStringTable. If the text already exists, return its index.
foreach (SharedStringItem item in stringTable.Elements<SharedStringItem>())
{
if (item.InnerText == value)
{
found = true;
break;
}
index += 1;
}
if (!found)
{
stringTable.AppendChild(new SharedStringItem(new Text(value)));
stringTable.Save();
}
return index;
}
// Used to force a recalc of cells containing formulas. The
// CellValue has a cached value of the evaluated formula. This
// will prevent Excel from recalculating the cell even if
// calculation is set to automatic.
private bool RemoveCellValue(WorkbookPart wbPart, string sheetName, string addressName)
{
bool returnValue = false;
Sheet sheet = wbPart.Workbook.Descendants<Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
if (sheet != null)
{
Worksheet ws = ((WorksheetPart)(wbPart.GetPartById(sheet.Id))).Worksheet;
Cell cell = InsertCellInWorksheet(ws, addressName);
// If there is a cell value, remove it to force a recalc
// on this cell.
if (cell.CellValue != null)
{
cell.CellValue.Remove();
}
// Save the worksheet.
ws.Save();
returnValue = true;
}
return returnValue;
}
}
} |
Partager