I have created an excel with two sheets and the excel is getting created successfully, but when i open it it ask to repair the excel. I have used open xml librar
EntityCollection CMSInput = service.RetrieveMultiple(new FetchExpression(fetchCMSInputQuery));
tracing.Trace("Start Plugin time:" DateTime.Now.ToString());
MemoryStream ms = new MemoryStream();
SpreadsheetDocument xl = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = null;
try
{
wsp = wbp.AddNewPart();
}
catch (Exception ex)
{
throw new InvalidPluginExecutionException(ex.ToString());
}
Workbook wb = new Workbook();
FileVersion fv = new FileVersion
{
ApplicationName = "Microsoft Office Excel"
};
tracing.Trace("Worksheeet Created");
Worksheet ws = new Worksheet();
//First cell
SheetData sd = new SheetData();
Columns columns = new Columns();
columns.Append(CreateColumnData(1, 3, 23.5703125));
columns.Append(CreateColumnData(4, 5, 14));
columns.Append(CreateColumnData(6, 20, 18));
ws.Append(columns);
UInt32Value row_count = 1;
Row row1 = new Row() { RowIndex = 1 };
//Row rowEngagement = new Row() { RowIndex = 1 };
//CreateHeaderEngagement(service, rowEngagement, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, engagementName);
CreateHeader(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv);
int MainCountFlag = 1;
int RowIndexCountFlag = 2;
EntityCollection filtered = new EntityCollection();
foreach (var engagement in CMSInput.Entities)
{
try
{
engagementName = "ABC";
var ss = new byte[] { };
row_count = Convert.ToUInt32(RowIndexCountFlag);// Convert.ToUInt32(MainCountFlag 1);
row1 = new Row() { RowIndex = Convert.ToUInt32(RowIndexCountFlag) };
Cell c2 = new Cell();
c2 = new Cell
{
CellReference = "A" row_count,
DataType = CellValues.String,
StyleIndex = 1,
CellValue = new CellValue("123")
};
row1.Append(c2);
tracing.Trace("first2");
Cell c3 = new Cell();
c3 = new Cell
{
CellReference = "B" row_count,
DataType = CellValues.String,
StyleIndex = 1,
CellValue = new CellValue("245")
};
row1.Append(c3);
Cell c10 = new Cell();
c10 = new Cell
{
CellReference = "C" row_count,
DataType = CellValues.String,
StyleIndex = 1,
CellValue = new CellValue("245")
};
row1.Append(c10);
ss = new byte[] { };
if (MainCountFlag == CMSInput.Entities.Count)
{
ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, false, ws, wsp, sd);
//ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, true);
}
else
{
ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, false, ws, wsp, sd);
//ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, false);
}
RowIndexCountFlag ;
}
catch (Exception ex)
{
tracing.Trace("Exception ss" ex.Message);
}
MainCountFlag ;
tracing.Trace("MainCountFlag ss" MainCountFlag);
//if (MainCountFlag != 0)
}
WorksheetPart wsp1 = null;
try
{
wsp1 = wbp.AddNewPart();
}
catch (Exception ex)
{
throw new InvalidPluginExecutionException(ex.ToString());
}
Worksheet ws1 = new Worksheet();
//First cell
SheetData sd1 = new SheetData();
row1 = new Row() { RowIndex = 1 };
CreateHeader(service, row1, 1, ms, wb, xl, sd1, ws1, wbp, wsp1, fv);
MainCountFlag = 1;
RowIndexCountFlag = 2;
foreach (var engagement in CMSInput.Entities)
{
try
{
engagementName = "ABC";
var ss = new byte[] { };
row_count = Convert.ToUInt32(RowIndexCountFlag);// Convert.ToUInt32(MainCountFlag 1);
row1 = new Row() { RowIndex = Convert.ToUInt32(RowIndexCountFlag) };
Cell c2 = new Cell();
c2 = new Cell
{
CellReference = "A" row_count,
DataType = CellValues.String,
StyleIndex = 1,
CellValue = new CellValue("123")
};
row1.Append(c2);
tracing.Trace("first2");
Cell c3 = new Cell();
c3 = new Cell
{
CellReference = "B" row_count,
DataType = CellValues.String,
StyleIndex = 1,
CellValue = new CellValue("245")
};
row1.Append(c3);
Cell c10 = new Cell();
c10 = new Cell
{
CellReference = "C" row_count,
DataType = CellValues.String,
StyleIndex = 1,
CellValue = new CellValue("245")
};
row1.Append(c10);
ss = new byte[] { };
if (MainCountFlag == CMSInput.Entities.Count)
{
ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, true, ws1, wsp1, sd1);
//ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, true);
}
else
{
ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd1, ws1, wbp, wsp1, fv, false, ws1, wsp1, sd1);
//ss = CreateExcelDoc(service, row1, 1, ms, wb, xl, sd, ws, wbp, wsp, fv, false);
}
RowIndexCountFlag ;
}
catch (Exception ex)
{
tracing.Trace("Exception ss" ex.Message);
}
MainCountFlag ;
tracing.Trace("MainCountFlag ss" MainCountFlag);
//if (MainCountFlag != 0)
}
public byte[] CreateExcelDoc(IOrganizationService service, Row r1, UInt32Value rowcount, MemoryStream ms,
Workbook wb, SpreadsheetDocument xl,
SheetData sd, Worksheet ws, WorkbookPart wbp, WorksheetPart wsp, FileVersion fv, bool Upload,
Worksheet ws1, WorksheetPart wsp1, SheetData sd1)
{
sd.Append(r1);
byte[] dt = null;
if (Upload)
{
ws.Append(sd);
ws1.Append(sd1);
wsp.Worksheet = ws;
wsp1.Worksheet = ws1;
//New Changes
WorkbookStylesPart wbsp = wbp.AddNewPart();
//// add styles to sheet
Stylesheet stylesheet1 = new Stylesheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
wbsp.Stylesheet = stylesheet1;//CreateStylesheet();
wbsp.Stylesheet.Save();
//
wsp.Worksheet.Save();
wsp1.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = engagementName,
SheetId = 1,
Id = wbp.GetIdOfPart(wsp)
};
Sheet sheet2 = new Sheet
{
Name = "Input",
SheetId = 2,
Id = wbp.GetIdOfPart(wsp1)
};
sheets.Append(sheet);
sheets.Append(sheet2);
wb.Append(fv);
wb.Append(sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
dt = ms.ToArray();
Entity note = new
Entity("annotation");
note["subject"] = "Summary Report";
note["filename"] = "SummaryReport.xlsx";
note["documentbody"] = Convert.ToBase64String(dt);
note["objectid"] = new EntityReference("cr493_exportdatatoexcel", SummaryId);
var attachmentId = service.Create(note);
}
//}
return dt;
}
public void CreateHeader(IOrganizationService service, Row r1, UInt32Value rowcount, MemoryStream ms, Workbook wb, SpreadsheetDocument xl, SheetData sd, Worksheet ws, WorkbookPart wbp, WorksheetPart wsp, FileVersion fv)
{
Cell c1 = new Cell
{
CellReference = "A2",
DataType = CellValues.String,
CellValue = new CellValue("Request type"),
StyleIndex = (UInt32Value)3U
};
r1.Append(c1);
// Second cell
Cell c2 = new Cell
{
CellReference = "B2",
DataType = CellValues.String,
CellValue = new CellValue("Confirming Party"),
StyleIndex = (UInt32Value)3U
};
r1.Append(c2);
// Third cell
Cell c3 = new Cell
{
CellReference = "C2",
DataType = CellValues.String,
CellValue = new CellValue("Confirming Party Contact"),
StyleIndex = (UInt32Value)3U
};
r1.Append(c3);
// Fourth cell
sd.Append(r1);
}y.
