private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.ASCII);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, ref writer);
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement();
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
// Create cell with data
if (IsNumericColumn[colInx])
{
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
else if (IsDateColumn[colInx])
{
DateTime dtValue;
string strValue = "";
if (DateTime.TryParse(cellValue, out dtValue))
{
strValue = dtValue.ToShortDateString();
}
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), strValue, ref writer);
}
else
{
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, ref writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
}
public static void ReadCountryData(ZandraUserPreferences userPreferences)
{
ObservableCollection <Country> countries = userPreferences.Countries;
var fileContent = string.Empty;
var filePath = string.Empty;
using (System.Windows.Forms.OpenFileDialog openFileDialog = new OpenFileDialog())
{
openFileDialog.InitialDirectory = "c:\\";
openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog.FilterIndex = 1;
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
//Get the path of specified file
filePath = openFileDialog.FileName;
;
// Open the document for reading.
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, false))
{
bool countryDataFound = false;
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = GetWorksheetPart(workbookPart,
"Consolidated Country Data");
SheetData sheetData = null;
if (worksheetPart != null)
{
countryDataFound = true;
sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
}
if (!countryDataFound)
{
MessageBox.Show("This File Does not contain the correct country data sheet." +
"Please choose a different file.", "Invalid File!");
}
else
{
SharedStringTablePart stringTable =
workbookPart.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
Row r = null;
if (sheetData != null)
{
r = sheetData.Elements <Row>().Where(row => row.RowIndex == 1).First();
}
string nameColumn = null;
string numberColumn = null;
string codeColumn = null;
string tailNumberColumn = null;
string nationalityColumn = null;
//Get Column Letter
char[] ch1 = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
foreach (Cell c in r.Elements <Cell>())
{
string cellvalue = stringTable.SharedStringTable
.ElementAt(int.Parse(c.CellValue.InnerText)).InnerText;
if (cellvalue == "Country")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
nameColumn = tempString.Substring(0, index);
}
else if (cellvalue == "3-Letter")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
codeColumn = tempString.Substring(0, index);
}
else if (cellvalue == "ISO 3166-1 Number")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
numberColumn = tempString.Substring(0, index);
}
else if (cellvalue == "Nationality")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
nationalityColumn = tempString.Substring(0, index);
}
else if (cellvalue == "Tail Number Prefix")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
tailNumberColumn = tempString.Substring(0, index);
}
}
if (nameColumn != null ||
numberColumn != null ||
codeColumn != null ||
tailNumberColumn != null ||
nationalityColumn != null)
{
countries.Clear();
foreach (Row row in sheetData)
{
if (row.RowIndex > 1)
{
string referenceString = nameColumn + row.RowIndex;
if (sheetData.Descendants <Cell>().
Where(c => c.CellReference == referenceString).
FirstOrDefault().CellValue.Text != null &&
sheetData.Descendants <Cell>().
Where(c => c.CellReference == referenceString).
FirstOrDefault().CellValue.Text != "")
{
string cellValue;
Cell cell;
referenceString = nameColumn + row.RowIndex;
string countryName;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
countryName = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
countryName = cellValue;
}
referenceString = codeColumn + row.RowIndex;
string countryCode;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType == null)
{
countryCode = null;
}
else if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
countryCode = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
countryCode = cellValue;
}
referenceString = nationalityColumn + row.RowIndex;
string nationality;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType == null)
{
nationality = null;
}
else if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
nationality = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
nationality = cellValue;
}
referenceString = tailNumberColumn + row.RowIndex;
string tailPrefix;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType == null)
{
tailPrefix = cell.InnerText;
}
else if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
tailPrefix = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
tailPrefix = cellValue;
}
referenceString = numberColumn + row.RowIndex;
string countryNumber;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType == null)
{
countryNumber = cell.InnerText;
}
else if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
countryNumber = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
countryNumber = cellValue;
}
uint countryISONumber = (uint)Int32.Parse(countryNumber);
if (tailPrefix == "#N/A" || tailPrefix == "")
{
tailPrefix = null;
}
if (nationality == "#N/A" ||
nationality == "0" ||
nationality == "" ||
nationality == null)
{
nationality = null;
}
countries.Add(new Country(
countryName,
countryCode,
nationality,
tailPrefix,
countryISONumber));
}
}
}
userPreferences.SaveMe();
}
else
{
MessageBox.Show("This File Does not contain the correct ICAO Aifield data sheet." +
"Please choose a different file.", "Invalid File!");
}
}
}
}
}
}
public bool WriteDataCollectors(IEnumerable <DataCollector> dataCollectors, Stream stream)
{
var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbook = document.AddWorkbookPart();
workbook.Workbook = new Workbook();
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
// Create a sheet in the document
var data = new SheetData();
WorksheetPart worksheet = workbook.AddNewPart <WorksheetPart>();
worksheet.Worksheet = new Worksheet(data);
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheet), SheetId = 1, Name = "Case Reports"
};
sheets.Append(sheet);
uint rowIndex = 0;
// Add some headers
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var headers = new SortedDictionary <string, string>
{
{ "A", "Full Name" },
{ "B", "Display Name" },
{ "C", "Year of birth" },
{ "D", "Sex" },
{ "E", "PreferredLanguage" },
{ "F", "Lat. / Long." },
{ "G", "Region" },
{ "H", "District" },
{ "I", "Village" },
{ "J", "Phonenumbers" },
{ "K", "Registered at" },
{ "L", "Last Report Recieved At" }
};
foreach (var header in headers)
{
var cell = new Cell {
CellReference = header.Key + rowIndex
};
row.Append(cell);
cell.DataType = new EnumValue <CellValues>(CellValues.String);
cell.CellValue = new CellValue(header.Value);
}
}
// Insert data
foreach (var dataCollector in dataCollectors.OrderBy(e => e.RegisteredAt))
{
var row = new Row {
RowIndex = ++rowIndex
};
data.Append(row);
var fullName = new Cell {
CellReference = "A" + rowIndex
};
row.Append(fullName);
fullName.DataType = new EnumValue <CellValues>(CellValues.String);
fullName.CellValue = new CellValue(dataCollector.FullName);
var displayName = new Cell {
CellReference = "B" + rowIndex
};
row.Append(displayName);
displayName.DataType = new EnumValue <CellValues>(CellValues.String);
displayName.CellValue = new CellValue(dataCollector.DisplayName);
var yearOfBirth = new Cell {
CellReference = "C" + rowIndex
};
row.Append(yearOfBirth);
yearOfBirth.DataType = new EnumValue <CellValues>(CellValues.Number);
yearOfBirth.CellValue = new CellValue(dataCollector.YearOfBirth.ToString());
var sex = new Cell {
CellReference = "D" + rowIndex
};
row.Append(sex);
sex.DataType = new EnumValue <CellValues>(CellValues.String);
sex.CellValue = new CellValue(Enum.GetName(typeof(Sex), dataCollector.Sex));
var preferredLanguage = new Cell {
CellReference = "E" + rowIndex
};
row.Append(preferredLanguage);
preferredLanguage.DataType = new EnumValue <CellValues>(CellValues.String);
preferredLanguage.CellValue = new CellValue(Enum.GetName(typeof(Language), dataCollector.PreferredLanguage));
var location = new Cell {
CellReference = "F" + rowIndex
};
row.Append(location);
location.DataType = new EnumValue <CellValues>(CellValues.String);
location.CellValue = new CellValue(dataCollector.Location.Latitude.ToString() + ", " + dataCollector.Location.Longitude.ToString());
var region = new Cell {
CellReference = "G" + rowIndex
};
row.Append(region);
region.DataType = new EnumValue <CellValues>(CellValues.String);
region.CellValue = new CellValue(dataCollector.Region ?? "Unknown");
var district = new Cell {
CellReference = "H" + rowIndex
};
row.Append(district);
district.DataType = new EnumValue <CellValues>(CellValues.String);
district.CellValue = new CellValue(dataCollector.District ?? "Unknown");
var village = new Cell {
CellReference = "I" + rowIndex
};
row.Append(village);
village.DataType = new EnumValue <CellValues>(CellValues.String);
village.CellValue = new CellValue(dataCollector.Village ?? "Unknown");
var phoneNumbers = new Cell {
CellReference = "J" + rowIndex
};
row.Append(phoneNumbers);
phoneNumbers.DataType = new EnumValue <CellValues>(CellValues.String);
phoneNumbers.CellValue = new CellValue(string.Join(", ", dataCollector.PhoneNumbers.Select(pn => pn.Value)));
var registeredAt = new Cell {
CellReference = "K" + rowIndex
};
row.Append(registeredAt);
registeredAt.DataType = new EnumValue <CellValues>(CellValues.Date);
registeredAt.CellValue = new CellValue(dataCollector.RegisteredAt);
var lastReportRecievedAt = new Cell {
CellReference = "L" + rowIndex
};
row.Append(lastReportRecievedAt);
lastReportRecievedAt.DataType = new EnumValue <CellValues>(CellValues.Date);
lastReportRecievedAt.CellValue = new CellValue(DateTimeOffset.UtcNow /* TODO: dataCollector.LastReportRecievedAt ?? dataCollector.RegisteredAt*/);
}
// Save the document in memory, and serve to client
workbook.Workbook.Save();
document.Close();
return(true);
}
public void LoadSpreadsheet(int worksheetId, string fileName)
{
// Create new stopwatch
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
// Begin timing
stopwatch.Start();
string cellAddress = "";
string sheetName = Path.GetFileName(fileName).Replace(".xlsx", "");
sheetName = Path.GetFileName(fileName).Replace(".xls", "");
savedCells = new Dictionary <string, Cell>();
allCells = new Dictionary <string, Cell>();
string workbookPath = fileName;
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
{
var sheets = spreadsheetDocument.WorkbookPart.Workbook.Descendants <Sheet>();
int?colIndex = 0;
foreach (Sheet sheet in sheets)
{
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(sheet.Id);
DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet = worksheetPart.Worksheet;
SharedStringTablePart sharedStringTablePart1;
if (spreadsheetDocument.WorkbookPart.GetPartsCountOfType
<SharedStringTablePart>() > 0)
{
sharedStringTablePart1 =
spreadsheetDocument.WorkbookPart.GetPartsOfType
<SharedStringTablePart>().First();
}
else//This is only needed to compile. If we don't have string table that means there are no strings
{
sharedStringTablePart1 =
spreadsheetDocument.WorkbookPart.AddNewPart
<SharedStringTablePart>();
}
var rows = worksheet.GetFirstChild <SheetData>().Elements <Row>();
foreach (var r in rows)
{
if (r.RowIndex <= 30)//30 rows
{
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell rCell in r.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
colIndex = GetColumnIndex(rCell.CellReference.ToString());
if (colIndex <= 12)
{
Cell c = new Cell();
cellAddress = rCell.CellReference.ToString().Replace("$", "");
c.readOnly = true;//until updated
c.isDerived = false;
c.row = int.Parse(r.RowIndex.ToString());
c.column = (int)colIndex;
c.cellAddress = cellAddress;
c.value = rCell.CellValue.Text;
if (cellAddress == "A3")
{
Console.WriteLine("A3");
}
//Add Formulas
if (rCell.CellFormula != null)
{
if (rCell.CellFormula.SharedIndex != null)
{
c.sharedForumlaIndex =
int.Parse(rCell.CellFormula.SharedIndex.ToString());
if (rCell.CellFormula.Text != "")
{
c.isSharedForumla = true;
}
}
c.fullFormula = rCell.CellFormula.Text;
c.formula = "=" + //Add =
(string)rCell.CellFormula.Text.Replace("$", ""); //Remove any $
string regex = @"\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b([:\s]\$?(?:\bXF[A-D]|X[A-E][A-Z]|[A-W][A-Z]{2}|[A-Z]{2}|[A-Z])\$?(?:104857[0-6]|10485[0-6]\d|1048[0-4]\d{2}|104[0-7]\d{3}|10[0-3]\d{4}|[1-9]\d{1,5}|[1-9])d?\b)?";
Match match = Regex.Match(c.formula, regex,
RegexOptions.IgnoreCase);
while (match.Success && c.formula.IndexOf(":") != -1)
{
if (match.Groups[0].Value.IndexOf(":") != -1)
{
//// Finally, we get the Group value and display it.
string key = match.Groups[0].Value;
string cellRef = key.Replace("$", "");
string newCells = RangeToSeries(key);
c.formula = c.formula.Replace(key, newCells);
}
match = Regex.Match(c.formula, regex,
RegexOptions.IgnoreCase);
}
}
else //String (use shared string table)
{
c.value = sharedStringTablePart1.SharedStringTable.ChildElements[int.Parse(c.value)].InnerText;
}
allCells.Add(cellAddress, c);
}
}
}
}
}
Console.WriteLine();
}
ReplaceSharedFormulas(ref allCells);
Console.WriteLine("Loaded from Excel:" + stopwatch.Elapsed);
//while (savedCells.Count < 360)
//{
foreach (Cell rCell in allCells.Values)
{
if (!savedCells.ContainsKey(rCell.cellAddress))
{
if (rCell.value == null) //Blank?
{
savedCells.Add(rCell.cellAddress, rCell);
}
else if (rCell.formula == null) //Input
{
rCell.readOnly = true; //until updated (May be label, May be input)
rCell.isDerived = false;
savedCells.Add(rCell.cellAddress, rCell);
}
else //Formula
{
bool allCellsHere = true;
foreach (string s in GetCellReferences((string)rCell.formula))
{
if (!savedCells.ContainsKey(s))
{
allCellsHere = false;
break;
}
else
{
if (savedCells[s].formula == null) //Is an input
{
savedCells[s].empty = false;
savedCells[s].readOnly = false;
savedCells[s].hasDependent = true;
}
//c.empty = false;//this cell impacts others
}
}
if (allCellsHere)
{
rCell.formula = rCell.formula.Replace("$", "");
rCell.readOnly = true;
rCell.isDerived = true;
savedCells.Add(rCell.cellAddress, rCell);
}
}
}
}
//}
Console.WriteLine("Organized Cells:" + stopwatch.Elapsed);
Dictionary <string, Cell> savedCellsTrimmed = new Dictionary <string, Cell>();
List <int> rowsToRemove = new List <int>();
bool includeRow = false;
for (int k = 30; k > 0; k--)
{
foreach (Cell c in savedCells.Values)
{
if (c.row == k)
{
if (c.value != "" || c.hasDependent)
{
includeRow = true;
break; //include row;
}
}
}
if (includeRow)
{
break;
}
else
{
rowsToRemove.Add(k);
}
}
foreach (Cell c in savedCells.Values)
{
if (!rowsToRemove.Contains(c.row))
{
savedCellsTrimmed.Add(c.cellAddress, c);
}
}
savedCells = savedCellsTrimmed;
DataTable dt = new DataTable();
dt.Columns.Add("worksheet_id");
dt.Columns.Add("row");
dt.Columns.Add("column");
dt.Columns.Add("cell_address");
dt.Columns.Add("value");
dt.Columns.Add("formula");
dt.Columns.Add("data_type");
dt.Columns.Add("format");
dt.Columns.Add("read_only");
dt.Columns.Add("is_derived");
dt.Columns.Add("has_dependent");
dt.Columns.Add("fore_color");
dt.Columns.Add("back_color");
dt.Columns.Add("font_family");
dt.Columns.Add("italics");
dt.Columns.Add("bold");
dt.Columns.Add("underline");
dt.Columns.Add("font_size");
foreach (Cell c in savedCells.Values)
{
DataRow row = dt.NewRow();
row[0] = worksheetId;
row[1] = c.row;
row[2] = c.column;
row[3] = c.cellAddress;
row[4] = c.value;
row[5] = c.formula;
row[6] = c.dataType;
row[7] = c.format;
row[8] = c.readOnly;
row[9] = c.isDerived;
row[10] = c.hasDependent;
row[11] = c.ForeColor;
row[12] = c.BackColor;
row[13] = c.FontFamily;
row[14] = c.Italics;
row[15] = c.Bold;
row[16] = c.Underline;
row[17] = c.FontSize;
dt.Rows.Add(row);
}
SqlConnection cn = new SqlConnection(ConnectionString);
cn.Open();
using (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add(0, 0);
copy.ColumnMappings.Add(1, 1);
copy.ColumnMappings.Add(2, 2);
copy.ColumnMappings.Add(3, 3);
copy.ColumnMappings.Add(4, 4);
copy.ColumnMappings.Add(5, 5);
copy.ColumnMappings.Add(6, 6);
copy.ColumnMappings.Add(7, 7);
copy.ColumnMappings.Add(8, 8);
copy.ColumnMappings.Add(9, 9);
copy.ColumnMappings.Add(10, 10);
copy.ColumnMappings.Add(11, 11);
copy.ColumnMappings.Add(12, 12);
copy.ColumnMappings.Add(13, 13);
copy.ColumnMappings.Add(14, 14);
copy.ColumnMappings.Add(15, 15);
copy.ColumnMappings.Add(16, 16);
copy.ColumnMappings.Add(17, 17);
copy.DestinationTableName = "worksheet_cell";
copy.WriteToServer(dt);
}
Console.WriteLine("Loaded DB:" + stopwatch.Elapsed);
}
public static void Write(string fileName, Workbook workbook)
{
if (fileName == null)
{
throw new ArgumentNullException("fileName");
}
if (workbook == null)
{
throw new ArgumentNullException("workbook");
}
FileInfo fi = new FileInfo(fileName);
if (fi.Exists)
{
fi.Delete();
}
// create the blank workbook
char[] base64CharArray = _EmptyXlsx
.Where(c => c != '\r' && c != '\n').ToArray();
byte[] byteArray =
System.Convert.FromBase64CharArray(base64CharArray,
0, base64CharArray.Length);
File.WriteAllBytes(fi.FullName, byteArray);
// open the workbook, and create the TableProperties sheet, populate it
using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(fi.FullName, true))
{
WorkbookPart workbookPart = sDoc.WorkbookPart;
XDocument wXDoc = workbookPart.GetXDocument();
XElement sheetElement = wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Where(s => (string)s.Attribute(SSNoNamespace.name) == "Sheet1")
.FirstOrDefault();
if (sheetElement == null)
{
throw new SpreadsheetWriterInternalException();
}
string id = (string)sheetElement.Attribute(R.id);
sheetElement.Remove();
workbookPart.PutXDocument();
WorksheetPart sPart = (WorksheetPart)workbookPart.GetPartById(id);
workbookPart.DeletePart(sPart);
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
vector.SetAttributeValue(SSNoNamespace.size, 0);
XElement lpstr = vector.Element(VT.lpstr);
lpstr.Remove();
}
XElement vector2 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.FirstOrDefault();
XElement variant = vector2
.Descendants(VT.i4)
.FirstOrDefault();
if (variant != null)
{
variant.Value = "1";
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
if (workbook.Worksheets != null)
{
foreach (var worksheet in workbook.Worksheets)
{
AddWorksheet(sDoc, worksheet);
}
}
workbookPart.WorkbookStylesPart.PutXDocument();
}
}
public List <Person> Load_sheet()
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Open(file_path, false))
{
WorkbookPart workbookPart = doc.WorkbookPart;
List <Person> people = new List <Person>();
List <string> collected_strings = new List <string>();
Sheet theSheet = workbookPart.Workbook.Descendants <Sheet>().FirstOrDefault();
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
WorksheetPart wsPart =
(WorksheetPart)(workbookPart.GetPartById(theSheet.Id));
var rows = wsPart.Worksheet.Descendants <DocumentFormat.OpenXml.Spreadsheet.Row>();
foreach (var row in rows)
{
collected_strings.Clear();
foreach (DocumentFormat.OpenXml.Spreadsheet.Cell c in row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>())
{
string cellValue = string.Empty;
if (c.DataType != null)
{
if (c.DataType == CellValues.SharedString)
{
int id = -1;
if (Int32.TryParse(c.InnerText, out id))
{
SharedStringItem item = GetSharedStringItemById(workbookPart, id);
if (item.Text != null)
{
cellValue = item.Text.Text;
}
else if (item.InnerText != null)
{
cellValue = item.InnerText;
}
else if (item.InnerXml != null)
{
cellValue = item.InnerXml;
}
}
}
}
else
{
DateTime start_date = new DateTime(1900, 1, 1);
try
{
int days = Convert.ToInt32(c.CellValue.Text);
start_date = start_date.AddDays(days - 2);
string str_date = start_date.ToString("d.MM.yyyy");
cellValue = str_date;
}
catch
{
cellValue = null;
}
}
collected_strings.Add(cellValue);
}
people.Add(new Person(collected_strings));
}
people.RemoveAt(0);
return(people);
}
}
/// <summary>
/// Считывание данных из потока xlsx в объект DataTable
/// </summary>
/// <param name="s">Stream xlsx</param>
/// <returns></returns>
public static DataSet FromStream(Stream s)
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();
ds.Tables.Add(dt);
try
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(s, false))
{
// Получение части книги SpreadSheetDocument
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
// Получение всех листов SpreadSheetDocument
IEnumerable <Sheet> sheetcollection = spreadsheetDocument.WorkbookPart.Workbook.GetFirstChild <Sheets>().Elements <Sheet>();
// Получение идентификатора отношения
string relationshipId = sheetcollection.First().Id.Value;
// Получение части листа 1 SpreadSheetDocument
WorksheetPart worksheetPart = (WorksheetPart)spreadsheetDocument.WorkbookPart.GetPartById(relationshipId);
// Получение данных из файла Excel
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
IEnumerable <Row> rowcollection = sheetData.Elements <Row>();
if (rowcollection.Count() == 0)
{
ds = null;
return(ds);
}
// Добавление столбцов. Для начала 10. При заполнении строк столбцы могут быть добавлены
for (int ci = 0; ci < 10; ci++)
{
dt.Columns.Add(String.Format("Column{0}", ci), typeof(String));
}
// Добавление строк в DataTable
foreach (Row row in rowcollection)
{
DataRow temprow = dt.NewRow();
int ci = 0;
foreach (Cell cell in row.Elements <Cell>())
{
// Получение индекса столбца ячейки
int cellColumnIndex = GetColumnIndex(GetColumnName(cell.CellReference));
// если ячейки не подряд - добавляем пустые ячейки
if (ci < cellColumnIndex)
{
do
{
temprow[ci] = string.Empty;
ci++;
}while (ci < cellColumnIndex);
}
// если ячейка из колонки которой ещё не - добавляем колонки
if (ci >= dt.Columns.Count)
{
do
{
dt.Columns.Add(String.Format("Column{0}", ci), typeof(String));
}while (ci >= dt.Columns.Count);
}
temprow[ci] = GetValueOfCell(spreadsheetDocument, cell);
ci++;
}
// Добавление строки в DataTable
// строки содержат строку заголовка
dt.Rows.Add(temprow);
}
}
}
catch (Exception e) { ds = null; throw new Exception("Ошибка при разборе потока xlsx.", e); }
return(ds);
}
/// <summary>
/// Save the styl for worksheet headers.
/// </summary>
/// <param name="cellLocation">Cell location.</param>
/// <param name="spreadSheet">Spreadsheet to change.</param>
/// <param name="workSheetPart">Worksheet to change.</param>
private static void SeatHeaderStyle(string cellLocation,
SpreadsheetDocument spreadSheet, WorksheetPart workSheetPart)
{
Stylesheet styleSheet =
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet;
Cell cell = workSheetPart.Worksheet.Descendants<Cell>().Where(
c => c.CellReference == cellLocation).FirstOrDefault();
if (cell == null)
{
throw new ArgumentNullException("Cell not found");
}
cell.SetAttribute(new OpenXmlAttribute("", "s", "", "1"));
OpenXmlAttribute cellStyleAttribute = cell.GetAttribute("s", "");
CellFormats cellFormats =
spreadSheet.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;
// pick the first cell format.
CellFormat cellFormat = (CellFormat)cellFormats.ElementAt(0);
CellFormat cf = new CellFormat(cellFormat.OuterXml);
cf.FontId = styleSheet.Fonts.Count;
cf.FillId = styleSheet.Fills.Count;
cellFormats.AppendChild(cf);
int a = (int)styleSheet.CellFormats.Count.Value;
cell.SetAttribute(cellStyleAttribute);
cell.StyleIndex = styleSheet.CellFormats.Count;
workSheetPart.Worksheet.Save();
}
/// <summary>
/// Write the content of the <see cref="ExcelMapCoOrdinateContainer"> to the worksheet</see>.
/// </summary>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="worksheetPart">The worksheet part.</param>
/// <param name="mapCoOrdinateContainer">The map co ordinate container.</param>
/// <param name="stylesManager">The styles manager.</param>
/// <param name="spreadsheetDocument">The spreadsheet document.</param>
public static void WriteMapToExcel(string sheetName,
WorksheetPart worksheetPart,
ExcelMapCoOrdinateContainer mapCoOrdinateContainer,
ExcelStylesManager stylesManager,
SpreadsheetDocument spreadsheetDocument)
{
TempDiagnostics.Output(string.Format("Writing map for ExcelMap[{0}] to worksheet '{1}'", mapCoOrdinateContainer.ContainerType, sheetName));
var dimensionConverter = new ExcelDimensionConverter("Calibri", 11.0f);
// Manages the writing to Excel.
var excelWriteManager = new OpenXmlExcelWriteManager(worksheetPart.Worksheet);
//** First we need to clear the destination worksheet down (rows, columns and merged areas)
excelWriteManager.EmptyWorksheet();
// Build up Columns models on all nested containers.
RowOrColumnsModel columnsModel = mapCoOrdinateContainer.BuildColumnsModel();
int colCount = columnsModel.Count();
// ==========================================================
// Traverse each column assigning start and end
// column indexes to the maps associated with that column.
// ==========================================================
RowOrColumnInfo columnInfo = columnsModel.First;
while (columnInfo != null)
{
double?width = columnInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.WidthToOpenXmlWidth(columnInfo.HeightOrWidth.Value) : null;
excelWriteManager.AddColumn(width, columnInfo.Hidden);
// Assign Excel start and end columns to maps for merge operations.
foreach (ExcelMapCoOrdinate map in columnInfo.Maps)
{
// Extend any cells that are merged across maps.
if (map is ExcelMapCoOrdinatePlaceholder)
{
var cell = map as ExcelMapCoOrdinatePlaceholder;
if (cell.MergeWith != null)
{
cell.ExcelColumnStart = cell.MergeWith.ExcelColumnStart;
cell.MergeWith.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol
}
}
if (map.ExcelColumnStart == 0)
{
map.ExcelColumnStart = columnInfo.ExcelIndex; //excelCol;
map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol;
}
else
{
map.ExcelColumnEnd = columnInfo.ExcelIndex; //excelCol;
}
}
// Move on to next column in list
columnInfo = columnInfo.Next;
}
TempDiagnostics.Output(string.Format("Created ColumnsModel which contains '{0}' columns", colCount));
// Build up Rows models on all nested containers.
RowOrColumnsModel rowsModel = mapCoOrdinateContainer.BuildRowsModel();
int rowCount = rowsModel.Count();
// ==========================================================
// Traverse each row assigning start and end
// row indexes to the maps associated with that row.
// ==========================================================
RowOrColumnInfo rowInfo = rowsModel.First;
while (rowInfo != null)
{
double?height = rowInfo.HeightOrWidth.HasValue ? (double?)dimensionConverter.HeightToOpenXmlHeight(rowInfo.HeightOrWidth.Value) : null;
excelWriteManager.AddRow(height, rowInfo.Hidden);
// Assign Excel start and end rows to maps for merge operations.
foreach (ExcelMapCoOrdinate map in rowInfo.Maps)
{
if (map.ExcelRowStart == 0)
{
map.ExcelRowStart = rowInfo.ExcelIndex; //excelRow;
map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow;
}
else
{
map.ExcelRowEnd = rowInfo.ExcelIndex; //excelRow;
}
}
// Move on to next Row in list
rowInfo = rowInfo.Next;
}
TempDiagnostics.Output(string.Format("Created RowsModel which contains '{0}' rows", rowCount));
// Build a layered cells dictionary for all cells, keyed by Excel row and column index
var layeredCellsDictionary = new LayeredCellsDictionary();
mapCoOrdinateContainer.UpdateLayeredCells(ref layeredCellsDictionary);
TempDiagnostics.Output(string.Format("Updated Layered Cell Information for worksheet '{0}' = {1}", sheetName, layeredCellsDictionary.Count));
// Probe the Row, Column and Cell Layered maps, embellishing them with row, column and cell formatting information.
for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
{
for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
{
// We can now use the layeredCellsDictionary to build the
// excel workbook based on layered cell information
var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
LayeredCellInfo layeredCellInfo = layeredCellsDictionary[currentCoOrdinate];
// Work through the layered maps to determine what needs to be written to the Excel worksheet at that Row/Column
ProcessLayeredCellMaps(currentCoOrdinate, layeredCellInfo);
}
}
TempDiagnostics.Output(string.Format("Built Worksheet CellInfos[Cols={0},Rows={1}]", colCount, rowCount));
//** Write the 2D array of cell information to the Excel Worksheet
//** building a list of areas that are to be merged.
for (uint worksheetRow = 1; worksheetRow <= rowCount; worksheetRow++)
{
OpenXmlSpreadsheet.Row row = excelWriteManager.GetRow(worksheetRow);
for (uint worksheetCol = 1; worksheetCol <= colCount; worksheetCol++)
{
// Pluck out information relating to the current cell
var currentCoOrdinate = new System.Drawing.Point((int)worksheetCol, (int)worksheetRow);
ExcelCellInfo cellInfo = layeredCellsDictionary[currentCoOrdinate].CellInfo;
// Not sure if we need this as column letters are easily (quickly) translated using existing OpenXml helpers
string columnLetter = excelWriteManager.GetColumnLetter(worksheetCol);
// All merge cells should have the same style as the source merge cell.
if (cellInfo.MergeFrom != null)
{
// If MergeFrom is non-null, then this cell has been already been marked as a merge cell,
// whose style is to be the same as the source 'MergeFrom cell.
// Update the source (MergeFrom) cell so it ends up containing a reference to the last (MergeTo) cell to be merged.
cellInfo.MergeFrom.MergeTo = cellInfo;
// Create/lookup styles in the target workbook and return index of created style
uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.MergeFrom.StyleInfo);
// Write in to Excel (style information only)
var cell = new OpenXmlSpreadsheet.Cell();
cell.SetCellReference(columnLetter, worksheetRow);
cell.StyleIndex = cellStyleIndex;
row.Append(cell);
cellInfo.Cell = cell;
}
else
{
// Create/lookup styles in the target workbook and return index of created style
uint cellStyleIndex = stylesManager.GetOrCreateStyle(cellInfo.StyleInfo);
// NB! If we write a Null value then we lose cell formatting information for some reason
object value = cellInfo.Value == null ? string.Empty : cellInfo.Value;
// Write in to Excel
var cell = new OpenXmlSpreadsheet.Cell();
cell.SetCellReference(columnLetter, worksheetRow);
excelWriteManager.SetCellValue(cell, value);
cell.StyleIndex = cellStyleIndex;
row.Append(cell);
cellInfo.Cell = cell;
}
// Span the cell accross it's parent columns if specified
if (cellInfo.LastSpanRow == 0)
{
cellInfo.LastSpanRow = worksheetRow;
}
if (cellInfo.LastSpanColumn == 0)
{
cellInfo.LastSpanColumn = worksheetCol;
}
// Merge cells if required (spanning)
for (uint rowIdx = worksheetRow; rowIdx <= cellInfo.LastSpanRow; rowIdx++)
{
for (uint colIdx = worksheetCol; colIdx <= cellInfo.LastSpanColumn; colIdx++)
{
// Mark processed (so we don't over-write)
var coOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
if (coOrdinate != currentCoOrdinate)
{
var mergeCoOrdinate = new System.Drawing.Point((int)colIdx, (int)rowIdx);
ExcelCellInfo mergeCellInfo = layeredCellsDictionary[mergeCoOrdinate].CellInfo;
if (mergeCellInfo.MergeFrom == null)
{
mergeCellInfo.MergeFrom = cellInfo;
}
}
}
}
}
}
//#if DEBUG
// if (!skipMerge)
// {
//#endif
// Merge cells that have been marked to merge in the cellInfos dictionary.
MergeCells(worksheetPart.Worksheet, (uint)rowCount, (uint)colCount, layeredCellsDictionary);
//#if DEBUG
// }
//#endif
TempDiagnostics.Output(string.Format("Written CellInfos[Cols={0},Rows={1}] to Excel", colCount, rowCount));
// Create a list of all of the the defined names in the container.
var definedNameList = new List <ExcelDefinedNameInfo>();
mapCoOrdinateContainer.UpdateDefinedNameList(ref definedNameList, sheetName);
// And write into Excel workbook
foreach (var definedNameInfo in definedNameList)
{
uint rangeColumnCount = definedNameInfo.EndColumnIndex - definedNameInfo.StartColumnIndex + 1;
uint rangeRowCount = definedNameInfo.EndRowIndex - definedNameInfo.StartRowIndex + 1;
if (rangeRowCount > 0 && rangeColumnCount > 0)
{
spreadsheetDocument.WorkbookPart.Workbook.AddDefinedName(sheetName,
definedNameInfo.DefinedName,
(uint)definedNameInfo.StartColumnIndex,
(uint)definedNameInfo.StartRowIndex,
(int)rangeColumnCount,
(int)rangeRowCount);
}
}
TempDiagnostics.Output(string.Format("Defined Names added - write map for {0} complete...", mapCoOrdinateContainer.ContainerType));
}
/// <summary>
/// This method exports datatable to a excel file
/// </summary>
/// <param name="table">DataTable</param>
/// <param name="exportFile">Excel file name</param>
private static void ExportDataTableToExcel(DataTable table, string exportFile)
{
try
{
// Create a spreadsheet document by supplying the filepath.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(exportFile, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
SheetData data = worksheetPart.Worksheet.GetFirstChild <SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
/// <summary>
/// Write values to the spreadsheet.
/// </summary>
/// <param name="cellLocation">Row Column Value.</param>
/// <param name="strValue">Value to write.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteValues(string cellLocation,
string strValue, SpreadsheetDocument spreadSheet,
WorksheetPart workSheet)
{
WorksheetWriter workSheetWriter =
new WorksheetWriter(spreadSheet, workSheet);
int intValue = 0;
if (strValue.Contains("$"))
{
strValue = strValue.Replace("$", "");
strValue = strValue.Replace(",", "");
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (int.TryParse(strValue, out intValue))
{
workSheetWriter.PasteValue(cellLocation, strValue, CellValues.Number);
}
else if (string.IsNullOrEmpty(strValue))
{
workSheetWriter.PasteText(cellLocation, strValue);
}
else
{
workSheetWriter.PasteText(cellLocation, strValue);
}
}
/// <summary>
/// Write the excel rows for the spreadsheet.
/// </summary>
/// <param name="rowData">Excel row values.</param>
/// <param name="rowDataKeys">Excel row-key values.</param>
/// <param name="rowNum">Row number.</param>
/// <param name="maxWidth">Max width.</param>
/// <param name="spreadSheet">Spreadsheet to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteRowsFromKeys(IList rowData,
string[] rowDataKeys, int rowNum, out int maxWidth,
SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
maxWidth = 0;
foreach (object row in rowData)
{
int colNum = 0;
foreach (string rowKey in rowDataKeys)
{
var rowValue = row.GetType().GetProperty(rowKey).GetValue(row, null);
string strValue = rowValue == null ? "" : rowValue.ToString();
//row.GetType().GetProperty(rowKey).GetValue(row, null).ToString();
strValue = ReplaceSpecialCharacters(strValue);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
string cellLocation = string.Format("{0}{1}",
GetColumnLetter(colNum.ToString()), rowNum);
ExcelDocument.WriteValues(cellLocation, strValue,
spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
}
/// <summary>
/// Write the excel headers for the spreadsheet.
/// </summary>
/// <param name="headerData">Excel header values.</param>
/// <param name="rowNum">Row number.</param>
/// <param name="colNum">Column Number.</param>
/// <param name="maxWidth">Max column width</param>
/// <param name="spreadSheet">Maximum Column Width to write to. </param>
/// <param name="workSheet">Worksheet to write to. </param>
private static void WriteHeaders(string[] headerData, out int rowNum,
out int colNum, out int maxWidth,
SpreadsheetDocument spreadSheet, WorksheetPart workSheet)
{
rowNum = 1;
colNum = 0;
maxWidth = 0;
foreach (string header in headerData)
{
string strValue = ReplaceSpecialCharacters(header);
string cellLocation = string.Format("{0}{1}",
GetColumnLetter(colNum.ToString()), rowNum);
maxWidth = strValue.Length > maxWidth ? strValue.Length : maxWidth;
ExcelDocument.WriteValues(cellLocation, strValue, spreadSheet, workSheet);
SeatHeaderStyle(cellLocation, spreadSheet, workSheet);
colNum++;
}
rowNum++;
}
public override void Export(string filename, LayoutList list)
{
using (SpreadsheetDocument xl = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = xl.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
//add styles
WorkbookStylesPart wbsp = workbookpart.AddNewPart <WorkbookStylesPart>();
wbsp.Stylesheet = CreateStylesheet();
wbsp.Stylesheet.Save();
// Add a SharedStringTablePart to the WorkbookPart.
var stringPart = workbookpart.AddNewPart <SharedStringTablePart>();
var stringTable = new StringKeyList();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sd = new SheetData();
Worksheet worksheet = new Worksheet(sd);
worksheetPart.Worksheet = worksheet;
// Add Sheets to the Workbook.
Sheets sheets = xl.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = xl.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "DataSheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
//List<ILayoutItem> cols = LayoutMapTool.GetVisibleItems(list.ListInfo.Columns);
//columns
ExpMapLayout(sd, list.ListInfo.Columns, 0, 1, out int mc, out int ind, null, null, stringTable);
//data
if (list.ListInfo.GroupVisible)
{
foreach (LayoutGroup g in list.Groups)
{
ind++;
Cell cell = GetCell(g.TextValue, 0, (int)ind, 8, stringTable);
GetRow(sd, ind, false).Append(cell);
MergeCells mcells = GetMergeCells(worksheet);
MergeCell mcell = new MergeCell()
{
Reference = new StringValue(cell.CellReference + ":" + Helper.IntToChar(mc) + (ind).ToString())
};
mcells.Append(mcell);
for (int i = g.IndexStart; i <= g.IndexEnd; i++)
{
ind++;
ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, list.ListSource[i], stringTable);
}
}
}
else
{
foreach (object o in list.ListSource)
{
ind++;
ExpMapLayout(sd, list.ListInfo.Columns, 0, ind, out mc, out ind, list, o, stringTable);
}
}
worksheet.Save();
OpenXmlValidator validator = new OpenXmlValidator();
var errors = validator.Validate(xl);
StringBuilder sb = new StringBuilder();
foreach (var error in errors)
{
sb.AppendLine(error.Description);
sb.AppendLine(error.Path.XPath.ToString());
sb.AppendLine();
}
if (sb.Length > 0)
{
//System.Windows.Forms.MessageDialog.ShowMessage(sb.ToString());
}
xl.Close();
}
}
/// <summary>
/// This method get the content of the Excel file
/// </summary>
/// <param name="file">Excel File</param>
/// <returns>Table of the content of the Excel file</returns>
/// <author>Edwin (Origin) - Abigail Rodriguez(Edit)</author>
public ActionResult ImpExcel(HttpPostedFileBase file)
{
Dictionary <string, int> orderCell = new Dictionary <string, int>();
string[] arrayalf = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
for (int i = 0; i < arrayalf.Length; i++)
{
orderCell.Add(arrayalf[i], i);
}
DataSet ds = new DataSet();
List <List <string> > tr = new List <List <string> >();
try
{
if (Request.Files["file"].ContentLength > 0 /* && (idcategory != "0" && idcategory != null)*/)
{
string fileExtension = System.IO.Path.GetExtension(Request.Files["file"].FileName);
if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}
Request.Files["file"].SaveAs(fileLocation);
}
string fileLocation2 = Server.MapPath("~/Content/") + Request.Files["file"].FileName;
if (System.IO.File.Exists(fileLocation2))
{
System.IO.File.Delete(fileLocation2);
}
Request.Files["file"].SaveAs(fileLocation2);
using (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileLocation2, false))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.Last();
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();;
foreach (Row r in sheetData.Elements <Row>())
{
List <string> td = new List <string>();
int index = 0;
foreach (Cell c in r.Elements <Cell>())
{
string cellIndex = c.CellReference.ToString().Substring(0, 1);
bool validate = false;
int numcellx = 0;
foreach (var x in orderCell)
{
if (x.Key == cellIndex)
{
numcellx = x.Value;
}
if (x.Key == cellIndex && x.Value == index)
{
validate = true;
break;
}
}
if (validate == false)
{
numcellx = numcellx - index;
for (int i = 0; i < numcellx; i++)
{
td.Add("");
}
index = index + numcellx;
}
Int32 id = -1;
if (c.DataType != null && c.DataType.Value == CellValues.SharedString)
{
if (Int32.TryParse(c.InnerText, out id))
{
SharedStringItem item = GetSharedStringItemById(workbookPart, id);
if (item.Text != null)
{
td.Add(item.Text.Text);
}
else if (item.InnerText != null)
{
td.Add(item.InnerText);
}
else if (item.InnerXml != null)
{
td.Add(item.InnerXml);
}
}
else
{
td.Add(c.CellValue.Text);
}
}
else
{
try
{
td.Add(c.CellValue.Text);
}
catch (Exception ex)
{
td.Add("");
}
}
index++;
}
tr.Add(td);
}
spreadsheetDocument.Close();
}
List <List <string> > data = new List <List <string> >();
ViewData["categoriedata"] = data;
ViewData["Filelocation"] = fileLocation2;
ViewData["Filerequest"] = file;
return(View(tr));
}
else
{
return(null);
}
}
catch (Exception ex)
{
//throw new Exception(ex.Message);
// System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
//System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
return(null);
}
}
public ActionResult Search(FormCollection model)
{
string selectedReport = model["cboReports"];
string datePeriod = model["DatePeriod"];
if (selectedReport.HasValue())
{
Guid idReport = new Guid(selectedReport);
Reports report = Reports.Get(idReport);
IEnumerable <dynamic> data = Reports.Run(report, datePeriod);
HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true")
{
Path = "/"
});
var context = HttpContext.Response;
context.Buffer = context.BufferOutput = false;
context.Cache.SetCacheability(HttpCacheability.Private);
context.Cache.SetExpires(DateTime.Now);
//context.ContentType = (new ContentType("text/csv") { CharSet = "utf-8" }).ToString(); // CSV
//context.ContentType = (new ContentType("application/vnd.ms-excel") { CharSet = "utf-8" }).ToString();
context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, report.FileNamePrefix + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now)
}.ToString()
);
context.AppendHeader("X-Content-Type-Options", "nosniff");
using (MemoryStream mDocument = new MemoryStream())
{
// Using SAX
using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook))
{
List <OpenXmlAttribute> attributes;
document.AddWorkbookPart();
// Stylesheet
WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesheet.Stylesheet = new Stylesheet(new Fonts(
new Font( // 0 = Default
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}
),
new Font( // 1 = Bold
new Bold()
),
new Font( // 2 = Red
new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}
)
),
new Fills(
new Fill()
{
}
),
new Borders(new Border()
{
}),
new CellFormats(
new CellFormat()
{
FontId = 0
}, // 0
new CellFormat()
{
FontId = 1, ApplyFont = true
}, // 1
new CellFormat()
{
FontId = 2, ApplyFont = true
} // 2
)
);
stylesheet.Stylesheet.Save();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
IDictionary <string, object> firstRow = data.FirstOrDefault();
if (firstRow != null)
{
int row = 1;
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in firstRow.Keys.ToList())
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col1) + row),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cols));
writer.WriteEndElement();
col1++;
}
writer.WriteEndElement();
row++;
foreach (IDictionary <string, object> row2 in data)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
foreach (var key in row2.Keys)
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col) + row)
};
if (row2[key] is decimal)
{
if ((decimal)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
else if (row2[key] is double)
{
if ((double)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : ""));
writer.WriteEndElement();
col++;
}
writer.WriteEndElement();
row++;
}
}
else
{
// Empty row (no data found)
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, "1")
};
writer.WriteStartElement(new Row(), attributes);
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(1) + 1),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(""));
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Sheet 1",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
document.Save();
document.Close();
mDocument.WriteTo(context.OutputStream);
}
}
return(null);
}
return(null);
}
public static void InsertImage(this SpreadsheetDocument document, string sheet, long x, long y, long?width, long?height, string sImagePath)
{
try
{
WorksheetPart wsp = (WorksheetPart)document.WorkbookPart.GetPartById(sheet);
DrawingsPart dp;
ImagePart imgp;
WorksheetDrawing wsd;
ImagePartType ipt;
switch (sImagePath.Substring(sImagePath.LastIndexOf('.') + 1).ToLower())
{
case "png":
ipt = ImagePartType.Png;
break;
case "jpg":
case "jpeg":
ipt = ImagePartType.Jpeg;
break;
case "gif":
ipt = ImagePartType.Gif;
break;
case "bmp":
ipt = ImagePartType.Bmp;
break;
default:
return;
}
if (wsp.DrawingsPart == null)
{
dp = wsp.AddNewPart <DrawingsPart>();
imgp = dp.AddImagePart(ipt, wsp.GetIdOfPart(dp));
wsd = new WorksheetDrawing();
}
else
{
dp = wsp.DrawingsPart;
imgp = dp.AddImagePart(ipt);
dp.CreateRelationshipToPart(imgp);
wsd = dp.WorksheetDrawing;
}
using (FileStream fs = new FileStream(sImagePath, FileMode.Open))
{
imgp.FeedData(fs);
}
int imageNumber = dp.ImageParts.Count <ImagePart>();
if (imageNumber == 1)
{
Drawing drawing = new Drawing();
drawing.Id = dp.GetIdOfPart(imgp);
wsp.Worksheet.Append(drawing);
}
NonVisualDrawingProperties nvdp = new NonVisualDrawingProperties();
nvdp.Id = new UInt32Value((uint)(1024 + imageNumber));
nvdp.Name = "Picture " + imageNumber.ToString();
nvdp.Description = "";
DocumentFormat.OpenXml.Drawing.PictureLocks picLocks = new DocumentFormat.OpenXml.Drawing.PictureLocks();
picLocks.NoChangeAspect = true;
picLocks.NoChangeArrowheads = true;
NonVisualPictureDrawingProperties nvpdp = new NonVisualPictureDrawingProperties();
nvpdp.PictureLocks = picLocks;
NonVisualPictureProperties nvpp = new NonVisualPictureProperties();
nvpp.NonVisualDrawingProperties = nvdp;
nvpp.NonVisualPictureDrawingProperties = nvpdp;
DocumentFormat.OpenXml.Drawing.Stretch stretch = new DocumentFormat.OpenXml.Drawing.Stretch();
stretch.FillRectangle = new DocumentFormat.OpenXml.Drawing.FillRectangle();
BlipFill blipFill = new BlipFill();
DocumentFormat.OpenXml.Drawing.Blip blip = new DocumentFormat.OpenXml.Drawing.Blip();
blip.Embed = dp.GetIdOfPart(imgp);
blip.CompressionState = DocumentFormat.OpenXml.Drawing.BlipCompressionValues.Print;
blipFill.Blip = blip;
blipFill.SourceRectangle = new DocumentFormat.OpenXml.Drawing.SourceRectangle();
blipFill.Append(stretch);
DocumentFormat.OpenXml.Drawing.Transform2D t2d = new DocumentFormat.OpenXml.Drawing.Transform2D();
DocumentFormat.OpenXml.Drawing.Offset offset = new DocumentFormat.OpenXml.Drawing.Offset();
offset.X = 0;
offset.Y = 0;
t2d.Offset = offset;
System.Drawing.Bitmap bm = new System.Drawing.Bitmap(sImagePath);
DocumentFormat.OpenXml.Drawing.Extents extents = new DocumentFormat.OpenXml.Drawing.Extents();
if (width == null)
{
extents.Cx = (long)bm.Width * (long)((float)914400 / bm.HorizontalResolution);
}
else
{
extents.Cx = width * (long)((float)914400 / bm.HorizontalResolution);
}
if (height == null)
{
extents.Cy = (long)bm.Height * (long)((float)914400 / bm.VerticalResolution);
}
else
{
extents.Cy = height * (long)((float)914400 / bm.VerticalResolution);
}
bm.Dispose();
t2d.Extents = extents;
ShapeProperties sp = new ShapeProperties();
sp.BlackWhiteMode = DocumentFormat.OpenXml.Drawing.BlackWhiteModeValues.Auto;
sp.Transform2D = t2d;
DocumentFormat.OpenXml.Drawing.PresetGeometry prstGeom = new DocumentFormat.OpenXml.Drawing.PresetGeometry();
prstGeom.Preset = DocumentFormat.OpenXml.Drawing.ShapeTypeValues.Rectangle;
prstGeom.AdjustValueList = new DocumentFormat.OpenXml.Drawing.AdjustValueList();
sp.Append(prstGeom);
sp.Append(new DocumentFormat.OpenXml.Drawing.NoFill());
DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture picture = new DocumentFormat.OpenXml.Drawing.Spreadsheet.Picture();
picture.NonVisualPictureProperties = nvpp;
picture.BlipFill = blipFill;
picture.ShapeProperties = sp;
Position pos = new Position();
pos.X = x * 914400 / 72;
pos.Y = y * 914400 / 72;
Extent ext = new Extent();
ext.Cx = extents.Cx;
ext.Cy = extents.Cy;
AbsoluteAnchor anchor = new AbsoluteAnchor();
anchor.Position = pos;
anchor.Extent = ext;
anchor.Append(picture);
anchor.Append(new ClientData());
wsd.Append(anchor);
wsd.Save(dp);
}
catch (Exception ex)
{
throw ex;
}
}
private static void WriteingToExcel(WorksheetPart worksheetPart)
{
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row row = new Row();
row.Append(new Cell()
{
CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2
},
new Cell()
{
CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2
},
new Cell()
{
CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 2
});
sheetData.AppendChild(row);
for (int i = 0; i < 20; i++)
{
row = new Row();
row.Append(new Cell()
{
CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test " + i), DataType = CellValues.String, StyleIndex = 1
},
new Cell()
{
CellValue = new CellValue("napis test "), DataType = CellValues.String, StyleIndex = 1
});
sheetData.AppendChild(row);
}
worksheetPart.Worksheet.Save();
}
static void WritePlainExcel(ResultTable results, Stream stream, string title)
{
stream.WriteAllBytes(Template);
if (results == null)
{
throw new ApplicationException(ExcelMessage.ThereAreNoResultsToWrite.NiceToString());
}
using (SpreadsheetDocument document = SpreadsheetDocument.Open(stream, true))
{
document.PackageProperties.Creator = "";
document.PackageProperties.LastModifiedBy = "";
WorkbookPart workbookPart = document.WorkbookPart;
WorksheetPart worksheetPart = document.GetWorksheetPartById("rId1");
worksheetPart.Worksheet = new Worksheet();
worksheetPart.Worksheet.Append(new Columns(results.Columns.Select((c, i) => new spreadsheet.Column()
{
Min = (uint)i + 1,
Max = (uint)i + 1,
Width = GetColumnWidth(c.Column.Type),
BestFit = true,
CustomWidth = true
}).ToArray()));
Dictionary <ResultColumn, (DefaultStyle defaultStyle, UInt32Value styleIndex)> indexes =
results.Columns.ToDictionary(c => c, c => CellBuilder.GetDefaultStyleAndIndex(c));
var ss = document.WorkbookPart.WorkbookStylesPart.Stylesheet;
{
var maxIndex = ss.NumberingFormats.ChildElements.Cast <NumberingFormat>()
.Max(f => (uint)f.NumberFormatId) + 1;
var decimalCellFormat = ss.CellFormats.ElementAt((int)(uint)CellBuilder.DefaultStyles[DefaultStyle.Decimal]);
foreach (var kvp in CellBuilder.CustomDecimalStyles)
{
var numberingFormat = new NumberingFormat
{
NumberFormatId = maxIndex++,
FormatCode = kvp.Key
};
ss.NumberingFormats.AppendChild(numberingFormat);
var cellFormat = (CellFormat)decimalCellFormat.CloneNode(false);
cellFormat.NumberFormatId = numberingFormat.NumberFormatId;
ss.CellFormats.AppendChild(cellFormat);
ss.CellFormats.Count = (uint)ss.CellFormats.ChildElements.Count;
if (ss.CellFormats.Count != kvp.Value + 1)
{
throw new InvalidOperationException("Unexpected CellFormats count");
}
}
}
worksheetPart.Worksheet.Append(new Sequence <Row>()
{
new [] { CellBuilder.Cell(title, DefaultStyle.Title) }.ToRow(),
(from c in results.Columns
select CellBuilder.Cell(c.Column.DisplayName, DefaultStyle.Header)).ToRow(),
from r in results.Rows
select(from c in results.Columns
let t = indexes.GetOrThrow(c)
select CellBuilder.Cell(r[c], t.defaultStyle, t.styleIndex)).ToRow()
}.ToSheetData());
workbookPart.Workbook.Save();
document.Close();
}
}
private static void AddWorksheet(SpreadsheetDocument sDoc, Worksheet worksheetData)
{
Regex validSheetName = new Regex(@"^[^'*\[\]/\\:?][^*\[\]/\\:?]{0,30}$");
if (!validSheetName.IsMatch(worksheetData.Name))
{
throw new InvalidSheetNameException(worksheetData.Name);
}
// throw WorksheetAlreadyExistsException if a sheet with the same name (case-insensitive) already exists in the workbook
string UCName = worksheetData.Name.ToUpper();
XDocument wXDoc = sDoc.WorkbookPart.GetXDocument();
if (wXDoc
.Root
.Elements(S.sheets)
.Elements(S.sheet)
.Attributes(SSNoNamespace.name)
.Select(a => ((string)a).ToUpper())
.Contains(UCName))
{
throw new WorksheetAlreadyExistsException(worksheetData.Name);
}
// create the worksheet with the supplied name
XDocument appXDoc = sDoc
.ExtendedFilePropertiesPart
.GetXDocument();
XElement vector = appXDoc
.Root
.Elements(EP.TitlesOfParts)
.Elements(VT.vector)
.FirstOrDefault();
if (vector != null)
{
int?size = (int?)vector.Attribute(SSNoNamespace.size);
if (size == null)
{
size = 1;
}
else
{
size = size + 1;
}
vector.SetAttributeValue(SSNoNamespace.size, size);
vector.Add(
new XElement(VT.lpstr, worksheetData.Name));
XElement i4 = appXDoc
.Root
.Elements(EP.HeadingPairs)
.Elements(VT.vector)
.Elements(VT.variant)
.Elements(VT.i4)
.FirstOrDefault();
if (i4 != null)
{
i4.Value = ((int)i4 + 1).ToString();
}
sDoc.ExtendedFilePropertiesPart.PutXDocument();
}
WorkbookPart workbook = sDoc.WorkbookPart;
string rId = "R" + Guid.NewGuid().ToString().Replace("-", "");
WorksheetPart worksheetPart = workbook.AddNewPart <WorksheetPart>(rId);
XDocument wbXDoc = workbook.GetXDocument();
XElement sheets = wbXDoc.Descendants(S.sheets).FirstOrDefault();
sheets.Add(
new XElement(S.sheet,
new XAttribute(SSNoNamespace.name, worksheetData.Name.ToString()),
new XAttribute(SSNoNamespace.sheetId, sheets.Elements(S.sheet).Count() + 1),
new XAttribute(R.id, rId)));
workbook.PutXDocument();
string ws = S.s.ToString();
string relns = R.r.ToString();
using (Stream partStream = worksheetPart.GetStream(FileMode.Create, FileAccess.Write))
{
using (XmlWriter partXmlWriter = XmlWriter.Create(partStream))
{
partXmlWriter.WriteStartDocument();
partXmlWriter.WriteStartElement("worksheet", ws);
partXmlWriter.WriteStartElement("sheetData", ws);
int numColumnHeadingRows = 0;
int numColumns = 0;
int numColumnsInRows = 0;
int numRows;
if (worksheetData.ColumnHeadings != null)
{
Row row = new Row
{
Cells = worksheetData.ColumnHeadings
};
SerializeRows(sDoc, partXmlWriter, new[] { row }, 1, out numColumns, out numColumnHeadingRows);
}
SerializeRows(sDoc, partXmlWriter, worksheetData.Rows, numColumnHeadingRows + 1, out numColumnsInRows,
out numRows);
int totalRows = numColumnHeadingRows + numRows;
int totalColumns = Math.Max(numColumns, numColumnsInRows);
if (worksheetData.ColumnHeadings != null && worksheetData.TableName != null)
{
partXmlWriter.WriteEndElement();
string rId2 = "R" + Guid.NewGuid().ToString().Replace("-", "");
partXmlWriter.WriteStartElement("tableParts", ws);
partXmlWriter.WriteStartAttribute("count");
partXmlWriter.WriteValue(1);
partXmlWriter.WriteEndAttribute();
partXmlWriter.WriteStartElement("tablePart", ws);
partXmlWriter.WriteStartAttribute("id", relns);
partXmlWriter.WriteValue(rId2);
TableDefinitionPart tdp = worksheetPart.AddNewPart <TableDefinitionPart>(rId2);
XDocument tXDoc = tdp.GetXDocument();
XElement table = new XElement(S.table,
new XAttribute(SSNoNamespace.id, 1),
new XAttribute(SSNoNamespace.name, worksheetData.TableName),
new XAttribute(SSNoNamespace.displayName, worksheetData.TableName),
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString()),
new XAttribute(SSNoNamespace.totalsRowShown, 0),
new XElement(S.autoFilter,
new XAttribute(SSNoNamespace._ref, "A1:" + SpreadsheetMLUtil.IntToColumnId(totalColumns - 1) + totalRows.ToString())),
new XElement(S.tableColumns,
new XAttribute(SSNoNamespace.count, totalColumns),
worksheetData.ColumnHeadings.Select((ch, i) =>
new XElement(S.tableColumn,
new XAttribute(SSNoNamespace.id, i + 1),
new XAttribute(SSNoNamespace.name, ch.Value)))),
new XElement(S.tableStyleInfo,
new XAttribute(SSNoNamespace.name, "TableStyleMedium2"),
new XAttribute(SSNoNamespace.showFirstColumn, 0),
new XAttribute(SSNoNamespace.showLastColumn, 0),
new XAttribute(SSNoNamespace.showRowStripes, 1),
new XAttribute(SSNoNamespace.showColumnStripes, 0)));
tXDoc.Add(table);
tdp.PutXDocument();
}
}
}
}
/// <summary>
/// Write excel file of a list of object as T
/// Assume that maximum of 24 columns
/// </summary>
/// <typeparam name="T">Object type to pass in</typeparam>
/// <param name="fileName">Full path of the file name of excel spreadsheet</param>
/// <param name="objects">list of the object type</param>
/// <param name="sheetName">Sheet names of Excel File</param>
/// <param name="headerNames">Header names of the object</param>
public void Create <T>(
string fileName,
List <T> objects,
string sheetName,
List <string> headerNames)
{
//Open the copied template workbook.
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myWorkbook.AddWorkbookPart();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
// Create Styles and Insert into Workbook
WorkbookStylesPart stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
string relId = workbookPart.GetIdOfPart(worksheetPart);
Workbook workbook = new Workbook();
FileVersion fileVersion = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
SheetData sheetData = CreateSheetData <T>(objects, headerNames, stylesPart);
Worksheet worksheet = new Worksheet();
//PageMargins pageM = worksheet.GetFirstChild<PageMargins>();
//SheetProtection sheetProtection = new SheetProtection();
//sheetProtection.Sheet = true;
//sheetProtection.Objects = true;
//sheetProtection.Scenarios = true;
////add column C:Z to allow edit range, which means column A,B and after Z are locked
//ProtectedRanges pRanges = new ProtectedRanges();
//ProtectedRange pRange = new ProtectedRange();
//ListValue<StringValue> lValue = new ListValue<StringValue>();
//lValue.InnerText = "D1:Z1048576";
//pRange.SequenceOfReferences = lValue;
//pRange.Name = "AllowEditRange1";
//pRanges.Append(pRange);
//worksheet.InsertBefore(sheetProtection, pageM);
//worksheet.InsertBefore(pRanges, pageM);
int numCols = headerNames.Count;
int width = headerNames.Max(h => h.Length) + 5;
Columns columns = new Columns();
for (int col = 0; col < numCols; col++)
{
Column c = CreateColumnData((UInt32)col + 1, (UInt32)numCols + 1, width);
if (col == 0)
{
c.Hidden = BooleanValue.FromBoolean(true);
}
columns.Append(c);
}
worksheet.Append(columns);
Sheets sheets = new Sheets();
Sheet sheet = new Sheet {
Name = sheetName, SheetId = 1, Id = relId
};
sheets.Append(sheet);
workbook.Append(fileVersion);
workbook.Append(sheets);
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet;
worksheetPart.Worksheet.Save();
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
static void Main(string[] args)
{
Student myrecord = new Student {
StudentId = "200429013", FirstName = "Priyanka", LastName = "Garg"
};
List <string> directories = FTP.GetDirectory(Constants.FTP.BaseUrl);
List <Student> students = new List <Student>();
foreach (var directory in directories)
{
Student student = new Student()
{
AbsoluteUrl = Constants.FTP.BaseUrl
};
student.FromDirectory(directory);
//Console.WriteLine(student);
string infoFilePath = student.FullPathUrl + "/" + Constants.Locations.InfoFile;
bool fileExists = FTP.FileExists(infoFilePath);
if (fileExists == true)
{
string csvPath = $@"/Users/priyankagarg/Desktop/Student Data/{directory}.csv";
// FTP.DownloadFile(infoFilePath, csvPath);
byte[] bytes = FTP.DownloadFileBytes(infoFilePath);
string csvData = Encoding.Default.GetString(bytes);
string[] csvlines = csvData.Split("\r\n", StringSplitOptions.RemoveEmptyEntries);
if (csvlines.Length != 2)
{
Console.WriteLine("Error in CSV format");
}
else
{
student.FromCSV(csvlines[1]);
Console.WriteLine(" \t Age of Student is: {0} ", student.age);
}
Console.WriteLine("Found info file:");
}
else
{
Console.WriteLine("Could not find info file:");
}
Console.WriteLine("\t" + infoFilePath);
string imageFilePath = student.FullPathUrl + "/" + Constants.Locations.ImageFile;
bool imageFileExists = FTP.FileExists(imageFilePath);
if (imageFileExists == true)
{
Console.WriteLine("Found image file:");
}
else
{
Console.WriteLine("Could not find image file:");
}
Console.WriteLine("\t" + imageFilePath);
students.Add(student);
Console.WriteLine(directory);
Console.WriteLine(" \t Count of student is: {0}", students.Count);
Console.WriteLine(" \t Age of Student is: {0} ", student.age);
}
Student me = students.SingleOrDefault(x => x.StudentId == myrecord.StudentId);
Student meUsingFind = students.Find(x => x.StudentId == myrecord.StudentId);
var avgage = students.Average(x => x.age);
var minage = students.Min(x => x.age);
var maxage = students.Max(x => x.age);
Console.WriteLine(" \n\t Name Searched With Query: {0} ", meUsingFind);
Console.WriteLine(" \t Average of Student age is: {0} ", avgage);
Console.WriteLine(" \t Minimum of Student age is: {0} ", minage);
Console.WriteLine(" \t Maximum of Student age is: {0} ", maxage);
//save to csv
string studentsCSVPath = $"{Constants.Locations.DataFolder}//students.csv";
//Establish a file stream to collect data from the response
using (StreamWriter fs = new StreamWriter(studentsCSVPath))
{
foreach (var student in students)
{
fs.WriteLine(student.ToCSV());
}
}
string studentsWordPath = $"{Constants.Locations.DataFolder}//students.docx";
//string studentsImagePath1 = $"{Constants.Locations.ImagesFolder}//images.jpg";
// Create a document by supplying the filepath.
using (WordprocessingDocument wordDocument =
WordprocessingDocument.Create(studentsWordPath, WordprocessingDocumentType.Document))
{
// Add a main document part.
MainDocumentPart mainPart = wordDocument.AddMainDocumentPart();
// Create the document structure and add some text.
mainPart.Document = new Document();
Body body = mainPart.Document.AppendChild(new Body());
Paragraph para = body.AppendChild(new Paragraph());
Run run = para.AppendChild(new Run());
foreach (var student in students)
{
run.AppendChild(new Text("My name is : "));
//run.AppendChild(new Text(student.ToString()));
run.AppendChild(new Text(student.FirstName.ToString()));
run.AppendChild(new Text(" , "));
run.AppendChild(new Text("My Student id is: "));
run.AppendChild(new Text(student.StudentId.ToString()));
run.AppendChild(new Text(" , "));
run.AppendChild(new Break()
{
Type = BreakValues.Page
});
}
}
string studentsjsonPath = $"{Constants.Locations.DataFolder}//students.json";
//Establish a file stream to collect data from the response
using (StreamWriter fs = new StreamWriter(studentsjsonPath))
{
foreach (var student in students)
{
string Student = Newtonsoft.Json.JsonConvert.SerializeObject(student);
fs.WriteLine(Student.ToString());
//Console.WriteLine(jStudent);
}
}
string studentsExcelPath = $"{Constants.Locations.DataFolder}//students.xlsx";
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
Create(studentsExcelPath, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(new DocumentFormat.OpenXml.Spreadsheet.SheetData());
// Add Sheets to the Workbook.
DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
AppendChild <DocumentFormat.OpenXml.Spreadsheet.Sheets>(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
// Append a new worksheet and associate it with the workbook.
DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet()
{
Id = spreadsheetDocument.WorkbookPart.
GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.Append(sheet);
workbookpart.Workbook.Save();
spreadsheetDocument.Close();
string studentsxmlPath = $"{Constants.Locations.DataFolder}//students.xml";
//Establish a file stream to collect data from the response
using (StreamWriter fs = new StreamWriter(studentsxmlPath))
{
XmlSerializer x = new XmlSerializer(students.GetType());
x.Serialize(fs, students);
Console.WriteLine();
}
// 4.Upload the files to My FTP
foreach (var student in students)
{
FTP.UploadFile(studentsCSVPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.csv");
FTP.UploadFile(studentsjsonPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.json");
FTP.UploadFile(studentsxmlPath, Constants.FTP.BaseUrl + "/200429013 Priyanka Garg/students.xml");
}
return;
}
public static string GetCellValue(string fileName, string sheetName, string addressName)
{
string value = null;
using (SpreadsheetDocument document =
SpreadsheetDocument.Open(fileName, false))
{
WorkbookPart wbPart = document.WorkbookPart;
// Find the sheet with the supplied name, and then use that Sheet
// object to retrieve a reference to the appropriate worksheet.
Sheet theSheet = wbPart.Workbook.Descendants <Sheet>().
Where(s => s.Name == sheetName).FirstOrDefault();
if (theSheet == null)
{
throw new ArgumentException("sheetName");
}
// Retrieve a reference to the worksheet part, and then use its
// Worksheet property to get a reference to the cell whose
// address matches the address you supplied:
WorksheetPart wsPart =
(WorksheetPart)(wbPart.GetPartById(theSheet.Id));
Cell theCell = wsPart.Worksheet.Descendants <Cell>().
Where(c => c.CellReference == addressName).FirstOrDefault();
// If the cell does not exist, return an empty string:
if (theCell != null)
{
value = theCell.InnerText;
// If the cell represents a numeric value, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and Booleans
// individually. For shared strings, the code looks up the
// corresponding value in the shared string table. For Booleans,
// the code converts the value into the words TRUE or FALSE.
if (theCell.DataType != null)
{
switch (theCell.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the shared
// strings table.
var stringTable = wbPart.
GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something is
// wrong. Return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTable != null)
{
value = stringTable.SharedStringTable.
ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
}
}
return(value);
}
public static void CreateDoc(ExcelInfoEmployee info)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(info.FileName, SpreadsheetDocumentType.Workbook))
{
// Создаем книгу (в ней хранятся листы)
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
CreateStyles(workbookpart);
// Получаем/создаем хранилище текстов для книги
SharedStringTablePart shareStringPart = spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().Count() > 0
? spreadsheetDocument.WorkbookPart.GetPartsOfType <SharedStringTablePart>().First()
: spreadsheetDocument.WorkbookPart.AddNewPart <SharedStringTablePart>();
// Создаем SharedStringTable, если его нет
if (shareStringPart.SharedStringTable == null)
{
shareStringPart.SharedStringTable = new SharedStringTable();
}
// Создаем лист в книгу
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Добавляем лист в книгу
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Лист"
};
sheets.Append(sheet);
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = 1,
Text = info.Title,
StyleIndex = 2U
});
MergeCells(new ExcelMergeParameters
{
Worksheet = worksheetPart.Worksheet,
CellFromName = "A1",
CellToName = "C1"
});
uint rowIndex = 2;
var list = info.Purchases.GroupBy(purchase => purchase.CosmeticName, purchase => purchase.Price, (name, cost) => new { Key = name, Price = cost, Count = cost.Count() });
var j = 0;
foreach (var cosmetic in list)
{
rowIndex++;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = "Наименование",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = cosmetic.Key,
StyleIndex = 0U
});
rowIndex++;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = "Стоимость",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = cosmetic.Price.First().ToString(),
StyleIndex = 0U
});
rowIndex += 2;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = "Дата",
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = "Количество",
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = "ID клиента",
StyleIndex = 1U
});
rowIndex++;
for (var i = 0; i < cosmetic.Count; i++)
{
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = info.Purchases[j].Date.ToString(),
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "C",
RowIndex = rowIndex,
Text = info.Purchases[j].Count.ToString(),
StyleIndex = 1U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "D",
RowIndex = rowIndex,
Text = info.Purchases[j].ClientId.ToString(),
StyleIndex = 1U
});
rowIndex++;
j++;
}
}
rowIndex++;
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "A",
RowIndex = rowIndex,
Text = "Общая сумма покупок",
StyleIndex = 0U
});
InsertCellInWorksheet(new ExcelCellParameters
{
Worksheet = worksheetPart.Worksheet,
ShareStringPart = shareStringPart,
ColumnName = "B",
RowIndex = rowIndex,
Text = info.Purchases[0].TotalCost.ToString(),
StyleIndex = 0U
});
workbookpart.Workbook.Save();
}
}
/// <summary>
/// 向当前工作区的表中插入一个单元格
/// </summary>
/// <param name="worksheetPart">当前的工作区对象</param>
/// <param name="cell">设置要插入的单元格信息</param>
/// <returns>返回插入成功的数量</returns>
public static bool InsertCell(this WorksheetPart worksheetPart, ExcelCell cell)
{
return(InsertCell(worksheetPart, new List <ExcelCell> {
cell
}) > 0);
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart)
{
OpenXmlWriter writer = OpenXmlWriter.Create(worksheetPart, Encoding.UTF8);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
string cellValue = "";
string cellReference = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsIntegerColumn = new bool[numberOfColumns];
bool[] IsFloatColumn = new bool[numberOfColumns];
bool[] IsDateColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
AppendHeaderTextCell(excelColumnNames[colInx] + "1", col.ColumnName, writer);
IsIntegerColumn[colInx] = (col.DataType.FullName.StartsWith("System.Int"));
IsFloatColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Double") || (col.DataType.FullName == "System.Single");
IsDateColumn[colInx] = (col.DataType.FullName == "System.DateTime");
}
writer.WriteEndElement(); // End of header "Row"
//
// Now, step through each row of data in our DataTable...
//
double cellFloatValue = 0;
CultureInfo ci = new CultureInfo("en-US");
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
writer.WriteStartElement(new Row {
RowIndex = rowIndex
});
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
cellValue = ReplaceHexadecimalSymbols(cellValue);
cellReference = excelColumnNames[colInx] + rowIndex.ToString();
// Create cell with data
if (IsIntegerColumn[colInx] || IsFloatColumn[colInx])
{
// For numeric cells without any decimal places.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellFloatValue = 0;
if (double.TryParse(cellValue, out cellFloatValue))
{
cellValue = cellFloatValue.ToString(ci);
AppendNumericCell(cellReference, cellValue, writer);
}
}
else if (IsDateColumn[colInx])
{
// This is a date value.
DateTime dateValue;
if (DateTime.TryParse(cellValue, out dateValue))
{
AppendDateCell(cellReference, dateValue, writer);
}
else
{
// This should only happen if we have a DataColumn of type "DateTime", but this particular value is null/blank.
AppendTextCell(cellReference, cellValue, writer);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(cellReference, cellValue, writer);
}
}
writer.WriteEndElement(); // End of Row
}
writer.WriteEndElement(); // End of SheetData
writer.WriteEndElement(); // End of worksheet
writer.Close();
dt.Clear();
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ExecutionContext context,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
//// 1. Get data from SQL
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(Environment.GetEnvironmentVariable("sqlConnection")))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter("select * from Test", connection))
{
dataAdapter.Fill(ds);
}
}
//// 2. Create and Format the excel
using (SpreadsheetDocument workbook = SpreadsheetDocument.Create(context.FunctionDirectory + "\\test.xlsx", SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookPart = workbook.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Test Sheet"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row row = new Row();
foreach (DataTable dataTable in ds.Tables)
{
foreach (DataRow dataRow in dataTable.Rows)
{
row = new Row();
row.Append(
ConstructCell(dataRow["id"].ToString(), CellValues.String),
ConstructCell(dataRow["name"].ToString(), CellValues.String));
sheetData.AppendChild(row);
}
}
workbookPart.Workbook.Save();
}
//// 3. Uplaod the resulting excel to Storage blob
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(Environment.GetEnvironmentVariable("MyStorageConnectionAppSetting"));
CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
CloudBlobContainer container = blobClient.GetContainerReference("test");
CloudBlockBlob blockBlob = container.GetBlockBlobReference("test.xlsx");
blockBlob.UploadFromFile(context.FunctionDirectory + "\\test.xlsx");
return(new OkResult());
}
private DataTable ReadExcelFileDOM(string filename)
{
DataTable table;
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
Sheet worksheet = workbookPart.Workbook.Descendants <Sheet>().First();
WorksheetPart worksheetPart =
(WorksheetPart)(workbookPart.GetPartById(worksheet.Id));
SheetData sheetData =
worksheetPart.Worksheet.Elements <SheetData>().First();
List <List <string> > totalRows = new List <List <string> >();
int maxCol = 0;
foreach (Row r in sheetData.Elements <Row>())//Skip header row
{
// Add the empty row.
string value = null;
while (totalRows.Count < r.RowIndex - 1)
{
List <string> emptyRowValues = new List <string>();
for (int i = 0; i < maxCol; i++)
{
emptyRowValues.Add("");
}
totalRows.Add(emptyRowValues);
}
List <string> tempRowValues = new List <string>();
foreach (Cell c in r.Elements <Cell>())
{
#region get the cell value of c.
if (c != null)
{
value = c.InnerText;
// If the cell represents a numeric value, you are done.
// For dates, this code returns the serialized value that
// represents the date. The code handles strings and Booleans
// individually. For shared strings, the code looks up the
// corresponding value in the shared string table. For Booleans,
// the code converts the value into the words TRUE or FALSE.
if (c.DataType != null)
{
switch (c.DataType.Value)
{
case CellValues.SharedString:
// For shared strings, look up the value in the shared
// strings table.
var stringTable = workbookPart.
GetPartsOfType <SharedStringTablePart>().FirstOrDefault();
// If the shared string table is missing, something is
// wrong. Return the index that you found in the cell.
// Otherwise, look up the correct text in the table.
if (stringTable != null)
{
value = stringTable.SharedStringTable.
ElementAt(int.Parse(value)).InnerText;
}
break;
case CellValues.Boolean:
switch (value)
{
case "0":
value = "FALSE";
break;
default:
value = "TRUE";
break;
}
break;
}
}
Console.Write(value + " ");
}
#endregion
// Add the cell to the row list.
int i = Convert.ToInt32(c.CellReference.ToString().ToCharArray().First() - 'A');
// Add the blank cell in the row.
while (tempRowValues.Count < i)
{
tempRowValues.Add("");
}
tempRowValues.Add(value);
}
// add the row to the totalRows.
maxCol = processList(tempRowValues, totalRows, maxCol);
Console.WriteLine();
}
table = ConvertListListStringToDataTable(totalRows, maxCol);
}
return(table);
}
static void Main(string[] args)
{
//Get Template and create new file to edit
System.IO.File.Copy("Templates\\MediumTemplate.xlsx", "CustomersReport.xlsx", true);
//Generate Data
List <CustomersReport> reportData = new CustomersReport().GetRandomData();
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open("CustomersReport.xlsx", true))
{
WorkbookPart wBookPart = null;
wBookPart = spreadsheetDoc.WorkbookPart;
wBookPart.Workbook = new Workbook();
spreadsheetDoc.WorkbookPart.Workbook.Sheets = new Sheets();
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
//Get worksheetpart
WorksheetPart wSheetPart = spreadsheetDoc.WorkbookPart.WorksheetParts.First();
//Get existing workSheetPart
WorksheetPart newWorksheetPart = spreadsheetDoc.WorkbookPart.WorksheetParts.First();
//add Styles
WorkbookStylesPart stylesPart = spreadsheetDoc.WorkbookPart.WorkbookStylesPart;
//stylesPart.Stylesheet = Styles.GenerateStyleSheet();
stylesPart.Stylesheet.Save();
string relationshipId = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart);
// Get a unique ID for the new worksheet.
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
// Give the new worksheet a name.
Sheet sheet = new Sheet()
{
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(newWorksheetPart), SheetId = sheetId, Name = "Customer_Report" + sheetId
};
sheets.Append(sheet);
//get existing sheetData
SheetData sheetData = newWorksheetPart.Worksheet.GetFirstChild <SheetData>();
foreach (CustomersReport data in reportData)
{
Row contentRow = new Row();
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.Name
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.RegisterDate
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.LastBuy
}
});
contentRow.Append(new Cell {
DataType = CellValues.String, CellValue = new CellValue {
Text = data.Item
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.Quantity.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = data.ItemCost.ToString()
}
});
contentRow.Append(new Cell {
DataType = CellValues.Number, CellValue = new CellValue {
Text = string.Format("{0}", data.Quantity * data.ItemCost)
}
});
sheetData.AppendChild(contentRow);
}
}
}
public static void ReadAirfieldData(ZandraUserPreferences userPreferences)
{
Dictionary <string, Point> points = userPreferences.EntryToValidPoint;
var fileContent = string.Empty;
var filePath = string.Empty;
using (System.Windows.Forms.OpenFileDialog openFileDialog = new OpenFileDialog())
{
openFileDialog.InitialDirectory = "c:\\";
openFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
openFileDialog.FilterIndex = 1;
openFileDialog.RestoreDirectory = true;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
//Get the path of specified file
filePath = openFileDialog.FileName;
;
// Open the document for reading.
using (SpreadsheetDocument spreadsheetDocument =
SpreadsheetDocument.Open(filePath, false))
{
bool countryDataFound = false;
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
WorksheetPart worksheetPart = GetWorksheetPart(workbookPart,
"icao");
SheetData sheetData = null;
if (worksheetPart != null)
{
countryDataFound = true;
sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
}
if (!countryDataFound)
{
MessageBox.Show("This File Does not contain the correct ICAO Aifield data sheet." +
"Please choose a different file.", "Invalid File!");
}
else
{
SharedStringTablePart stringTable =
workbookPart.GetPartsOfType <SharedStringTablePart>()
.FirstOrDefault();
Row r = null;
if (sheetData != null)
{
r = sheetData.Elements <Row>().Where(row => row.RowIndex == 1).First();
}
string nameColumn = null;
string icaoColumn = null;
string countryCodeColumn = null;
//Get Column Letter
char[] ch1 = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };
foreach (Cell c in r.Elements <Cell>())
{
string cellvalue = stringTable.SharedStringTable
.ElementAt(int.Parse(c.CellValue.InnerText)).InnerText;
if (cellvalue == "ISO 3-Letter")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
countryCodeColumn = tempString.Substring(0, index);
}
else if (cellvalue == "Name")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
nameColumn = tempString.Substring(0, index);
}
else if (cellvalue == "ICAO")
{
string tempString = c.CellReference;
int index = tempString.IndexOfAny(ch1);
icaoColumn = tempString.Substring(0, index);
}
}
if (nameColumn != null ||
countryCodeColumn != null ||
icaoColumn != null)
{
points.Clear();
foreach (Row row in sheetData)
{
if (row.RowIndex > 1)
{
string referenceString = nameColumn + row.RowIndex;
if (sheetData.Descendants <Cell>().
Where(c => c.CellReference == referenceString).
FirstOrDefault().CellValue.Text != null &&
sheetData.Descendants <Cell>().
Where(c => c.CellReference == referenceString).
FirstOrDefault().CellValue.Text != "")
{
string cellValue;
Cell cell;
referenceString = nameColumn + row.RowIndex;
string airfieldName;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
airfieldName = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
airfieldName = cellValue;
}
referenceString = countryCodeColumn + row.RowIndex;
string countryCode;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType == null)
{
countryCode = null;
}
else if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
countryCode = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
countryCode = cellValue;
}
referenceString = icaoColumn + row.RowIndex;
string icao;
cell = sheetData.Descendants <Cell>().Where(c => c.CellReference
== referenceString).FirstOrDefault();
if (cell.DataType == null)
{
icao = null;
}
else if (cell.DataType.Value == CellValues.SharedString)
{
cellValue = cell.CellValue.InnerText;
icao = stringTable.SharedStringTable
.ElementAt(int.Parse(cellValue)).InnerText;
}
else
{
cellValue = cell.CellValue.InnerText;
icao = cellValue;
}
Country country = null;
try
{
country = userPreferences.Countries.First(c => c.Code == countryCode) as Country;
}
catch (System.InvalidOperationException)
{
continue;
}
if (country != null)
{
ObservableCollection <Country> borderingCountries = new ObservableCollection <Country>();
borderingCountries.Add(country);
points.Add(icao, new Point(
icao,
true,
false,
false,
airfieldName,
borderingCountries));
}
else
{
points.Add(icao, new Point(
icao,
true,
false,
false,
airfieldName,
null));
}
}
}
}
userPreferences.SaveMe();
}
else
{
MessageBox.Show("This File Does not contain the correct ICAO Aifield data sheet." +
"Please choose a different file.", "Invalid File!");
}
}
}
}
}
}
public static void GenerateExcel(IEnumerable <IExcelRecord> records)
{
if (records.Any())
{
var firstRecord = records.First();
Type recordType = firstRecord.GetType();
var props = recordType.GetProperties();
string fileName = "TestExcelFile.XLSX";
using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// Add a WorkbookPart to the document.
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Tab1"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
// Constructing header
Row row = new Row();
foreach (var item in props)
{
var propattr = item.GetCustomAttributes(false);
object attr = (from attribute in propattr where attribute.GetType() == typeof(ExcelColumnAttribute) select attribute).FirstOrDefault();
if (attr == null)
{
continue;
}
ExcelColumnAttribute excelColumnAttribute = (ExcelColumnAttribute)attr;
row.Append(ConstructCell(excelColumnAttribute.DisplayName, CellValues.String));
}
// Insert the header row to the Sheet Data
sheetData.AppendChild(row);
// Inserting each record
foreach (var record in records)
{
var dataRow = new Row();
foreach (var property in props)
{
var propattr = property.GetCustomAttributes(false);
object attr = (from attribute in propattr where attribute.GetType() == typeof(ExcelColumnAttribute) select attribute).FirstOrDefault();
if (attr == null)
{
continue;
}
var value = property.GetValue(record);
//var value = property.GetValue(property, null);
dataRow.Append(ConstructCell(value.ToString(), CellValues.String));
}
sheetData.AppendChild(dataRow);
}
worksheetPart.Worksheet.Save();
}
}
}
/*
* report.GetReportData()
*/
public ActionResult Export(Reports report)
{
try
{
var reportData = report.GetReportData(true);
HttpContext.Response.SetCookie(new HttpCookie("fileDownload", "true")
{
Path = "/"
});
var context = HttpContext.Response;
context.Buffer = context.BufferOutput = false;
context.Cache.SetCacheability(HttpCacheability.Private);
context.Cache.SetExpires(DateTime.Now);
context.ContentType = new ContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
CharSet = "utf-8"
}.ToString();
context.AppendHeader("Content-Disposition",
new ContentDisposition
{
DispositionType = DispositionTypeNames.Attachment,
FileName = string.Format(CultureInfo.InvariantCulture, RemoveInvalidFilePathCharacters(reportData.title) + "_{0:yyyyMMdd_HHmmss}.xlsx", DateTime.Now)
}.ToString()
);
context.AppendHeader("X-Content-Type-Options", "nosniff");
using (MemoryStream mDocument = new MemoryStream())
{
// Using SAX
using (SpreadsheetDocument document = SpreadsheetDocument.Create(mDocument, SpreadsheetDocumentType.Workbook))
{
List <OpenXmlAttribute> attributes;
document.AddWorkbookPart();
// Stylesheet
WorkbookStylesPart stylesheet = document.WorkbookPart.AddNewPart <WorkbookStylesPart>();
stylesheet.Stylesheet = new Stylesheet(new Fonts(
new Font(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "000000"
}
}), // 0
new Font(new Bold()), // 1
new Font(new Color()
{
Rgb = new HexBinaryValue()
{
Value = "FF0000"
}
}) // 2
),
new Fills(new Fill()
{
}),
new Borders(new Border()
{
}),
new CellFormats(
new CellFormat()
{
FontId = 0
}, // 0
new CellFormat()
{
FontId = 1, ApplyFont = true
}, // 1
new CellFormat()
{
FontId = 2, ApplyFont = true
} // 2
)
);
stylesheet.Stylesheet.Save();
WorksheetPart workSheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
OpenXmlWriter writer = OpenXmlWriter.Create(workSheetPart);
writer.WriteStartElement(new Worksheet());
writer.WriteStartElement(new SheetData());
IDictionary <string, object> firstRow = new Dictionary <string, object>()
{
{ Words.Reports_Name, "" },
{ Words.Reports_Value, "" }
};
if (firstRow != null)
{
int row = 1;
AddLine(writer, row, new string[] { reportData.title }); row++;
AddLine(writer, row, new string[] { "" }); row++;
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col1 = 1;
foreach (var cols in firstRow.Keys.ToList())
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col1) + row),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(cols));
writer.WriteEndElement();
col1++;
}
writer.WriteEndElement();
row++;
for (int i = 0; i < reportData.labels.Length; i++)
{
attributes =
new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, row.ToString())
};
writer.WriteStartElement(new Row(), attributes);
int col = 1;
var row2 = new Dictionary <string, object>()
{
{ "Name", reportData.labels[i] },
{ "Value", reportData.datasets[0].data[i] }
};
foreach (var key in row2.Keys)
{
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(col) + row)
};
if (row2[key] is decimal)
{
if ((decimal)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
else if (row2[key] is double)
{
if ((double)row2[key] < 0)
{
attributes.Add(new OpenXmlAttribute("s", "", "2")); // Red (Style 2)
}
}
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(row2[key] != null ? row2[key].ToString() : ""));
writer.WriteEndElement();
col++;
}
writer.WriteEndElement();
row++;
}
}
else
{
// Empty row (no data found)
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("r", null, "1")
};
writer.WriteStartElement(new Row(), attributes);
attributes = new List <OpenXmlAttribute>
{
new OpenXmlAttribute("t", null, "str"),
new OpenXmlAttribute("r", "", GetColumnName(1) + 1),
new OpenXmlAttribute("s", "", "1") // Bold (Style 1)
};
writer.WriteStartElement(new Cell(), attributes);
writer.WriteElement(new CellValue(""));
writer.WriteEndElement();
writer.WriteEndElement();
}
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
writer = OpenXmlWriter.Create(document.WorkbookPart);
writer.WriteStartElement(new Workbook());
writer.WriteStartElement(new Sheets());
writer.WriteElement(new Sheet()
{
Name = "Sheet 1",
SheetId = 1,
Id = document.WorkbookPart.GetIdOfPart(workSheetPart)
});
writer.WriteEndElement();
writer.WriteEndElement();
writer.Close();
document.Save();
document.Close();
mDocument.WriteTo(context.OutputStream);
}
}
}
catch (Exception ex)
{
ex.Log();
}
return(null);
}
static void WriteRandomValuesSAX(string filename, int numRows, int numCols)
{
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open(filename, true))
{
WorkbookPart workbookPart = myDoc.WorkbookPart;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
string origninalSheetId = workbookPart.GetIdOfPart(worksheetPart);
WorksheetPart replacementPart = workbookPart.AddNewPart <WorksheetPart>();
string replacementPartId = workbookPart.GetIdOfPart(replacementPart);
OpenXmlReader reader = OpenXmlReader.Create(worksheetPart);
OpenXmlWriter writer = OpenXmlWriter.Create(replacementPart);
Row r = new Row();
Cell c = new Cell();
CellFormula f = new CellFormula();
f.CalculateCell = true;
f.Text = "RAND()";
c.Append(f);
CellValue v = new CellValue();
c.Append(v);
while (reader.Read())
{
if (reader.ElementType == typeof(SheetData))
{
if (reader.IsEndElement)
{
continue;
}
writer.WriteStartElement(new SheetData());
for (int row = 0; row < numRows; row++)
{
writer.WriteStartElement(r);
for (int col = 0; col < numCols; col++)
{
writer.WriteElement(c);
}
writer.WriteEndElement();
}
writer.WriteEndElement();
}
else
{
if (reader.IsStartElement)
{
writer.WriteStartElement(reader);
}
else if (reader.IsEndElement)
{
writer.WriteEndElement();
}
}
}
reader.Close();
writer.Close();
Sheet sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Id.Value.Equals(origninalSheetId))
.First();
sheet.Id.Value = replacementPartId;
workbookPart.DeletePart(worksheetPart);
}
}
private static void WriteDataTableToExcelWorksheet(DataTable dt, WorksheetPart worksheetPart, bool writeHeader)
{
var worksheet = worksheetPart.Worksheet;
var sheetData = worksheet.GetFirstChild <SheetData>();
string cellValue = "";
// Create a Header Row in our Excel file, containing one header for each Column of data in our DataTable.
//
// We'll also create an array, showing which type each column of data is (Text or Numeric), so when we come to write the actual
// cells of data, we'll know if to write Text values or Numeric cell values.
int numberOfColumns = dt.Columns.Count;
bool[] IsNumericColumn = new bool[numberOfColumns];
string[] excelColumnNames = new string[numberOfColumns];
for (int n = 0; n < numberOfColumns; n++)
{
excelColumnNames[n] = GetExcelColumnName(n);
}
//
// Create the Header row in our Excel Worksheet
//
uint rowIndex = 1;
var headerRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(headerRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
DataColumn col = dt.Columns[colInx];
if (writeHeader)
{
AppendTextCell(excelColumnNames[colInx] + "1", col.ColumnName, headerRow);
}
IsNumericColumn[colInx] = (col.DataType.FullName == "System.Decimal") || (col.DataType.FullName == "System.Int32") ||
col.ColumnName.StartsWith("Count") || col.ColumnName.StartsWith("Amount") || col.ColumnName.StartsWith("Budget");
}
//
// Now, step through each row of data in our DataTable...
//
double cellNumericValue = 0;
foreach (DataRow dr in dt.Rows)
{
// ...create a new row, and append a set of this row's data to it.
++rowIndex;
var newExcelRow = new Row {
RowIndex = rowIndex
}; // add a row at the top of spreadsheet
sheetData.Append(newExcelRow);
for (int colInx = 0; colInx < numberOfColumns; colInx++)
{
cellValue = dr.ItemArray[colInx].ToString();
// Create cell with data
if (IsNumericColumn[colInx])
{
// For numeric cells, make sure our input data IS a number, then write it out to the Excel file.
// If this numeric value is NULL, then don't write anything to the Excel file.
cellNumericValue = 0;
if (double.TryParse(cellValue, out cellNumericValue))
{
cellValue = cellNumericValue.ToString();
AppendNumericCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
else
{
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
else
{
// For text cells, just write the input data straight out to the Excel file.
AppendTextCell(excelColumnNames[colInx] + rowIndex.ToString(), cellValue, newExcelRow);
}
}
}
}
/// <summary>
/// Add cell width styles.
/// </summary>
/// <param name="minCol">Minimum column index.</param>
/// <param name="maxCol">Maximum column index.</param>
/// <param name="maxWidth">Maximum column width.</param>
/// <param name="spreadSheet">Spread sheet.</param>
/// <param name="workSheetPart">Work sheet.</param>
private static void AddCellWidthStyles(uint minCol, uint maxCol,
int maxWidth, SpreadsheetDocument spreadSheet,
WorksheetPart workSheetPart)
{
Columns cols = new Columns(new Column()
{
CustomWidth = true,
Min = minCol,
Max = maxCol,
Width = maxWidth,
BestFit = false
});
workSheetPart.Worksheet.InsertBefore<Columns>(cols,
workSheetPart.Worksheet.GetFirstChild<SheetData>());
}
