// Given a column name, a row index, and a WorksheetPart, inserts a cell into the worksheet.
// If the cell already exists, returns it.
private Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// If the worksheet does not contain a row with the specified row index, insert one.
DocumentFormat.OpenXml.Spreadsheet.Row row;
if (sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex };
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements<Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return row.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
Cell refCell = null;
foreach (Cell cell in row.Elements<Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
Cell newCell = new Cell() { CellReference = cellReference };
row.InsertBefore(newCell, refCell);
worksheet.Save();
return newCell;
}
}
public Excel.Row CloneRow(Excel.Row cloning, int r)
{
var rez = (Excel.Row)cloning.Clone();
UpdateRowIndex(rez, r);
return(rez);
}
protected void PopulateExcelTable(DataTable dataTable, excel.SheetData sheetData)
{
excel.Row excelRow = new excel.Row();
foreach (DataColumn dataColumn in dataTable.Columns)
{
excelRow.Append(new excel.Cell()
{
CellValue = new excel.CellValue(GetColumnName(dataColumn))
});
}
sheetData.AppendChild(excelRow);
foreach (DataRow row in dataTable.Rows)
{
excelRow = new excel.Row();
foreach (object cellItem in row.ItemArray)
{
excelRow.Append(new excel.Cell()
{
CellValue = new excel.CellValue(cellItem.ToString())
});
}
sheetData.AppendChild(excelRow);
}
}
private void SetActivityLogRows(SheetData sheetData, List <ActivityLogEntryData> activityLogEntry)
{
Row row;
foreach (var activityLog in activityLogEntry)
{
row = new Row();
row.Append(
CreateCell(activityLog.From, CellValues.String, 1),
CreateCell(activityLog.To, CellValues.String, 1),
CreateCell(activityLog.ElapsedTime, CellValues.String, 1),
CreateCell(activityLog.CumulativeTime, CellValues.String, 1),
CreateCell(activityLog.Depth, CellValues.String, 1),
CreateCell(activityLog.MudWeight, CellValues.String, 1),
CreateCell(activityLog.Activity, CellValues.String, 1),
CreateCell(activityLog.Milestone, CellValues.String, 1),
CreateCell(activityLog.Unplanned_Planned, CellValues.String, 1),
CreateCell(activityLog.HasNpt, CellValues.String, 1),
CreateCell(activityLog.NptReference, CellValues.String, 1),
CreateCell(activityLog.Description, CellValues.String, 1)
);
sheetData.Append(row);
}
}
/// <summary>
/// Set height of row in points (pixels with 72dpi)
/// </summary>
/// <param name="row">row instance which height is going to change</param>
/// <param name="height">height of row in points (pixels with 72dpi)</param>
/// <returns></returns>
public static x.Row SetHeightInPoints(this x.Row row, double height)
{
if (row == null)
{
return(null);
}
var ws = row.ParentOfType <x.Worksheet>();
if (ws == null)
{
throw new InvalidDocumentStructureException();
}
var sheetFormatProps = ws.SheetFormatProperties;
if (sheetFormatProps == null)
{
sheetFormatProps = new x.SheetFormatProperties();
ws.Insert(sheetFormatProps).AfterOneOf(typeof(x.Dimension), typeof(x.SheetView));
}
if (sheetFormatProps.DefaultRowHeight == null || !sheetFormatProps.DefaultRowHeight.HasValue)
{
sheetFormatProps.DefaultRowHeight = 18;
}
row.Height = height;
row.CustomHeight = true;
return(row);
}
private DocumentFormat.OpenXml.Spreadsheet.Row MakeXLSXInfoRow(string title, string value, uint rowindex)
{
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowindex
};
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "A" + rowindex.ToString()
};
row.InsertBefore(newCell, refCell);
string cellValue = title + ": ";
if (!String.IsNullOrEmpty(value))
{
cellValue += value;
}
else
{
cellValue += "N/A";
}
newCell.CellValue = new CellValue(cellValue);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = 3;
return(row);
}
private void ExportDatatableToExcel(DataTable table, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
//new
public void ExportDSToExcel(DataSet ds, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
uint sheetId = 1;
foreach (DataTable table in ds.Tables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
sheetId =
sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = table.TableName };
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List<String> columns = new List<string>();
foreach (DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}
static void ReadRow(Sheet sheet, Excel.Row importRow, Dictionary <string, string> sharedStringTable, Dictionary <int, CellFormat> cellFormats)
{
Row row = sheet.Row((int)(uint)importRow.RowIndex - 1);
foreach (var importCell in importRow.Elements <Excel.Cell>())
{
ReadCell(row, importCell, sharedStringTable, cellFormats);
}
}
private void SetEmptyRow(SheetData sheetData)
{
Row row = new Row();
row.Append(
CreateCell("", CellValues.String)
);
sheetData.Append(row);
}
public Excel.Row GetRow(OpenXmlCompositeElement sheetData, int r, bool check, Excel.Row cloning)
{
Excel.Row rez = null;
if (check)
{
foreach (OpenXmlCompositeElement row in sheetData)
{
rez = row as Excel.Row;
if (rez != null && rez.RowIndex != null && rez.RowIndex.Value == r)
{
break;
}
}
}
if (rez == null || rez.RowIndex.Value != r)
{
if (cloning != null)
{
Excel.Row parent = null;
if (!check)
{
foreach (OpenXmlCompositeElement row in sheetData)
{
rez = row as Excel.Row;
if (rez != null && rez.RowIndex != null && rez.RowIndex.Value == r)
{
parent = rez;
break;
}
}
}
rez = CloneRow(cloning, r);
if (parent != null)
{
parent.RowIndex = parent.RowIndex.Value + 1;
sheetData.InsertBefore <Excel.Row>(rez, parent);
}
else
{
sheetData.Append(rez);
}
}
else
{
rez = new Excel.Row()
{
RowIndex = (uint)r
};
sheetData.Append(rez);
}
}
return(rez);
}
private static DocumentFormat.OpenXml.Spreadsheet.Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
{
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild <SheetData>();
string cellReference = columnName + rowIndex;
DocumentFormat.OpenXml.Spreadsheet.Row lastRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().LastOrDefault();
// If the worksheet does not contain a row with the specified row index, insert one.
DocumentFormat.OpenXml.Spreadsheet.Row row;
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(r => r.RowIndex == rowIndex).First();
//set auto height -- don't know how this line is worked
sheetData.InsertAfter(new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = (lastRow.RowIndex + 1)
}, lastRow);
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
}
// If there is not a cell with the specified column name, insert one.
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
// Cells must be in sequential order according to CellReference. Determine where to insert the new cell.
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell cell in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
worksheet.Save();
return(newCell);
}
}
private void SetReportBudgetHeader(SheetData sheetData, List <string> reportBudgetHeaders)
{
Row row = new Row();
foreach (var header in reportBudgetHeaders)
{
row.Append(CreateCell(header, CellValues.String, 2));
}
sheetData.Append(row);
}
private void SetActivityLogHeader(SheetData sheetData, List <string> activityLogHeaders)
{
Row row = new Row();
List <CellDfn> cells = new List <CellDfn>();
foreach (var header in activityLogHeaders)
{
row.Append(CreateCell(header, CellValues.String, 2));
}
sheetData.Append(row);
}
// Create Excel Sheet
public void UpdateCells(string docName, IList <ExcelSheetInput> excelSheetInputs)
{
using (SpreadsheetDocument createdDocument = SpreadsheetDocument.Create(docName, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart createdWorkbookPart = createdDocument.AddWorkbookPart();
createdWorkbookPart.Workbook = new spd.Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart createdWorksheetPart = createdWorkbookPart.AddNewPart <WorksheetPart>();
createdWorksheetPart.Worksheet = new spd.Worksheet();
spd.Sheets createdSheets = createdWorkbookPart.Workbook.AppendChild(new spd.Sheets());
spd.Sheet createdSheet = new spd.Sheet()
{
Id = createdWorkbookPart.GetIdOfPart(createdWorksheetPart),
SheetId = 1,
Name = "Sheet 1"
};
createdSheets.Append(createdSheet);
createdWorkbookPart.Workbook.Save();
spd.SheetData sheetData = createdWorksheetPart.Worksheet.AppendChild(new spd.SheetData());
var rowsCount = excelSheetInputs.Last().RowNumber;
for (var rowsCounter = 1; rowsCounter <= rowsCount; rowsCounter++)
{
var rowItems = excelSheetInputs.Where(e => e.RowNumber == rowsCounter).ToList();
// Constructing header
spd.Row createdRow = new spd.Row();
foreach (ColumnName c in Enum.GetValues(typeof(ColumnName)))
{
try
{
var cellValue = rowItems.First(e => e.ColumnName.Equals(c.ToString()));
createdRow.Append(ConstructCell(cellValue.Text, spd.CellValues.String));
}
catch (Exception)
{
createdRow.Append(ConstructCell("", spd.CellValues.String));
}
}
sheetData.AppendChild(createdRow);
}
createdWorkbookPart.Workbook.Save();
}
}
private static OpenXmlSpread.Cell GetCell(DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet,
string columnName, uint rowIndex)
{
OpenXmlSpread.Row row = GetRow(worksheet, rowIndex);
if (row == null)
{
return(null);
}
return(row.Elements <OpenXmlSpread.Cell>()
.Where(c => string.Compare(c.CellReference.Value, columnName + rowIndex, true) == 0).First());
}
private static void GenerateExcel(string excelFilePath, DataTable dataTable)
{
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(excelFilePath, true))
{
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable <Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
Sheet sheet = sheets.SingleOrDefault(s => s.Name == "EC 2 SAP Analysis");
if (sheet == null)
{
return;
}
string relationshipId = sheet.Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild <SheetData>();
//DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in dataTable.Columns)
{
columns.Add(column.ColumnName);
//DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
//cell.DataType = CellValues.String;
//cell.CellValue = new CellValue(column.ColumnName);
//headerRow.AppendChild(cell);
}
//sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in dataTable.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookPart.Workbook.Save();
}
}
}//end method
public static MemoryStream ExportDataSet(DataSet ds) {
MemoryStream ms = new MemoryStream();
using (var workbook = SpreadsheetDocument.Create(ms, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook)) {
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
foreach (System.Data.DataTable table in ds.Tables) {
var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 1) {
sheetId = sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() {
Id = relationshipId,
SheetId = sheetId,
Name = table.TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List<String> columns = new List<String>();
foreach (System.Data.DataColumn column in table.Columns) {
columns.Add(column.ColumnName);
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}//end loop
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows) {
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns) {
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}//end loop
sheetData.AppendChild(newRow);
}//end loop
}//end loop
workbookPart.Workbook.Save();
workbook.Close();
}//end using
ms.Position = 0;
return ms;
}//end method
private DocumentFormat.OpenXml.Spreadsheet.Row MakeTitleRow(string title)
{
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = 1
};
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "A1"
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(title.Trim());
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = 2;
return(row);
}
private IEnumerable <Cell> GetRowCells(Row row)
{
int currentCount = 0;
foreach (Cell cell in row.Descendants <Cell>())
{
string columnName = GetColumnName(cell.CellReference);
int currentColumnIndex = ConvertColumnNameToNumber(columnName);
for (; currentCount < currentColumnIndex; currentCount++)
{
yield return(new Cell());
}
yield return(cell);
currentCount++;
}
}
private DocumentFormat.OpenXml.Spreadsheet.Row MakeDataRow(uint rowNumber, string cellReference, string value, uint styleIndex)
{
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowNumber
};
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellReference + rowNumber.ToString()
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue(value);
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
return(row);
}
static void SaveRow(Excel.SheetData exportedSheetData, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Row row)
{
Excel.Row exportedRow = new Excel.Row()
{
RowIndex = RowIndex(row), Hidden = row._hidden
};
if (row._hidden)
{
exportedRow.Hidden = true;
}
exportedSheetData.Append(exportedRow);
foreach (var cell in row._cells.OrderBy(r => r.Column._index))
{
SaveCell(exportedRow, styleSheet, cellFormatList, cell);
}
}
/// <summary>
/// Inserts the cell in worksheet.
/// </summary>
/// <param name="sheetData">The sheet data.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="rowIndex">Index of the row.</param>
/// <returns></returns>
protected static SpreadSheet.Cell InsertCellInWorksheet(SpreadSheet.SheetData sheetData, string columnName, uint rowIndex)
{
string cellReference = columnName + rowIndex;
SpreadSheet.Row row;
if (sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <SpreadSheet.Row>().Where(r => r.RowIndex == rowIndex).First();
}
else
{
row = new SpreadSheet.Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
}
// If column doesn't exist, insert one.
if (row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == columnName + rowIndex).Count() > 0)
{
return(row.Elements <SpreadSheet.Cell>().Where(c => c.CellReference.Value == cellReference).First());
}
else
{
SpreadSheet.Cell refCell = null;
foreach (SpreadSheet.Cell cell in row.Elements <SpreadSheet.Cell>())
{
if (string.Compare(cell.CellReference.Value, cellReference, true) > 0)
{
refCell = cell;
break;
}
}
SpreadSheet.Cell newCell = new SpreadSheet.Cell()
{
CellReference = cellReference
};
row.InsertBefore(newCell, refCell);
return(newCell);
}
}
private DocumentFormat.OpenXml.Spreadsheet.Row MakeComplianceHeaderRows(uint rowindex)
{
DocumentFormat.OpenXml.Spreadsheet.Cell refCell = null;
DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowindex
};
DocumentFormat.OpenXml.Spreadsheet.Cell newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "A" + rowindex.ToString()
};
uint styleIndex = 3;
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue("Control");
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
// next column
newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "B" + rowindex.ToString()
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue("Title");
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
// next column
newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "C" + rowindex.ToString()
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue("Checklist");
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
// next column
newCell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = "D" + rowindex.ToString()
};
row.InsertBefore(newCell, refCell);
newCell.CellValue = new CellValue("Status");
newCell.DataType = new EnumValue <CellValues>(CellValues.String);
newCell.StyleIndex = styleIndex;
return(row);
}
private MSOpenXML.Cell CreateCellIfNotExist(MSOpenXML.Worksheet worksheet, string cellName)
{
string columnName = GetColumnName(cellName);
uint rowIndex = GetRowIndex(cellName);
IEnumerable <MSOpenXML.Row> rows = worksheet.Descendants <MSOpenXML.Row>().Where(r => r.RowIndex.Value == rowIndex);
// If the Worksheet does not contain the specified row, create the specified row.
// Create the specified cell in that row, and insert the row into the Worksheet.
if (rows.Count() == 0)
{
MSOpenXML.Row row = new MSOpenXML.Row()
{
RowIndex = rowIndex
};
MSOpenXML.Cell cell = new MSOpenXML.Cell()
{
CellReference = cellName
};
row.Append(cell);
worksheet.Descendants <MSOpenXML.SheetData>().First().Append(row);
return(cell);
}
else
{
MSOpenXML.Row row = rows.First();
IEnumerable <MSOpenXML.Cell> cells = row.Elements <MSOpenXML.Cell>().Where(c => c.CellReference.Value == cellName);
// If the row does not contain the specified cell, create the specified cell.
if (cells.Count() == 0)
{
MSOpenXML.Cell cell = new MSOpenXML.Cell()
{
CellReference = cellName
};
row.Append(cell);
return(cell);
}
else
{
return(cells.First());
}
}
}
private MSOpenXML.Row CreateContentRow(MSOpenXML.SheetData ws, int row, object[] values, Type[] types)
{
//Create cells that contain data
MSOpenXML.Row r = ws.AppendChild(new MSOpenXML.Row());
r.RowIndex = (uint)row;
for (int i = 0; i < values.Length; i++)
{
MSOpenXML.Cell c = r.AppendChild(new MSOpenXML.Cell());
c.CellReference = CellName(row, i);
//if (values[i] != null)
Type t = types[i];
if (t == null)
{
c.DataType = MSOpenXML.CellValues.String;
c.CellValue = new MSOpenXML.CellValue();
}
else if (t.In(typeof(int), typeof(int?), typeof(long), typeof(long?)))
{
c.StyleIndex = 2; //DataType = dos.CellValues.Number;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((long)Convert.ChangeType(values[i], typeof(long))).ToString());
}
else if (t.In(typeof(decimal), typeof(decimal?), typeof(float), typeof(float?), typeof(double), typeof(double?)))
{
c.StyleIndex = 3; //DataType = dos.CellValues.Number;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((decimal)Convert.ChangeType(values[i], typeof(decimal))).ToString(_en_us_ci.NumberFormat));
}
else if (t.In(typeof(DateTime), typeof(DateTime?)))
{
c.StyleIndex = 1; //.DataType = dos.CellValues.Date;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : ((DateTime)values[i]).ToOADate().ToString(_en_us_ci.NumberFormat));
}
else if (t.In(typeof(bool), typeof(bool?)))
{
c.DataType = MSOpenXML.CellValues.Boolean;
c.CellValue = new MSOpenXML.CellValue(values[i].ToString());
}
else
{
c.DataType = MSOpenXML.CellValues.String;
c.CellValue = new MSOpenXML.CellValue(values[i] == null ? null : values[i].ToString());
}
}
return(r);
}
public void UpdateRowIndex(Excel.Row row, int r)
{
if (row.RowIndex != (uint)r)
{
row.RowIndex = (uint)r;
foreach (Excel.Cell cell in row.Descendants <Excel.Cell>())
{
var reference = CellReference.Parse(cell.CellReference);
reference.Row = r;
cell.CellReference = reference.ToString();
//if (cell.CellFormula != null)
//{
// cell.CellValue = null;
//}
}
}
}
private void SetReportBudgetRows(SheetData sheetData, List <DailyReportBudgetData> reportBudgetData)
{
Row row;
List <CellDfn> cells;
foreach (var budgetData in reportBudgetData)
{
cells = new List <CellDfn>();
row = new Row();
row.Append(CreateCell(budgetData.LineItem, CellValues.String, 1));
foreach (var milestone in budgetData.Milestones)
{
row.Append(CreateCell(milestone, CellValues.String, 1));
}
sheetData.Append(row);
}
}
public static double GetHeight(this x.Row row)
{
var height = row.Height;
if (height != null)
{
return(height.Value);
}
var ws = row.ParentOfType <x.Worksheet>();
if (ws == null)
{
throw new InvalidDocumentStructureException();
}
var sheetFormatProps = ws.SheetFormatProperties;
var result = sheetFormatProps?.DefaultRowHeight == null ? 18 : sheetFormatProps.DefaultRowHeight.Value * (72d / 96d);
return(result);
}
public static IEnumerable <DocumentFormat.OpenXml.Spreadsheet.Cell> Cells(this DocumentFormat.OpenXml.Spreadsheet.Row row, SharedStringTable sharedStringTable)
{
foreach (var celll in row.Descendants <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
var c = (DocumentFormat.OpenXml.Spreadsheet.Cell)celll.Clone();
if (celll.CellValue != null)
{
var text = (celll.DataType != null &&
celll.DataType.HasValue &&
celll.CellValue.InnerText != null &&
celll.DataType == CellValues.SharedString)
? sharedStringTable.ChildElements[int.Parse(celll.CellValue.InnerText)].InnerText
: celll.CellValue.InnerText;
c.CellValue.Text = text;
}
yield return(c);
}
}
public void DescargarArchivos()
{
List <OrdenDTO> _ordenes = this.GetOrdenesConArchivos();
if (_ordenes.Count == 0)
{
Console.WriteLine("---------------------------------------------------------------------");
Console.WriteLine(String.Format("No hay ordenes en la tienda {0} para descargar PDFs", this._nombreTienda.ToUpper()));
Console.WriteLine("---------------------------------------------------------------------");
}
_ordenes.ForEach(orden =>
{
string _ordenFolder = $"{ this._workspace}\\{ orden.OrdenId}";
Console.WriteLine(_ordenFolder);
orden.Imprimir();
FileExcel cfile = new FileExcel();
try
{
orden.CustomData.ForEach(x => cfile.AgregarRow(x.Name, x.Value));
string urlLogoDistribuidor = orden.CustomData
.Where(x => x.Name.Contains("Logo"))
.Select(x => x.Value).FirstOrDefault();
string extension = Path.GetExtension(urlLogoDistribuidor);
this.DownloadFile(urlLogoDistribuidor, _ordenFolder, $"{orden.NombreArchivo}{extension}");
Row cabecera = new Row();
cabecera.Append(
cfile.ConstructCell("Valor", CellValues.String),
cfile.ConstructCell("Campo", CellValues.String)
);
cfile.SalvarExcel($"{_ordenFolder}\\{orden.NombreArchivo}.xlsx", "Michelin", cabecera);
}
catch (Exception e)
{
Console.WriteLine("No se pudo crear el archivo excel");
}
this.DownloadFile(orden.FilePdfURL, _ordenFolder, $"{orden.NombreArchivo}.pdf");
});
}
private void SetDailyReportInfo(SheetData sheetData, List <DailyReportInfo> dailyReportInfo)
{
Row row = new Row();
List <CellDfn> cells = new List <CellDfn>();
for (int i = 1; i <= dailyReportInfo.Count; i++)
{
row.Append(
CreateCell(dailyReportInfo[i - 1].Header, CellValues.String, 4),
CreateCell(dailyReportInfo[i - 1].Value, CellValues.String, 1),
CreateCell("", CellValues.String, 1)
);
if (i % 4 == 0)
{
sheetData.Append(row);
row = new Row();
}
}
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint? styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = Excel.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row() { RowIndex = rowIndex };
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements<DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements<DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == Excel.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell() { CellReference = cellAddress };
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements<DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements<DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements<DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue<DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return true;
}
// 08/25/2012 Change Excel export type to use Open XML as the previous format is not supported on Office 2010.
public static void ExportExcelOpenXMLByCustomer(Stream stmResponse, DataView vw, string sModuleName, int nStartRecord, int nEndRecord)
{
// http://msdn.microsoft.com/en-us/library/office/ff478153.aspx
// http://msdn.microsoft.com/en-us/library/office/cc850837
using (MemoryStream stm = new MemoryStream())
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(stm, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDocument.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
worksheetPart.Worksheet.Save();
// http://www.codeproject.com/Articles/371203/Creating-basic-Excel-workbook-with-Open-XML
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
workbookStylesPart.Stylesheet = OpenXML_CreateStylesheet();
workbookStylesPart.Stylesheet.Save();
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sModuleName };
sheets.Append(sheet);
workbookPart.Workbook.Save();
DataTable tbl = vw.Table;
L10N L10n = HttpContext.Current.Items["L10n"] as L10N;
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart<SharedStringTablePart>();
Worksheet worksheet = worksheetPart.Worksheet;
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
UInt32Value numberStyleId = OpenXML_CreateCellFormat(workbookStylesPart.Stylesheet, null, null, UInt32Value.FromUInt32(3));
UInt32Value doubleStyleId = OpenXML_CreateCellFormat(workbookStylesPart.Stylesheet, null, null, UInt32Value.FromUInt32(4));
UInt32Value dateStyleId = OpenXML_CreateCellFormat(workbookStylesPart.Stylesheet, null, null, UInt32Value.FromUInt32(14));
int rowIndex = 1;
Dictionary<string, int> dictStringToInt = new Dictionary<string, int>();
DocumentFormat.OpenXml.Spreadsheet.Cell cell = null;
DocumentFormat.OpenXml.Spreadsheet.Row xRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
xRow.RowIndex = (uint)rowIndex;
sheetData.Append(xRow);
for (int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++)
{
DataColumn col = tbl.Columns[nColumn];
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, col.ColumnName.ToLower(), dictStringToInt);
xRow.AppendChild(cell);
}
rowIndex++;
// 12/02/2013 Add a blank string to the shared array so that there is at least one.
OpenXML_InsertSharedStringItem(shareStringPart, String.Empty, dictStringToInt);
for (int i = nStartRecord; i < nEndRecord; i++, rowIndex++)
{
xRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
xRow.RowIndex = (uint)rowIndex;
sheetData.Append(xRow);
DataRowView row = vw[i];
for (int nColumn = 0; nColumn < tbl.Columns.Count; nColumn++)
{
DataColumn col = tbl.Columns[nColumn];
if (row[nColumn] != DBNull.Value)
{
switch (col.DataType.FullName)
{
case "System.Boolean":
//xw.WriteAttributeString("ss:Type", "String");
cell = OpenXML_CreateBoolean(nColumn + 1, rowIndex, Sql.ToBoolean(row[nColumn]) ? "1" : "0");
xRow.AppendChild(cell);
break;
case "System.Single":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToDouble(row[nColumn]).ToString(), doubleStyleId);
xRow.AppendChild(cell);
break;
case "System.Double":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToDouble(row[nColumn]).ToString(), doubleStyleId);
xRow.AppendChild(cell);
break;
case "System.Int16":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToInteger(row[nColumn]).ToString(), numberStyleId);
xRow.AppendChild(cell);
break;
case "System.Int32":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToInteger(row[nColumn]).ToString(), numberStyleId);
xRow.AppendChild(cell);
break;
case "System.Int64":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToLong(row[nColumn]).ToString(), numberStyleId);
xRow.AppendChild(cell);
break;
case "System.Decimal":
//xw.WriteAttributeString("ss:Type", "Number");
cell = OpenXML_CreateNumber(nColumn + 1, rowIndex, Sql.ToDecimal(row[nColumn]).ToString(), doubleStyleId);
xRow.AppendChild(cell);
break;
case "System.DateTime":
//xw.WriteAttributeString("ss:Type", "DateTime");
cell = OpenXML_CreateDate(nColumn + 1, rowIndex, Sql.ToDateTime(row[nColumn]), dateStyleId);
xRow.AppendChild(cell);
break;
case "System.Guid":
//xw.WriteAttributeString("ss:Type", "String");
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, Sql.ToGuid(row[nColumn]).ToString().ToUpper(), dictStringToInt);
xRow.AppendChild(cell);
break;
case "System.String":
//xw.WriteAttributeString("ss:Type", "String");
// 11/29/2013 Catch and ignore bad data exceptions. This can happen with imported unicode data.
// '', hexadecimal value 0x13, is an invalid character.
try
{
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, Sql.ToString(row[nColumn]), dictStringToInt);
}
catch
{
// 11/29/2013 After exception, the item still remains in the list and causes future save operations to fail.
// 11/29/2013 Use dictionary to increase performance.
OpenXML_RemoveText(shareStringPart, Sql.ToString(row[nColumn]), dictStringToInt);
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
}
xRow.AppendChild(cell);
break;
case "System.Byte[]":
{
//xw.WriteAttributeString("ss:Type", "String");
//byte[] buffer = Sql.ToByteArray((System.Array) row[nColumn]);
//xw.WriteBase64(buffer, 0, buffer.Length);
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
xRow.AppendChild(cell);
break;
}
default:
// throw(new Exception("Unsupported field type: " + rdr.GetFieldType(nColumn).FullName));
// 08/25/2012 We need to write the type even for empty cells.
//xw.WriteAttributeString("ss:Type", "String");
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
xRow.AppendChild(cell);
break;
}
}
else
{
// 08/25/2012 We need to write the type even for empty cells.
// 11/29/2013 Use dictionary to increase performance.
cell = OpenXML_CreateText(nColumn + 1, rowIndex, shareStringPart, String.Empty, dictStringToInt);
xRow.AppendChild(cell);
}
}
}
workbookPart.Workbook.Save();
spreadsheetDocument.Close();
}
stm.WriteTo(stmResponse);
}
}
