public void TestThatExcelFileDataConstructorSetCorrectValuesOfExcelDataModel()
{
var exceldata = new ExcelData
{
Headers = new List <string>
{
"Header1"
},
DataRows = new List <List <string> >
{
new List <string> {
"Data1"
},
new List <string> {
"Data2"
}
},
SheetName = "Sheet"
};
var excelFileData = new ExcelFileData(exceldata);
Assert.AreEqual(1, excelFileData.Headers.Count);
Assert.AreEqual(2, excelFileData.DataRows.Count);
Assert.AreEqual(1, excelFileData.DataRows[0].Count);
Assert.AreEqual(1, excelFileData.DataRows[1].Count);
Assert.AreEqual("Header1", excelFileData.Headers.First());
Assert.AreEqual("Data1", excelFileData.DataRows[0].First());
Assert.AreEqual("Data2", excelFileData.DataRows[1].First());
Assert.AreEqual("Sheet", excelFileData.SheetName);
}
private void PopulateData(ExcelFileData data, WorksheetPart worksheetPart)
{
// Add header
uint rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
var sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
sheetData.AppendChild(row);
var cellIdex = 0;
foreach (var header in data.Headers)
{
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header ?? string.Empty));
}
if (data.Headers.Count > 0)
{
// Add the column configuration if available
if (data.ColumnConfigurations != null)
{
var columns = (Columns)data.ColumnConfigurations.Clone();
worksheetPart.Worksheet.InsertAfter(columns, worksheetPart.Worksheet.SheetFormatProperties);
}
}
// Add sheet data
foreach (var rowData in data.DataRows)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
foreach (var cellData in rowData)
{
var cell = CreateTextCell(ColumnLetter(cellIdex++),
rowIdex,
cellData ?? string.Empty);
row.AppendChild(cell);
}
}
}
public void SaveToExcel(ExcelFileData data)
{
if (string.IsNullOrEmpty(data.FilePath))
{
throw new ArgumentNullException("filePath");
}
WorksheetPart workSheetPart;
data.SheetName = data.SheetName ?? ExcelData.DefaultSheetName;
if (data.UpdateExistingFile)
{
try
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(data.FilePath, true))
{
workSheetPart = AddWorkSheet(data.SheetName, document.WorkbookPart, data.UpdateExistingFile);
PopulateData(data, workSheetPart);
document.WorkbookPart.Workbook.Save();
}
}
catch (OpenXmlPackageException)
{
throw new FileNotFoundException("File is not found", data.FilePath);
}
}
else
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(data.FilePath, SpreadsheetDocumentType.Workbook))
{
var workBookPart = document.AddWorkbookPart();
workBookPart.Workbook = new Workbook();
workSheetPart = AddWorkSheet(data.SheetName, document.WorkbookPart, data.UpdateExistingFile);
PopulateData(data, workSheetPart);
workBookPart.Workbook.Save();
}
}
}
public void SaveToCsv(ExcelFileData data)
{
try
{
if (string.IsNullOrEmpty(data.FilePath))
{
throw new DataException("Excel file path is not provided");
}
if (!data.UpdateExistingFile)
{
throw new NotSupportedException();
}
if (data.Headers != null)
{
// add headers to a file
var headers = new StringBuilder();
foreach (var header in data.Headers)
{
headers.Append(string.Format(CellFormat, header));
}
using (var w = new StreamWriter(data.FilePath, false))
{
w.WriteLine(headers);
w.Flush();
}
}
if (data.DataRows != null)
{
// add rows by portions in PortionNumber rows to a file
var dataContent = new StringBuilder();
for (int i = 0; i < data.DataRows.Count; i++)
{
var isLastRowInPortion = (i + 1) % PortionNumber == 0 || i == data.DataRows.Count - 1;
for (int j = 0; j < data.DataRows[i].Count; j++)
{
var cellData = data.DataRows[i][j];
if (cellData.IndexOf(',') > 0)
{
cellData = string.Format("\"{0}\"", cellData);
}
if (!isLastRowInPortion && j == data.DataRows[i].Count - 1)
{
dataContent.AppendLine(string.Format(CellFormat, cellData));
}
else
{
dataContent.Append(string.Format(CellFormat, cellData));
}
}
if (isLastRowInPortion)
{
using (var w = new StreamWriter(data.FilePath, true))
{
w.WriteLine(dataContent);
w.Flush();
dataContent = dataContent.Clear();
}
}
}
}
}
catch (IOException ex)
{
throw new IOException("An exception occurs during Excel data writing", ex);
}
}
