public async Task ProcessSheet(Stream file)
{
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(file, false))
{
S sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets;
Task[] tasks = new Task[sheets.Count()];
int i = 0;
// For each sheet, display the sheet information.
foreach (E sheet in sheets)
{
var sheetName = string.Empty;
try
{
sheetName = sheet.GetAttributes().First(c => c.LocalName == "name").Value;
var sheetId = sheet.GetAttributes().First(c => c.LocalName == "id").Value;
log.Info($"Sheet Founded: name: {sheetName}, id: {sheetId}");
tasks[i] = ReadExcelSheet(mySpreadsheet, sheetId);
i++;
log.Info($"Sheet {sheetName} add to queue process");
}
catch (Exception ex)
{
log.Error($"Error on process sheet {sheetName} -> {ex}");
}
}
Task.WaitAll(tasks);
log.Info($"End of file process");
}
}
public static StringCollection GetSheetInfo(string fileName)
{
var stringCollection = new StringCollection();
// Open file as read-only.
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(fileName, false))
{
S sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets;
// For each sheet, display the sheet information.
foreach (E sheet in sheets)
{
foreach (A attr in sheet.GetAttributes())
{
//Console.WriteLine("{0}: {1}", attr.LocalName, attr.Value);
if (attr.LocalName == "name")
{
stringCollection.Add(attr.Value);
break;
}
}
}
}
return(stringCollection);
}
public FileResult OpenXML_Download()
{
// save the doc on local storage (blob)
string fileName = "D:/Documents/tmp/tst.xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
}
return(File(fileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
public void X002_XlsxCreation()
{
FileInfo fi = new FileInfo(Path.Combine(s_TestFileLocation, Guid.NewGuid().ToString() + ".docx"));
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument doc = SpreadsheetDocument.Create(fi.FullName, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = doc.AddWorkbookPart();
workbookpart.Workbook = new S.Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new S.Worksheet(new S.SheetData());
S.Sheets sheets = doc.WorkbookPart.Workbook.AppendChild <S.Sheets>(new S.Sheets());
S.Sheet sheet = new S.Sheet()
{
Id = doc.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
OpenXmlValidator v = new OpenXmlValidator(FileFormatVersions.Office2013);
var errs = v.Validate(doc);
Assert.Equal(0, errs.Count());
doc.Close();
if (TestUtil.DeleteTempFiles)
{
fi.Delete();
}
}
public Spreadsheet.Sheet AddSheet(string sheetName)
{
uint maxSheetId = 1;
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
wsPart = this.workbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
wsPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
sheets = this.workbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
if (sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
{
maxSheetId = sheets.Elements <DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max();
maxSheetId++;
}
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet();
sheet.Id = this.workbookPart.GetIdOfPart(wsPart);
sheet.SheetId = maxSheetId;
sheet.Name = sheetName;
//if (this.workbookPart.Workbook.Sheets == null)
//{
// sheets = this.workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
//}
sheets.Append(sheet);
Spreadsheet.Sheet newSheet = new Spreadsheet.Sheet(ref wsPart, ref sheet);
this.sheetCollection.Add(newSheet);
return(newSheet);
}
public FileResult OpenXML_DownloadNoSave()
{
byte[] b;
// save the doc on local storage (blob)
using (MemoryStream memStream = new MemoryStream())
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(memStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new SheetData());
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "DownloadNoSave"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
}
b = memStream.ToArray();
return(File(b, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
}
public void X003_XlsxCreation_Stream()
{
using (MemoryStream ms = new MemoryStream())
// By default, AutoSave = true, Editable = true, and Type = xlsx.
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = doc.AddWorkbookPart();
workbookpart.Workbook = new S.Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new S.Worksheet(new S.SheetData());
S.Sheets sheets = doc.WorkbookPart.Workbook.AppendChild <S.Sheets>(new S.Sheets());
S.Sheet sheet = new S.Sheet()
{
Id = doc.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet",
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
OpenXmlValidator v = new OpenXmlValidator(FileFormatVersions.Office2013);
var errs = v.Validate(doc);
Assert.Empty(errs);
}
}
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);
}
}
}
public static void CreateExcelDoc(List <fnRESIDENTCONTRACT> marketType, string path)
{
var fileName = path + @"\Export.xls";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet();
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Residents"
};
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
row.Append(
ConstructCell("ID", CellValues.Number),
ConstructCell("NAME", CellValues.String),
ConstructCell("FAMILY", CellValues.String),
ConstructCell("FATHERNAME", CellValues.String),
ConstructCell("BRANCH", CellValues.Number),
ConstructCell("STREET", CellValues.Boolean),
ConstructCell("CITY", CellValues.Boolean));
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
sheets.Append(sheet);
foreach (var temp in marketType)
{
row = new Row();
row.Append(
ConstructCell(temp.ID.ToString(), CellValues.Number),
ConstructCell(temp.NAME.ToString(), CellValues.String),
ConstructCell(temp.FAMILY.ToString(), CellValues.String),
ConstructCell(temp.FATHERNAME.ToString(), CellValues.String),
ConstructCell(temp.BRANCH.ToString(), CellValues.Number),
ConstructCell(temp.STREET.ToString(), CellValues.String),
ConstructCell(temp.CITY.ToString(), CellValues.Boolean));
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
document.Close();
}
}
// 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();
}
}
}//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
static void SaveDocument(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Spreadsheet document)
{
var exportedWorkbook = new Excel.Workbook();
exportedWorkbookPart.Workbook = exportedWorkbook;
Excel.Sheets exportedSheets = new Excel.Sheets();
exportedWorkbook.AppendChild(exportedSheets);
uint sheetId = 1;
foreach (var sheet in document.Sheets)
{
SaveSheet(exportedWorkbookPart, styleSheet, cellFormatList, exportedSheets, sheet, sheetId++);
}
}
public async Task Execute(string filePath, bool save)
{
using (SpreadsheetDocument mySpreadsheet = SpreadsheetDocument.Open(filePath, true))
{
S sheets = mySpreadsheet.WorkbookPart.Workbook.Sheets;
// For each sheet, display the sheet information.
foreach (E sheet in sheets)
{
var sheetName = string.Empty;
try
{
sheetName = sheet.GetAttributes().First(c => c.LocalName == "name").Value;
//verifica se o nome da planilha contem somente números.
if (!Regex.IsMatch(sheetName, @"^[0-9]+$"))
{
Log(LogType.Information, $"Planilha {sheetName} ignorada");
continue;
}
Log(LogType.Trace, $"Planilha {sheetName} - iniciando processamento");
var sheetId = sheet.GetAttributes().First(c => c.LocalName == "id").Value;
var qtdRowFounded = await ReadExcelSheet(mySpreadsheet, sheetId, save);
Log(LogType.Trace, $"Planilha {sheetName} - fim processamento, {qtdRowFounded} linhas foram alteradas.");
}
catch (Exception ex)
{
Log(LogType.Error, "Ocorreu um erro");
}
}
if (save)
{
mySpreadsheet.Save();
}
mySpreadsheet.Close();
}
}
/// <summary>
/// Adds a new worksheet to the workbook
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="name">Name of the worksheet</param>
/// <returns>True if succesful</returns>
public static bool AddWorksheet(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, string name)
{
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheet.WorkbookPart.Workbook.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>();
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet;
DocumentFormat.OpenXml.Packaging.WorksheetPart worksheetPart;
// Add the worksheetpart
worksheetPart = spreadsheet.WorkbookPart.AddNewPart <DocumentFormat.OpenXml.Packaging.WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
worksheetPart.Worksheet.Save();
// Add the sheet and make relation to workbook
sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(spreadsheet.WorkbookPart.Workbook.Sheets.Count() + 1),
Name = name
};
sheets.Append(sheet);
spreadsheet.WorkbookPart.Workbook.Save();
return(true);
}
}//экспорт в XLSX
private void ExportDataSet(DataTable table, string destination)
{
using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
//режем тэйбл от мусора
table.Columns.Remove("Код");
table.Columns.Remove("ID");
//создание workbook+sheets
var workbookPart = workbook.AddWorkbookPart();
workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();
//собственный формат для даты
//styles
WorkbookStylesPart workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>("rId3");
Stylesheet stylesheet = new Stylesheet();
// Date Time Display Format when s="1" is applied to cell
NumberingFormats numberingFormats = new NumberingFormats()
{
Count = (UInt32Value)1U
};
NumberingFormat numberingFormat = new NumberingFormat()
{
NumberFormatId = (UInt32Value)164U, FormatCode = "dd.MM.yyyy"
};
numberingFormats.Append(numberingFormat);
// Cell font
Fonts fonts = new Fonts()
{
Count = (UInt32Value)1U
};
DocumentFormat.OpenXml.Spreadsheet.Font font = new DocumentFormat.OpenXml.Spreadsheet.Font();
FontSize fontSize = new FontSize()
{
Val = 11D
};
FontName fontName = new FontName()
{
Val = "Calibri"
};
font.Append(fontSize);
font.Append(fontName);
fonts.Append(font);
// empty, but mandatory
Fills fills = new Fills()
{
Count = (UInt32Value)1U
};
Fill fill = new Fill();
fills.Append(fill);
Borders borders = new Borders()
{
Count = (UInt32Value)1U
};
Border border = new Border();
borders.Append(border);
// cellFormat1 for text cell cellFormat2 for Datetime cell
CellFormats cellFormats = new CellFormats()
{
Count = (UInt32Value)2U
};
CellFormat cellFormat1 = new CellFormat()
{
FontId = (UInt32Value)0U
};
CellFormat cellFormat2 = new CellFormat()
{
NumberFormatId = (UInt32Value)164U, FontId = (UInt32Value)0U, ApplyNumberFormat = true
};
cellFormats.Append(cellFormat1);
cellFormats.Append(cellFormat2);
// Save as styles
stylesheet.Append(numberingFormats);
stylesheet.Append(fonts);
stylesheet.Append(fills);
stylesheet.Append(borders);
stylesheet.Append(cellFormats);
workbookStylesPart.Stylesheet = stylesheet;
{
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() > 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 = "Отчет"
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
//создаем header в xlsx
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);
}
sheetData.AppendChild(headerRow);
//Перенос из datatable
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();
//проверка типа входных данных
Type dtype = dsrow[col].GetType();
switch (dtype.Name.ToString())
{
case "DateTime":
DateTime dt = Convert.ToDateTime(dsrow[col].ToString());
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dt.ToOADate().ToString());
cell.StyleIndex = 1;
break;
case "String":
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
break;
case "Int32":
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
break;
default:
break;
}
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
}//вывод dataTable в xlsx с учетом типа данных
public static void CreateCrossTabulation(string filePath, DataTable dt, List <SQLBuilder.Clauses.Column> SelectedColumns, ResultViewModel rvm)
{
FileInfo f = new FileInfo(filePath);
if (f.Exists)
{
f.Delete();
}
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
var stylesPart = spreadsheetDocument.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = (spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets()));
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
string cl = "A";
uint row = 1;
int index;
Cell cell = new Cell();
System.Windows.Controls.Label colRow = new System.Windows.Controls.Label();
int lineNumber = 0;
colRow.Content = "Cross Tabulation by " + rvm.QueryBulder.CrossTabulationResults.CrossTabColumn.AliasName;
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(colRow.Content));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
row++;
List <List <string> > groupByColumnValues = rvm.QueryBulder.CrossTabulationResults.GroupByColumnValueList;
for (int groupByColIndex = 0; groupByColIndex < rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count; groupByColIndex++)
{
SQLBuilder.Clauses.Column groupByCol = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.ElementAt <SQLBuilder.Clauses.Column>(groupByColIndex);
string colFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(groupByCol.Format);
CrossTabulationViewGroupByControl ctvgCntrl = new CrossTabulationViewGroupByControl();
colRow.Content = Common.getColumnNameOrAlias(groupByCol);
for (int i = 0; i <= groupByColumnValues.Count + 1; i++)
{
if (groupByColIndex >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((groupByColIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + groupByColIndex % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + groupByColIndex));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
if (row == 2)
{
index = InsertSharedStringItem(dt.Columns[groupByColIndex].ColumnName, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(dt.Columns[groupByColIndex]));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row > 3)
{
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(groupByColumnValues.ElementAt <List <string> >(i - 2).ElementAt <string>(groupByColIndex)));
}
lineNumber = lineNumber + 1;
row++;
}
row = 2;
}
int groupByColCount = rvm.QueryBulder.CrossTabulationResults.GroupByColumns.Count;
int summarrizeValueIndex = groupByColCount + 1;
Dictionary <string, Object> dataMap = rvm.QueryBulder.CrossTabulationResults.DataMap;
row = 1;
foreach (string summaryMainValue in rvm.QueryBulder.CrossTabulationResults.CrossTabColumnVaues)
{
CrossTabulationViewSummaryMainControl summaryMain = new CrossTabulationViewSummaryMainControl();
summaryMain.lblSummaryHeader.Content = summaryMainValue;
int totalcolumn = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count + groupByColCount;
for (int summaryColIndex = 0; summaryColIndex < rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.Count; summaryColIndex++)
{
SQLBuilder.Clauses.Column summaryCol = rvm.QueryBulder.CrossTabulationResults.SummarizeColumns.ElementAt <SQLBuilder.Clauses.Column>(summaryColIndex);
string summaryColName = summaryCol.AliasName;
string summarycolFormat = SQLBuilder.Common.ColumnFormat.Instance.getColumnFormat(summaryCol.Format);
CrossTabulationViewSummaryControl ctvsCtrl = new CrossTabulationViewSummaryControl();
for (int keyIndex = 0; keyIndex <= rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2; keyIndex++)
{
if (summaryColIndex >= 26)
{
cl = Convert.ToString(Convert.ToChar(65 + ((summarrizeValueIndex / 26) - 1))) + Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex % 26));
}
else
{
cl = Convert.ToString(Convert.ToChar(65 + summarrizeValueIndex));
}
SharedStringTablePart shareStringPart;
if (spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0)
{
shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First();
}
else
{
shareStringPart = spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
}
if (row == 1)
{
index = InsertSharedStringItem(summaryMainValue, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(summaryMainValue));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row == 2)
{
index = InsertSharedStringItem(summaryColName, shareStringPart);
cell = InsertCellInWorksheet(cl, row, worksheetPart);
cell.CellValue = new CellValue(Convert.ToString(summaryColName));
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
cell.StyleIndex = 7;
}
else if (row > 3)
{
string key = rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.ElementAt <string>(keyIndex - 3);
string keyValue = key + summaryMainValue + summaryColIndex;
cell = InsertCellInWorksheet(cl, row, worksheetPart);
if (dataMap.ContainsKey(keyValue))
{
if (summarycolFormat != null)
{
cell.CellValue = new CellValue(String.Format(summarycolFormat, dataMap[keyValue]));
}
else
{
cell.CellValue = new CellValue(Convert.ToString(dataMap[keyValue]));
}
}
else
{
cell.CellValue = new CellValue(Convert.ToString("00"));
}
}
lineNumber = lineNumber + 1;
if (row > rvm.QueryBulder.CrossTabulationResults.KeyPrefixes.Count + 2)
{
row = 1;
summarrizeValueIndex = summarrizeValueIndex + 1;
}
else
{
row++;
}
}
lineNumber = 0;
}
row = 1;
groupByColCount = groupByColCount + 1;
}
worksheetPart.Worksheet.Save();
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
}
/*
* static Excel.SharedStringTable SaveSharedStringTable()
* {
* var exportedSharedStringTable = new Excel.SharedStringTable();
*
* return exportedSharedStringTable;
* }
*/
static void SaveSheet(Package.WorkbookPart exportedWorkbookPart, Excel.Stylesheet styleSheet, Dictionary <CellFormat, uint> cellFormatList, Excel.Sheets exportedSheets, Sheet sheet, uint sheetId)
{
var exportedWorksheetPart = exportedWorkbookPart.AddNewPart <Package.WorksheetPart>();
string relId = exportedWorkbookPart.GetIdOfPart(exportedWorksheetPart);
var exportedWorksheet = new Excel.Worksheet();
exportedWorksheetPart.Worksheet = exportedWorksheet;
var exportedColumns = new Excel.Columns();
exportedWorksheet.Append(exportedColumns);
var exportedSheetData = new Excel.SheetData();
exportedWorksheet.Append(exportedSheetData);
var exportedSheet = new Excel.Sheet()
{
Name = sheet.Name, Id = relId, SheetId = sheetId
};
if (sheet.Hidden)
{
exportedSheet.State = Excel.SheetStateValues.Hidden;
}
exportedSheets.Append(exportedSheet);
foreach (var column in sheet.Columns.OrderBy(r => r.Index))
{
SaveColumn(exportedColumns, column);
}
foreach (var row in sheet.Rows.OrderBy(r => r.Index))
{
SaveRow(exportedSheetData, styleSheet, cellFormatList, row);
}
exportedWorksheetPart.Worksheet.Save();
}
public static String ExportHportIDXToOpenXMLCSV(GridView gv, string fileName, string sheetName = "")
{
string filePath = HttpContext.Current.Server.MapPath(fileName + sheetName.ToString() + ".xlsx");
StringBuilder sbCSVString = new StringBuilder();
try
{
using (var workbook = SpreadsheetDocument.Create(filePath, 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();
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() > 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();
sheet.Id = relationshipId;
sheet.SheetId = sheetId;
if (sheetName != "")
{
sheet.Name = sheetName;
}
else
{
sheet.Name = fileName;
}
WorksheetPart newWorksheetPart;
newWorksheetPart = sheetPart.Worksheet.WorksheetPart;
SheetProperties sp = new SheetProperties(new PageSetupProperties());
sheetPart.Worksheet.SheetProperties = sp;
sheetPart.Worksheet.SheetProperties.PageSetupProperties.FitToPage = BooleanValue.FromBoolean(true);
DocumentFormat.OpenXml.Spreadsheet.PageSetup pgOr = new DocumentFormat.OpenXml.Spreadsheet.PageSetup();
pgOr.Orientation = DocumentFormat.OpenXml.Spreadsheet.OrientationValues.Portrait;
pgOr.FitToHeight = 3;
pgOr.FitToWidth = 1;
sheetPart.Worksheet.AppendChild(pgOr);
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
int rowCounter = 0;
int columnCounter = 0;
DocumentFormat.OpenXml.Spreadsheet.Row columnRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (DataColumn col in ((DataTable)gv.DataSource).Columns)
{
String cellValue = col.ColumnName;
DocumentFormat.OpenXml.Spreadsheet.Cell cellCol = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cellCol.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cellCol.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
columnRow.AppendChild(cellCol);
sbCSVString.Append(cellValue);
columnCounter++;
if (columnCounter < ((DataTable)gv.DataSource).Columns.Count)
{
sbCSVString.Append(",");
}
}
sheetData.AppendChild(columnRow);
sbCSVString.Append(Environment.NewLine);
foreach (GridViewRow dataSourceRow in gv.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newSheetRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
//DataRow dataSourceRow = rowView.Row;
string cellValue = dataSourceRow.Cells[0].Text.ToString();
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[1].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[2].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[3].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[4].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sbCSVString.Append(",");
cellValue = dataSourceRow.Cells[5].Text.ToString();
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
newSheetRow.AppendChild(cell);
sbCSVString.Append(cellValue);
sheetData.AppendChild(newSheetRow);
sbCSVString.Append(Environment.NewLine);
rowCounter = rowCounter + 1;
}
}
File.WriteAllText(filePath.Replace("xlsx", "csv"), sbCSVString.ToString());
}
catch (System.Exception ex)
{
string sError;
sError = ex.ToString();
throw ex;
}
return(filePath);
}
public void generarReporteGenerico(DataSet dsDatosReporte, string nombreReporte)
{
string directorio = @"C:\Reportes\";
string pathArchivo = directorio + nombreReporte + ".xlsx";
if (!Directory.Exists(directorio))
{
Directory.CreateDirectory(directorio);
}
try
{
var workbook = SpreadsheetDocument.Create((pathArchivo), 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 dsDatosReporte.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() > 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 (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);
}
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);
}
sheetData.AppendChild(newRow);
}
}
workbook.WorkbookPart.Workbook.Save();
workbook.Close();
}
}
catch (Exception)
{
throw;
}
System.Diagnostics.Process.Start(pathArchivo);
}
internal SheetCollection(ref DocumentFormat.OpenXml.Packaging.WorkbookPart workbookPart)
{
this.workbookPart = workbookPart;
sheets = this.workbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
this.list = new List <Spreadsheet.Sheet>();
}
private void CreateExcelFile(DataTable table, string destination)
{
// hfFileName.Value = destination;
// lblFileName.Text = string.Empty;
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();
//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() > 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 (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);
}
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);
}
sheetData.AppendChild(newRow);
}
//}
}
// btnDownloadExcel.Visible = true;
// lblFileName.Text = "Servicing file created successfully";
}
/// <summary>
/// 创建excel,并且把dataTable导入到excel中
/// </summary>
/// <param name="destination">保存路径</param>
/// <param name="dataTables">数据源</param>
/// <param name="sheetNames">excel中sheet的名称</param>
public void CreateExcel(string destination, DataTable[] dataTables, string[] sheetNames = null)
{
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 dataTables)
{
var sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>();
var sheetData = new SheetData();
sheetPart.Worksheet = new Worksheet();
Columns headColumns = CrateColunms(table);
sheetPart.Worksheet.Append(headColumns);
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;
}
string sheetName = string.Empty;
if (sheetNames != null)
{
if (sheetNames.Length >= sheetId)
{
sheetName = sheetNames[sheetId - 1].ToString();
}
}
else
{
sheetName = table.TableName ?? sheetId.ToString();
}
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = sheetName
};
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.StyleIndex = 11;
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.StyleIndex = 10;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Append(sheetData);
}
workbook.Close();
}
}
//------ NumberFormatId upto 164 is "In-Built"---------------//
//public void ExportDataSet(DataSet ds, string destination, string fileName)
//{
// try
// {
// if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
// {
// 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();
// WorkbookStylesPart stylesPart = workbook.WorkbookPart.AddNewPart<WorkbookStylesPart>();
// stylesPart.Stylesheet = GenerateStyleSheet();
// stylesPart.Stylesheet.Save();
// 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() > 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 (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);
// cell.StyleIndex = 3;
// headerRow.AppendChild(cell);
// }
// 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);
// }
// sheetData.AppendChild(newRow);
// }
// }
// }
// downloadfile(destination, fileName);
// }
// }
// catch (Exception ex)
// {
// }
//}
/// <summary>
///
/// </summary>
/// <param name="dsHeader">Custom Header DataSet</param>
/// <param name="ds">Actual dataset with values</param>
/// <param name="customHeader">True: Use custom header dataset, False: used table header</param>
/// <param name="destination">File location with name</param>
public void ExportDataSet(DataSet dsHeader, DataSet ds, bool customHeader, string filename, string exportFileName)
{
string dbFilePath = "/DownloadExcel/";
string strFileName = GenerateFileName(filename) + "xlsx";
if (!Directory.Exists(System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath)))
{
Directory.CreateDirectory(System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath));
}
string destination = System.Web.HttpContext.Current.Server.MapPath("~" + dbFilePath) + strFileName;
try
{
if ((dsHeader.Tables.Count == ds.Tables.Count && customHeader) || !customHeader)
{
List <KeyValuePair <string, string> > mergecellsdictionary = new List <KeyValuePair <string, string> >();
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();
WorkbookStylesPart stylesPart = workbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
for (int i = 0; i < ds.Tables.Count; i++)
{
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() > 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 = ds.Tables[i].TableName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
List <String> columns = new List <string>();
if (customHeader)
{
foreach (System.Data.DataColumn column in dsHeader.Tables[i].Columns)
{
columns.Add(column.ColumnName);
}
int rIndex = 1;
foreach (System.Data.DataRow dsrow in dsHeader.Tables[i].Rows)
{
headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (String col in columns)
{
string[] strval = Convert.ToString(dsrow[col]).Split(',');
if (strval.Length == 4)
{
// Check for rowspan
if (!string.IsNullOrEmpty(strval[2]) && strval[2].All(Char.IsDigit))
{
if (Convert.ToInt32(strval[2]) > 1)
{
int rpos = (columns.IndexOf(col)) + 1;
mergecellsdictionary.Add(new KeyValuePair <string, string>(sheet.Name, getColumnNameFromIndex(rpos) + rIndex.ToString() + "," + getColumnNameFromIndex(rpos) + (rIndex + Convert.ToInt32(strval[2]) - 1).ToString()));
}
}
// Check for colspan
if (!string.IsNullOrEmpty(strval[3]) && strval[3].All(Char.IsDigit))
{
if (Convert.ToInt32(strval[3]) > 1)
{
int cpos = (columns.IndexOf(col)) + 1;
mergecellsdictionary.Add(new KeyValuePair <string, string>(sheet.Name, getColumnNameFromIndex(cpos) + rIndex.ToString() + "," + getColumnNameFromIndex(cpos + Convert.ToInt32(strval[3]) - 1) + rIndex.ToString()));
}
}
}
else if (strval.Length == 1)
{
string str1 = strval[0];
strval = new string[] { str1, "1" };
}
int indexNum = (strval[1].All(Char.IsDigit)) ? Convert.ToInt32(strval[1]) : 1;
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(strval[0]);
cell.StyleIndex = (UInt32)indexNum;
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
rIndex = rIndex + 1;
}
}
else
{
foreach (System.Data.DataColumn column in ds.Tables[i].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);
cell.StyleIndex = 1; // The light gray Fill
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
}
foreach (System.Data.DataRow dsrow in ds.Tables[i].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;
if (IsDouble(dsrow[col].ToString()))
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.Number;
}
else
{
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
if (mergecellsdictionary.Count > 0)
{
foreach (KeyValuePair <string, string> entry in mergecellsdictionary)
{
string[] strcells = entry.Value.Split(',');
mergeCell(destination, entry.Key, strcells[0], strcells[1]);
}
}
downloadfile(destination, exportFileName);
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (System.IO.File.Exists(destination))
{
System.IO.File.Delete(destination);
}
}
}
/// <summary>
/// Determines whether [has sheet with name] [the specified sheet name].
/// </summary>
/// <param name="sheets">The sheets.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <returns></returns>
public static bool HasSheetWithName(this DocumentFormat.OpenXml.Spreadsheet.Sheets sheets, string sheetName)
{
return((from s in sheets.Descendants <DocumentFormat.OpenXml.Spreadsheet.Sheet>()
where s.Name != null && s.Name.HasValue && s.Name.Value == sheetName
select s).Any());
}
public void LoadData(string sheetName, uint sheetId, DataTable dt)
{
DocumentFormat.OpenXml.Packaging.WorksheetPart wsPart = null;
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = null;
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = null;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = null;
uint rowIndex = 1;
uint colIndex = 1;
string collName = string.Empty;
Row row = null;
Cell cell = null;
sheetData = new SheetData();
// Header Row
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
// Header Row cellls
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dc.ColumnName;
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
foreach (DataRow dr in dt.Rows)
{
row = new Row()
{
RowIndex = rowIndex
};
sheetData.Append(row);
colIndex = 1;
foreach (DataColumn dc in dt.Columns)
{
collName = ExcelHelper.ConvertColumnNumberToName((int)colIndex);
cell = new Cell()
{
CellReference = collName + rowIndex
};
row.AppendChild(cell);
Text txt = new Text();
txt.Text = dr[dc.ColumnName].ToString();
InlineString inStr = new InlineString();
inStr.AppendChild(txt);
cell.DataType = CellValues.InlineString;
cell.AppendChild(inStr);
colIndex++;
}
rowIndex++;
}
wsPart = wbPart.AddNewPart <WorksheetPart>();
wsPart.Worksheet = new Worksheet(sheetData);
sheet = new Sheet();
sheet.Id = wbPart.GetIdOfPart(wsPart);
sheet.SheetId = sheetId;
sheet.Name = sheetName;
sheets = wbPart.Workbook.AppendChild(new Sheets());
sheets.Append(sheet);
}
public override void Write()
{
ExportFileName = PopulatedName(ExportFileName);
if (!String.IsNullOrWhiteSpace(ExportFileName))
{
DocProperties["FileName"] = ExportFileName;
DocProperties["TableCount"] = _dataSet.Tables.Count.ToString();
if (PopulatePropertiesOnly)
{
if (_dataSet != null)
{
foreach (DataTable dTable in _dataSet.Tables)
{
if (dTable.Rows.Count > 0)
{
foreach (DataColumn dColumn in dTable.Columns)
{
DocProperties[dColumn.ColumnName] = dTable.Rows[0][dColumn].ToString();
}
}
}
}
}
switch (DestinationType)
{
case OfficeFileType.WordDocument:
WordprocessingDocument doc;
if (File.Exists(TemplateFileName))
{
doc = WordprocessingDocument.CreateFromTemplate(TemplateFileName);
doc = (WordprocessingDocument)doc.SaveAs(ExportFileName);
}
else
{
doc = WordprocessingDocument.Create(ExportFileName, WordprocessingDocumentType.Document);
}
CustomFilePropertiesPart customProp = doc.CustomFilePropertiesPart;
if (customProp == null)
{
customProp = doc.AddCustomFilePropertiesPart();
}
SetFileProperties(customProp);
MainDocumentPart mainDoc = doc.MainDocumentPart;
if (mainDoc == null)
{
mainDoc = doc.AddMainDocumentPart();
}
DocumentSettingsPart settingsPart = mainDoc.GetPartsOfType <DocumentSettingsPart>().First();
UpdateFieldsOnOpen updateFields = new UpdateFieldsOnOpen
{
Val = new OnOffValue(true)
};
settingsPart.Settings.PrependChild <UpdateFieldsOnOpen>(updateFields);
settingsPart.Settings.Save();
if (!PopulatePropertiesOnly)
{
if (mainDoc.Document == null)
{
mainDoc.Document = new word.Document();
}
word.Body body = new word.Body();
bool firstTable = true;
foreach (DataTable dt in _dataSet.Tables)
{
if (!firstTable)
{
body.Append(GetPageBreak());
}
else
{
firstTable = false;
}
body.Append(GetParagraph(dt.TableName));
body.Append(GetWordTable(dt));
}
mainDoc.Document.Append(body);
}
mainDoc.Document.Save();
doc.Dispose();
break;
case OfficeFileType.ExcelWorkbook:
SpreadsheetDocument spreadSheet;
if (File.Exists(TemplateFileName))
{
spreadSheet = SpreadsheetDocument.CreateFromTemplate(TemplateFileName);
spreadSheet = (SpreadsheetDocument)spreadSheet.SaveAs(ExportFileName);
}
else
{
spreadSheet = SpreadsheetDocument.Create(ExportFileName, SpreadsheetDocumentType.Workbook);
spreadSheet.Save();
}
using (SpreadsheetDocument workbook = spreadSheet)
{
CustomFilePropertiesPart excelCustomProp = workbook.AddCustomFilePropertiesPart();
SetFileProperties(excelCustomProp);
if (workbook.WorkbookPart == null)
{
workbook.AddWorkbookPart();
}
if (workbook.WorkbookPart.Workbook == null)
{
workbook.WorkbookPart.Workbook = new excel.Workbook();
}
if (workbook.WorkbookPart.Workbook.Sheets == null)
{
workbook.WorkbookPart.Workbook.Sheets = new excel.Sheets();
}
excel.Sheets sheets = workbook.WorkbookPart.Workbook.Sheets;
foreach (DataTable table in _dataSet.Tables)
{
excel.SheetData sheetData = null;
WorksheetPart sheetPart = null;
excel.Sheet sheet = null;
foreach (OpenXmlElement element in sheets.Elements())
{
if (element is Sheet)
{
sheet = (Sheet)element;
if (sheet.Name.Value.Equals(table.TableName, StringComparison.CurrentCultureIgnoreCase))
{
//Assign the sheetPart
sheetPart = (WorksheetPart)workbook.WorkbookPart.GetPartById(sheet.Id.Value);
sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
break;
}
}
sheet = null;
}
if (sheet == null)
{
sheetPart = workbook.WorkbookPart.AddNewPart <WorksheetPart>(); //Create a new WorksheetPart
sheetData = new excel.SheetData(); //create a new SheetData
sheetPart.Worksheet = new excel.Worksheet(sheetData); /// Create a new Worksheet with the sheetData and link it to the sheetPart...
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart); //get the ID of the sheetPart.
sheet = new excel.Sheet()
{
Id = relationshipId, SheetId = 1, Name = table.TableName
}; //create a new sheet
sheets.Append(sheet); //append the sheet to the sheets.
}
List <String> columns = new List <string>();
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
}
if (PrintTableHeader)
{
excel.Row headerRow = new excel.Row();
foreach (string column in columns)
{
excel.Cell cell = new excel.Cell
{
DataType = excel.CellValues.String,
CellValue = new excel.CellValue(GetColumnName(table.Columns[column]))
};
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
}
foreach (DataRow dsrow in table.Rows)
{
excel.Row newRow = new excel.Row();
foreach (String col in columns)
{
excel.Cell cell = new excel.Cell
{
DataType = excel.CellValues.String,
CellValue = new excel.CellValue(dsrow[col].ToString()) //
};
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
sheetPart.Worksheet.Save();
}
workbook.WorkbookPart.Workbook.Save();
workbook.Save();
workbook.Close();
}
break;
}
}
}
public async Task <bool> Export(IEnumerable <T> _list)
{
bool exported = false;
string className = typeof(T).Name;
this.fileName = string.Concat(System.IO.Path.GetFileNameWithoutExtension(this.fileName), "_", className.ToUpper(), System.IO.Path.GetExtension(this.fileName));
bool IsFileAvailable = await CheckIfFileCanBeUsed(this.fileName);
if (IsFileAvailable)
{
PropertyInfo[] properties = typeof(T).GetProperties();
string sheetName = "SHEET " + className.ToUpper();
string randomPath = this.RandomPath;
this.oldPath = randomPath;
using (var workbook = SpreadsheetDocument.Create(randomPath, 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();
WorkbookStylesPart workStylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
workStylePart.Stylesheet = GenerateStyleSheet();
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() > 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 = sheetName
};
sheets.Append(sheet);
DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
headerRow.Height = new DoubleValue()
{
Value = 32
};
foreach (PropertyInfo propertyInfo in properties)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(propertyInfo.Name.ToUpper());
cell.StyleIndex = 1;
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (T _obj in _list)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
foreach (PropertyInfo propertyInfo in properties)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
object propertyValue = propertyInfo.GetValue(_obj, null);
string cellValue = (propertyValue != null ? propertyValue.ToString() : "");
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(cellValue);
cell.StyleIndex = 0;
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbook.Save();
exported = true;
}
}
return(exported);
}
public dynamic CreateJsonFromCSV(string filePath)
{
int recordNumber = 1;
Resource resource = new Resource();
List <dynamic> ResourcesList = new List <dynamic>();
List <dynamic> organizationsList = new List <dynamic>();
List <dynamic> articlesList = new List <dynamic>();
List <dynamic> organizationReviewsList = new List <dynamic>();
List <dynamic> Resources = new List <dynamic>();
List <string> sheetNames = new List <string>()
{
Constants.ArticleSheetName, Constants.ArticleSectionSheetName, Constants.VideoSheetName, Constants.AdditionalReadingSheetName, Constants.FormSheetName, Constants.OrganizationSheetName, Constants.OrganizationReviewSheetName, Constants.RelatedLinkSheetName
};
try
{
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
Spreadsheet.Sheets sheets = workbookPart.Workbook.GetFirstChild <Spreadsheet.Sheets>();
foreach (Spreadsheet.Sheet sheet in sheets)
{
if (sheet.Name.HasValue && sheetNames.Find(a => a == sheet.Name.Value) != null)
{
Spreadsheet.Worksheet worksheet = ((WorksheetPart)workbookPart.GetPartById(sheet.Id)).Worksheet;
Spreadsheet.SheetData sheetData = worksheet.Elements <Spreadsheet.SheetData>().First();
Spreadsheet.SharedStringTable sharedStringTable = spreadsheetDocument.WorkbookPart.SharedStringTablePart.SharedStringTable;
Dictionary <string, string> keyValuePairs = new Dictionary <string, string>();
string cellValue;
int counter = 0;
bool isValidated = false;
ClearVariableData();
topicTagIds = new List <TopicTag>();
locations = new List <Shared.Models.Location>();
string resourceIdCell = string.Empty;
string resourceType = GetResourceType(sheet.Name.Value);
foreach (Spreadsheet.Row row in sheetData.Elements <Spreadsheet.Row>())
{
if (counter == 1)
{
var resourceIdColumn = from a in keyValuePairs where a.Key == "Id" select a.Value.First().ToString();
if (resourceIdColumn.Count() > 0)
{
resourceIdCell = resourceIdColumn.First();
}
}
foreach (Spreadsheet.Cell cell in row.Elements <Spreadsheet.Cell>())
{
cellValue = cell.InnerText;
if (string.IsNullOrEmpty(cellValue))
{
if (!string.IsNullOrEmpty(resourceIdCell) && cell.CellReference == string.Concat(resourceIdCell + row.RowIndex))
{
cell.CellValue = new CellValue(Guid.NewGuid().ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.String);
workbookPart.Workbook.Save();
}
}
else if (!string.IsNullOrEmpty(cellValue))
{
string cellActualValue = string.Empty;
if (cell.DataType != null && cell.DataType == Spreadsheet.CellValues.SharedString)
{
cellActualValue = sharedStringTable.ElementAt(Int32.Parse(cellValue, CultureInfo.InvariantCulture)).InnerText;
}
else
{
cellActualValue = cellValue;
}
if (counter == 0)
{
keyValuePairs.Add(cellActualValue, cell.CellReference);
}
else
{
var headerValues = from a in keyValuePairs select a.Key;
if (!isValidated)
{
if (!ValidateHeader(headerValues.ToArray <string>(), recordNumber, resourceType))
{
break;
}
else
{
isValidated = true;
}
}
IEnumerable <string> keyValue = null;
if (cell.CellReference.Value.Length == 2)
{
keyValue = from a in keyValuePairs where a.Value.Take(1).First() == cell.CellReference.Value.Take(1).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 3)
{
keyValue = from a in keyValuePairs where a.Value.Take(2).First() == cell.CellReference.Value.Take(2).First() select a.Key;
}
else if (cell.CellReference.Value.Length == 4)
{
keyValue = from a in keyValuePairs where a.Value.Take(3).First() == cell.CellReference.Value.Take(3).First() select a.Key;
}
if (keyValue.Count() > 0)
{
UpdateFormData(keyValue, cellActualValue, resourceType);
}
}
}
}
if (counter > 0)
{
InsertTopics topic = new InsertTopics();
locations = topic.GetLocations(state, county, city, zipcode);
if (resourceType == Constants.FormResourceType)
{
Form form = new Form()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
form.Validate();
ResourcesList.Add(form);
ClearVariableData();
}
if (resourceType == Constants.OrganizationResourceType)
{
Organization organization = new Organization()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Address = address,
Telephone = telephone,
Overview = overview,
Specialties = specialties,
EligibilityInformation = eligibilityInformation,
Qualifications = qualifications,
BusinessHours = businessHours,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
organization.Validate();
organizationsList.Add(organization);
ClearVariableData();
}
if (resourceType == Constants.OrganizationReview)
{
orgNameList.Add(organizationName);
orgFullNameList.Add(reviewerFullName);
orgTitleList.Add(reviewerTitle);
orgReviewTextList.Add(reviewText);
orgReviewerImageList.Add(reviewerImage);
ClearVariableData();
}
if (resourceType == Constants.ArticleResourceType)
{
Article article = new Article()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
article.Validate();
articlesList.Add(article);
ClearVariableData();
}
if (resourceType == Constants.ArticleContent)
{
articleNameList.Add(articleName);
headlineList.Add(headline);
contentList.Add(content);
ClearVariableData();
}
if (resourceType == Constants.VideoResourceType)
{
Video video = new Video()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceCategory = resourceCategory,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
Overview = overview,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
video.Validate();
ResourcesList.Add(video);
ClearVariableData();
}
if (resourceType == Constants.AdditionalReadingResourceType)
{
AdditionalReading additionalReading = new AdditionalReading()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
additionalReading.Validate();
ResourcesList.Add(additionalReading);
ClearVariableData();
}
if (resourceType == Constants.RelatedLinkResourceType)
{
RelatedLink relatedLink = new RelatedLink()
{
ResourceId = (string.IsNullOrEmpty(id) || string.IsNullOrWhiteSpace(id)) ? Guid.NewGuid() : id,
Name = name,
Description = description,
ResourceType = resourceType,
Url = url,
TopicTags = topicTagIds,
OrganizationalUnit = organizationalUnit,
Location = locations,
CreatedBy = Constants.Admin,
ModifiedBy = Constants.Admin
};
relatedLink.Validate();
ResourcesList.Add(relatedLink);
ClearVariableData();
}
}
counter++;
recordNumber++;
}
}
}
}
foreach (var resourceList in organizationsList)
{
List <OrganizationReviewer> organizationReviewer = new List <OrganizationReviewer>();
OrganizationReviewer orgReviewer = new OrganizationReviewer();
for (int iterator = 0; iterator < orgNameList.Count; iterator++)
{
var na = orgNameList[iterator];
if (resourceList.Name == orgNameList[iterator])
{
orgReviewer = new OrganizationReviewer
{
ReviewerFullName = orgFullNameList[iterator],
ReviewerTitle = orgTitleList[iterator],
ReviewText = orgReviewTextList[iterator],
ReviewerImage = orgReviewerImageList[iterator]
};
organizationReviewer.Add(orgReviewer);
}
}
var serializedResult = JsonConvert.SerializeObject(organizationReviewer);
var orgReviewData = JsonConvert.DeserializeObject(serializedResult);
resourceList.Reviewer = organizationReviewer;
ResourcesList.Add(resourceList);
}
foreach (var articleList in articlesList)
{
List <ArticleContent> articleContentList = new List <ArticleContent>();
ArticleContent articleContents = new ArticleContent();
for (int iterator = 0; iterator < articleNameList.Count; iterator++)
{
var na = articleNameList[iterator];
if (articleList.Name == articleNameList[iterator])
{
articleContents = new ArticleContent
{
Headline = headlineList[iterator],
Content = contentList[iterator],
};
articleContentList.Add(articleContents);
}
}
var serializedResult = JsonConvert.SerializeObject(articleContentList);
var articleContentData = JsonConvert.DeserializeObject(serializedResult);
articleList.Contents = articleContentList;
ResourcesList.Add(articleList);
}
}
catch (Exception ex)
{
InsertTopics.ErrorLogging(ex, recordNumber);
Resources = null;
}
Resources = ResourcesList;
return(Resources);
}
public static System.IO.MemoryStream createExcelReport(DataSet ds)
{
using (System.IO.MemoryStream mem = new System.IO.MemoryStream())
{
SpreadsheetDocument workbook = SpreadsheetDocument.
Create(mem, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = workbook.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
foreach (System.Data.DataTable table in ds.Tables)
{
var sheetPart = workbookpart.AddNewPart <WorksheetPart>();
sheetPart.Worksheet = new Worksheet(new SheetData());
var sheetData = sheetPart.Worksheet.GetFirstChild <SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());// workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
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);
}
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();
if (col.GetType() == typeof(Decimal))
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString()); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);
uint sheetId = 1;
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = relationshipId, SheetId = sheetId, Name = table.TableName
};
sheets.Append(sheet);
}
workbookpart.Workbook.Save();
workbook.Close();
return(mem);
}
}
static public bool ExportarExcel(DataSet ds, string archivo)
{
try
{
using (var workbook = SpreadsheetDocument.Create(archivo, 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() > 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 (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);
}
sheetData.AppendChild(headerRow);
foreach (System.Data.DataRow dsrow in table.Rows)
{
DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
DateTime val;
string dato;
foreach (String col in columns)
{
DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
dato = dsrow[col].ToString();
if (dato.Length >= 10 && DateTime.TryParse(dato, out val) == true)
{
dato = val.ToString("yyyy/MM/dd");
}
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dato); //
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(archivo);
excel.Visible = true;
return(true);
}
catch (Exception ex)
{
Utiles.WriteErrorLog(ex.Message);
return(false);
}
}
