/// <summary>
/// Write xlsx spreadsheet file of a list of T objects
/// Maximum of 24 columns
/// </summary>
/// <typeparam name="T">Type of objects passed in</typeparam>
/// <param name="fileName">Full path filename for the new spreadsheet</param>
/// <param name="def">A sheet definition used to create the spreadsheet</param>
public static void Create <T>(
string fileName,
SheetDefinition <T> def)
{
// open a template workbook
using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create workbook part
var workbookPart = myWorkbook.AddWorkbookPart();
// add stylesheet to workbook part
var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
// create workbook
var workbook = new Workbook();
// add work sheet
var sheets = new Sheets();
sheets.AppendChild(CreateSheet(1, def, workbookPart));
workbook.AppendChild(sheets);
// add workbook to workbook part
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
/// <summary>
/// 将DataTable转化为XML输出
/// </summary>
/// <param name="dataSet">dataSet</param>
/// <param name="fileName">文件名称</param>
//public void DataTableToXML(DataTable dataTable, string fileName)
public void DataSetToXML(DataSet dataSet, string filePath)
{
Dictionary <String, List <OpenXmlElement> > sets = ToSheets(dataSet);
using (SpreadsheetDocument package = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = package.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
Sheets sheets = workbookpart.Workbook.AppendChild(new Sheets());
foreach (KeyValuePair <String, List <OpenXmlElement> > set in sets)
{
WorksheetPart worksheetpart = workbookpart.AddNewPart <WorksheetPart>();
worksheetpart.Worksheet = new Worksheet(new SheetData(set.Value));
worksheetpart.Worksheet.Save();
Sheet sheet = new Sheet()
{
Id = workbookpart.GetIdOfPart(worksheetpart),
SheetId = (uint)(sheets.Count() + 1),
Name = set.Key
};
sheets.AppendChild(sheet);
}
workbookpart.Workbook.Save();
}
}
/// <summary>
/// Write xlsx spreadsheet file of a list of T objects
/// Maximum of 24 columns
/// </summary>
/// <typeparam name="T">Type of objects passed in</typeparam>
/// <param name="fileName">Full path filename for the new spreadsheet</param>
/// <param name="defs">A list of sheet definitions used to create the spreadsheet</param>
public static void Create <T>(
string fileName,
IEnumerable <SheetDefinition <T> > defs)
{
// open a template workbook
using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create workbook part
var workbookPart = myWorkbook.AddWorkbookPart();
// add stylesheet to workbook part
var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
styles.Save(stylesPart);
// create workbook
var workbook = new Workbook();
// add work sheets
var sheets = new Sheets();
var list = defs.ToList();
for (var i = 0; i < list.Count(); i++)
{
sheets.AppendChild(CreateSheet(i + 1, list[i], workbookPart));
}
workbook.AppendChild(sheets);
// add workbook to workbook part
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
internal static WorksheetPart AppendNewSheet(SpreadsheetDocument doc, WorksheetPart worksheetPart, string sheetName = null)
{
Sheets sheets = doc.WorkbookPart.Workbook.GetFirstChild <Sheets>();
if (sheets == null)
{
sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets());
}
uint sheetId = 1;
if (sheets.Elements <Sheet>().Count() > 0)
{
sheetId =
sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = $"Sheet{sheetId}";
}
sheets.AppendChild(new Sheet()
{
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = sheetId,
Name = sheetName
});
return(worksheetPart);
}
public static Stream ToXLSX2(TranslationModule project)
{
MemoryStream xlsStream = new MemoryStream();
var worksheet = new MemoryWorksheet();
Export.ToIWorksheet(project, worksheet);
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(xlsStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = myDoc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = myDoc.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "mySheet"
};
sheets.AppendChild(sheet);
int cellIdx = 0;
for (uint row = 0; row < worksheet.Rows; row++)
{
var r = new Row();
r.RowIndex = row;
for (int col = 0; col < worksheet.Columns; col++)
{
/*
* var c = new Cell();
* c.DataType = CellValues.String;
* //string val = worksheet[row, col] != null ? worksheet[row, col].ToString() : "";
* string val = "test;";
* c.CellValue = new CellValue(val);
*
* r.InsertAt(c, col);
*/
var c = CreateTextCell(ColumnLetter(cellIdx++), row, "test");
r.AppendChild(c);
}
sheetData.AppendChild(r);
}
worksheetPart.Worksheet.Save();
workbookPart.Workbook.Save();
myDoc.Close();
}
return(xlsStream);
}
public void CreateExcelDoc(string fileName)
{
using (SpreadsheetDocument document = SpreadsheetDocument
.Create(fileName, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
List <Employee> employees = Employees.EmployeesList;
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet()
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = "Employees"
};
//Construct header
Row row = new Row();
row.Append(
ConstructCell("Id", CellValues.String),
ConstructCell("Name", CellValues.String),
ConstructCell("Birth Date", CellValues.String),
ConstructCell("Salary", CellValues.String));
sheetData.AppendChild(row);
//Add data
foreach (var employee in employees)
{
row = new Row();
row.Append(
ConstructCell(employee.Id.ToString(), CellValues.Number),
ConstructCell(employee.Name, CellValues.String),
ConstructCell(employee.DOB.ToString("yyyy/MM/dd"), CellValues.String),
ConstructCell(employee.Salary.ToString(CultureInfo.CurrentCulture), CellValues.Number));
sheetData.AppendChild(row);
}
sheets.AppendChild(sheet);
//workbookPart.Workbook.Save();
worksheetPart.Worksheet.Save();
}
}
/// <summary>
/// Write xlsx spreadsheet file of a list of T objects
/// Maximum of 24 columns
/// </summary>
/// <typeparam name="T">Type of objects passed in</typeparam>
/// <param name="fileName">Full path filename for the new spreadsheet</param>
/// <param name="def">A sheet definition used to create the spreadsheet</param>
public static void Create <T>(string fileName, SheetDefinition <T>[] defs)
{
// open a template workbook
using (var myWorkbook = SpreadsheetDocument.Create(fileName, SpreadsheetDocumentType.Workbook))
{
// create workbook part
var workbookPart = myWorkbook.AddWorkbookPart();
// add stylesheet to workbook part
var stylesPart = myWorkbook.WorkbookPart.AddNewPart <WorkbookStylesPart>();
Stylesheet styles = new CustomStylesheet();
//styles.Append(new CellFormats());
//styles.CellFormats.Append(new CellFormat());
//styles.CellFormats.Append(new CellFormat() { ApplyNumberFormat = true, NumberFormatId = 22 });
styles.Save(stylesPart);
//var cc = myWorkbook.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ToList();
// create workbook
var workbook = new Workbook();
int sheetIndex = 1;
var sheets = new Sheets();
foreach (var def in defs)
{
// add work sheet
sheets.AppendChild(CreateSheet(sheetIndex, def, workbookPart));
sheetIndex++;
}
workbook.AppendChild(sheets);
CreateShareStringPart(myWorkbook);
// add workbook to workbook part
myWorkbook.WorkbookPart.Workbook = workbook;
myWorkbook.WorkbookPart.Workbook.Save();
myWorkbook.Close();
}
}
private void WriteWorksheets(WorkbookPart workbookPart, SaveContext context)
{
XlSharedStringsTable stringsTable = new XlSharedStringsTable(workbookPart, context);
Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
foreach (XlWorksheet sheet in Worksheets)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(context.RelIdGenerator.GetNext(RelType.Workbook));
SheetData sheetData = new SheetData();
Worksheet worksheet = new Worksheet(sheetData);
worksheetPart.Worksheet = worksheet;
if (
!worksheet.NamespaceDeclarations.Contains(new KeyValuePair <String, String>("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships")))
{
worksheet.AddNamespaceDeclaration("r",
"http://schemas.openxmlformats.org/officeDocument/2006/relationships");
}
var sheetProperties = new SheetProperties {
TabColor = new TabColor()
};
string colorString = sheet.TabColor.HtmlColor;
sheetProperties.TabColor.Rgb = colorString;
worksheet.SheetProperties = sheetProperties;
AddTableData(sheet, sheetData, stringsTable);
worksheet.Save();
Sheet s = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = (uint)(sheets.Count() + 1),
Name = sheet.Name
};
sheets.AppendChild(s);
workbookPart.Workbook.Save();
}
}
private OpenXmlWriter CreateWriter()
{
var workbookPart = document.AddWorkbookPart();
var worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
var sheets = new Sheets();
var sheet = new Sheet
{
Id = workbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = sheetName
};
sheets.AppendChild(sheet);
var workBook = new Workbook();
workbookPart.Workbook = workBook;
workBook.AppendChild(sheets);
return(OpenXmlWriter.Create(worksheetPart, Encoding.UTF8));
}
public ActionResult UsersExcel()
{
UsersRetrieveReq req = null;
RequestParameter para = new RequestParameter();
para.Load(Request);
req = new UsersRetrieveReq();
JsonConvert.PopulateObject(para.Item("json"), req);
var memoryStream = new MemoryStream();
using (var document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
#region Sheet1
SheetData sheetData = new SheetData();
Row row;
#region header
row = new Row(); //header
row.Append(
new Cell()
{
CellValue = new CellValue("序號")
},
new Cell()
{
CellValue = new CellValue("帳號")
},
new Cell()
{
CellValue = new CellValue("姓名")
},
new Cell()
{
CellValue = new CellValue("密碼")
},
new Cell()
{
CellValue = new CellValue("電子郵件")
},
new Cell()
{
CellValue = new CellValue("狀態")
},
new Cell()
{
CellValue = new CellValue("備註")
},
new Cell()
{
CellValue = new CellValue("建檔時間")
},
new Cell()
{
CellValue = new CellValue("建檔人員")
},
new Cell()
{
CellValue = new CellValue("異動時間")
},
new Cell()
{
CellValue = new CellValue("異動人員")
}
);
sheetData.AppendChild(row);
#endregion
#region data
UsersRetrieveRes res = new KYL_CMS.Models.BusinessLogic.Users("SCC").ReportData(req);
foreach (USERS data in res.USERS) //data
{
row = new Row();
row.Append(
new Cell()
{
CellValue = new CellValue(data.SN.ToString())
},
new Cell()
{
CellValue = new CellValue(data.ID)
},
new Cell()
{
CellValue = new CellValue(data.NAME.ToString())
},
new Cell()
{
CellValue = new CellValue(data.PASSWORD.ToString())
},
new Cell()
{
CellValue = new CellValue(data.EMAIL == null ? "" : data.EMAIL)
},
new Cell()
{
CellValue = new CellValue(data.MODE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MEMO == null ? "" : data.MEMO)
},
new Cell()
{
CellValue = new CellValue(data.CDATE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.CUSER.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MDATE.ToString())
},
new Cell()
{
CellValue = new CellValue(data.MUSER.ToString())
}
);
sheetData.AppendChild(row);
}
#endregion
Worksheet worksheet = new Worksheet();
worksheet.Append(sheetData);
worksheetPart.Worksheet = worksheet; //add a Worksheet to the WorksheetPart
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "工作表1"
});
#endregion
}
memoryStream.Seek(0, SeekOrigin.Begin);
return(File(memoryStream.ToArray(), "application/vnd.ms-excel", "TEST.xlsx"));
}
/// <summary>
/// Método para exportar a formato Excel una colección de objetos, a partir de un XML OpenXML.
/// </summary>
/// <typeparam name="T">Clase que define a la colección de objetos</typeparam>
/// <param name="_data">Colección de objetos tipo T a exportar</param>
/// <param name="_columnsToPrint">Arreglo de cadenas con los nombres de las propiedades a imprimir</param>
/// <param name="_dateTimeFormat">Representa el formato de impresión para propiedades del objeto tipo DateTime</param>
/// <param name="_printHeader">Indica si se imprime los títulos en la cabecera</param>
/// <param name="_sheetName">Nombre de la hoja activa del libro</param>
/// <returns>Regresa un Tuple con el archivo generado en el primer item
/// y un comentario en el segundo item</returns>
/// <remarks>El nombre de la columna se toma del atributo DisplatAttribute</remarks>
public Tuple <byte[], string> ExportToExcelOpenXml <T>(IEnumerable <T> _data, IEnumerable <string> _columnsToPrint = null, string _dateTimeFormat = "dd/MM/yyyy", bool _printHeader = true, string _sheetName = null) where T : class
{
try
{
if (_data == null || !_data.Any())
{
return(new Tuple <byte[], string>(null, String.Empty));
}
System.IO.MemoryStream stream = new System.IO.MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
SheetData sheetData = new SheetData();
worksheetPart.Worksheet = new Worksheet(sheetData);
Sheets sheets = document.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
Sheet sheet = new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(worksheetPart),
SheetId = 1,
Name = _sheetName ?? Resources.DefaultSheetName
};
sheets.AppendChild(sheet);
AddStyleSheet(document);
PropertyInfo[] _properties = typeof(T).GetProperties();
UInt32 rowIdex = 0;
var row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
var cellIdex = 0;
bool _print;
if (_printHeader)
{
for (int _counter = 0; _counter < _properties.Length; _counter++)
{
_print = true;
if (_columnsToPrint != null && _columnsToPrint.Any())
{
_print = _columnsToPrint.Select(x => x.ToLowerInvariant())
.Contains(_properties[_counter].Name.ToLowerInvariant());
}
if (_print)
{
var _displayAttribute =
_properties[_counter].GetCustomAttributes(false)
.FirstOrDefault(a => a is DisplayAttribute) as DisplayAttribute;
row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _displayAttribute != null
? GetDisplayAttributeFromResourceType(_displayAttribute)
: _properties[_counter].Name, 1));
}
}
}
foreach (var x in _data)
{
cellIdex = 0;
row = new Row {
RowIndex = ++rowIdex
};
sheetData.AppendChild(row);
for (int _counter = 0; _counter < _properties.Length; _counter++)
{
_print = true;
if (_columnsToPrint != null && _columnsToPrint.Any())
{
_print = _columnsToPrint.Select(y => y.ToLowerInvariant())
.Contains(_properties[_counter].Name.ToLowerInvariant());
}
if (_print)
{
Cell cell;
var _value = _properties[_counter].GetValue(x);
switch (_value != null ? _value.GetType().ToString() : string.Empty)
{
case "":
case "System.String":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? _value.ToString() : string.Empty);
break;
case "System.DateTime":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null
? ((DateTime)_value).ToString(_dateTimeFormat)
: string.Empty);
break;
case "System.Boolean":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? ((bool)_value ? ResourcesXMLExcel.TrueValue : ResourcesXMLExcel.FalseValue) : string.Empty);
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
case "System.Decimal":
case "System.Double":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, _value != null ? _value.ToString() : string.Empty);
break;
case "System.DBNull":
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, Resources.NullObject);
break;
default:
cell = CreateTextCell(ColumnLetter(cellIdex++), rowIdex, Resources.DefaultTypeText);
break;
}
row.AppendChild(cell);
}
}
}
workbookpart.Workbook.Save();
document.Close();
return(new Tuple <byte[], string>(stream.ToArray(), null));
}
catch (Exception ex)
{
return(new Tuple <byte[], string>(null, Resources.ExceptionText + ex.Message));
}
}
public void Save()
{
SpreadsheetDocument xl = SpreadsheetDocument.Create(FileOutputPath, SpreadsheetDocumentType.Workbook);
WorkbookPart wbp = xl.AddWorkbookPart();
WorksheetPart wsp = wbp.AddNewPart <WorksheetPart>();
Workbook wb = new Workbook();
FileVersion fv = new FileVersion {
ApplicationName = "Microsoft Office Excel"
};
Worksheet ws = new Worksheet();
SheetData sd = new SheetData();
uint rowIndex = 1;
Row rowHeaders = new Row {
RowIndex = rowIndex++
};
string[] headers = { "Codice Ordine", "Totale (€)", "Data & Ora" };
foreach (string header in headers)
{
Cell cell = new Cell
{
DataType = CellValues.String,
CellValue = new CellValue(header)
};
rowHeaders.AppendChild(cell);
}
sd.AppendChild(rowHeaders);
foreach (Order order in SavingOrders)
{
Row row = new Row {
RowIndex = rowIndex
};
Cell cell = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(order.Id.ToString())
};
Cell cell2 = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(order.TotalPrice.ToString("##.##")),
StyleIndex = 4
};
Cell cell3 = new Cell
{
DataType = CellValues.Date,
CellValue = new CellValue(order.DateTime.ToString("dd-MM-yyyy HH:mm:ss"))
};
row.Append(cell, cell2, cell3);
sd.AppendChild(row);
rowIndex++;
}
Row rowFine = new Row {
RowIndex = rowIndex
};
//Cell cellTextSum = new Cell
//{
// DataType = CellValues.String,
// CellValue = new CellValue("Totale (€):"),
//};
Cell cellSum = new Cell
{
DataType = CellValues.Number,
CellValue = new CellValue(SavingOrders.Sum(x => x.TotalPrice).ToString("##.##")),
CellReference = "B" + rowIndex
};
rowFine.AppendChild(cellSum);
sd.AppendChild(rowFine);
ws.AppendChild(sd);
wsp.Worksheet = ws;
wsp.Worksheet.Save();
Sheets sheets = new Sheets();
Sheet sheet = new Sheet
{
Name = Path.GetFileNameWithoutExtension(FileOutputPath),
SheetId = 1,
Id = wbp.GetIdOfPart(wsp)
};
sheets.AppendChild(sheet);
wb.Append(fv, sheets);
xl.WorkbookPart.Workbook = wb;
xl.WorkbookPart.Workbook.Save();
xl.Close();
}
/// <summary>
/// 生成多个sheet的Excel文件
/// </summary>
/// <param name="dataItems"></param>
/// <param name="shNames">sheet name</param>
/// <param name="filename">生成的excel文件名(包含路径)</param>
/// <param name="doMerge">true: 存在合并单元格/false: 不存在合并单元格</param>
/// <param name="customFormat">true: 有自定义样式/false: 没有自定义样式</param>
public void GenerateXlsxFile(List <BaseSheetData> exlsSheetData, string[] shNames, string filename, bool doMerge = false, bool customFormat = false)
{
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheetDoc.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
Sheets sheets = spreadsheetDoc.WorkbookPart.Workbook.AppendChild(new Sheets());
SharedStringTablePart tbpart = null;
var stylePart = workbookPart.AddNewPart <WorkbookStylesPart>();
List <SheetCellItem> allCells = new List <SheetCellItem>();
for (uint i = 0; i < exlsSheetData.Count; i++)
{
ExlsSheetData xlsxSheetData = exlsSheetData[(int)i] as ExlsSheetData;
if (xlsxSheetData != null)
{
allCells.AddRange(xlsxSheetData.AllCells);
}
}
if (allCells.Any())
{
if (customFormat)
{
stylePart.Stylesheet = GenerateStylesheet(allCells);
}
else
{
stylePart.Stylesheet = DefaultStylesheet(); //GenerateDefaultStylesheet(allCells);
}
stylePart.Stylesheet.Save();
}
if (allCells.Any(x => x.DataType == DataTypes.SharedString))
{
tbpart = workbookPart.AddNewPart <SharedStringTablePart>();
}
for (uint i = 0; i < exlsSheetData.Count; i++)
{
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
ExlsSheetData xlsxSheetData = exlsSheetData[(int)i] as ExlsSheetData;
if (xlsxSheetData != null)
{
worksheetPart.Worksheet = new Worksheet();
if (customFormat)
{
var cols = GenerateColumns(xlsxSheetData.AllCells);
if (cols != null)
{
worksheetPart.Worksheet.Append(cols);
}
}
else
{
foreach (var c in xlsxSheetData.AllCells)
{
c.FormatIndex = c.RowIndex.Equals(1) ? 1u : 2u;
}
}
worksheetPart.Worksheet.Append(CreateSheetData(xlsxSheetData.SheetRows, tbpart));
if (doMerge)
{
DoMerge(xlsxSheetData.AllCells, worksheetPart.Worksheet);
}
}
UInt32Value id = UInt32Value.FromUInt32(i + 1);
Sheet sheet = new Sheet {
Id = spreadsheetDoc.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = id, Name = shNames[i]
};
sheets.AppendChild(sheet);
}
workbookPart.Workbook.Save();
}
}
public async Task AddSheet <T>(IEnumerable <T> model, string sheetName) where T : class
{
if (model == default)
{
throw new ArgumentNullException(nameof(model));
}
if (string.IsNullOrWhiteSpace(sheetName))
{
throw new ArgumentException(nameof(sheetName));
}
await Task.Run(() =>
{
Sheet sheet = new Sheet()
{
Id = WorkbookPart.GetIdOfPart(WorksheetPart),
SheetId = _sheetId++,
Name = sheetName
};
Sheets.AppendChild(sheet);
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
Row headerRow = new Row();
foreach (PropertyInfo property in properties)
{
headerRow.AppendChild(new Cell()
{
DataType = CellValues.String,
CellValue = new CellValue(Localization.Get(property.Name))
});
}
SheetData.AppendChild(headerRow);
WorkbookPart.Workbook.Save();
Type stringType = typeof(string);
foreach (T value in model)
{
Row row = new Row();
foreach (PropertyInfo property in properties)
{
object propertyValue = property.GetValue(value);
if (propertyValue is IEnumerable enumerable && propertyValue.GetType() != stringType)
{
string tempResult = string.Empty;
foreach (object childResult in enumerable)
{
if (childResult != null)
{
tempResult += $"{childResult}, ";
}
}
propertyValue = tempResult.Length > 0 ? tempResult.Substring(0, tempResult.Length - 2) : tempResult;
}
Cell cell = new Cell()
{
CellValue = new CellValue(propertyValue?.ToString() ?? string.Empty)
};
if (propertyValue is long || propertyValue is ulong || propertyValue is double || propertyValue is decimal)
{
cell.DataType = CellValues.Number;
}
else if (propertyValue is bool)
{
cell.DataType = CellValues.Boolean;
}
else
{
cell.DataType = CellValues.String;
}
row.AppendChild(cell);
}
SheetData.AppendChild(row);
WorkbookPart.Workbook.Save();
}
});
}
private static Sheet CreateProjectSheet(SpreadsheetDocument document,
WorkbookPart workbookpart,
Sheets sheets,
int ProjectID,
int month,
int year,
RATV3Entities db,
uint sheetID = 1)
{
#region GetData
//getting the department
Project project = db.Projects.Where(x => x.ProjectID == ProjectID).FirstOrDefault();
Department department = db.Departments.Where(x => x.DepartmentID == project.DepartmentID).FirstOrDefault();
//setting the date interval for our Sprints
DateTime firstDayOfMonth = new DateTime(year, month, 1);
DateTime lastDayOfMonth = firstDayOfMonth.AddMonths(1).AddTicks(-1);
while (firstDayOfMonth.DayOfWeek != DayOfWeek.Monday)
{
firstDayOfMonth = firstDayOfMonth.AddDays(-1);
}
while (lastDayOfMonth.DayOfWeek != DayOfWeek.Sunday)
{
lastDayOfMonth = lastDayOfMonth.AddDays(1);
}
//GETTING SPRINTS + SPRINT IDS
List <Sprint> sprints = db.Sprints.Where(x => x.ProjectID == ProjectID &&
(DbFunctions.TruncateTime(x.StartDate) >= firstDayOfMonth.Date &&
DbFunctions.TruncateTime(x.EndDate) <= lastDayOfMonth.Date))
.OrderBy(x => x.StartDate).ToList();
int[] sprintIds = new int[] { 0 };
if (sprints != null && sprints.Count > 0)
{
sprintIds = sprints.Select(x => x.SprintID).ToArray();
}
//Getting Employees and Tasks
List <Employee> employees = db.Employees.Where(x => x.DepartmentID == department.DepartmentID).ToList();
List <Task> tasks = db.Tasks.Where(x => x.SprintID.HasValue && sprintIds.Contains(x.SprintID.Value)).ToList();
#endregion
WorksheetPart newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
string relationshipId = document.WorkbookPart.GetIdOfPart(newWorksheetPart);
var sheet = new Sheet()
{
Id = relationshipId,
SheetId = sheetID,
Name = project.Title
};
sheets.AppendChild(sheet);
var sheetData = new SheetData();
newWorksheetPart.Worksheet = new Worksheet(sheetData);
//create header rows
DateTime monthDate = new DateTime(year, month, 1);
List <string> MergeableRows = new List <string>();
Row projectRow = CreateProjectRow(firstDayOfMonth, lastDayOfMonth, project, MergeableRows, monthDate, 1);
Row sprintRow = CreateSprintRow(firstDayOfMonth, lastDayOfMonth, sprints, MergeableRows, 2);
Row headerRow = CreateDatesRow(firstDayOfMonth, lastDayOfMonth, 3);
sheetData.AppendChild(projectRow);
sheetData.AppendChild(sprintRow);
sheetData.AppendChild(headerRow);
//Merging cells
MergeCells mergeCells = new MergeCells();
for (int i = 0; i < MergeableRows.Count; i += 2)
{
mergeCells.Append(new MergeCell()
{
Reference = new StringValue(MergeableRows[i] + ":" + MergeableRows[i + 1])
});
}
//creating content rows
int colIndex = 4;
foreach (Employee emp in employees)
{
//creating row for this employee
List <Task> empTasks = tasks.Where(x => x.EmployeeID == emp.EmployeeID).ToList();
Row row = CreateEmployeeRow(firstDayOfMonth, lastDayOfMonth, colIndex, emp, empTasks);
sheetData.AppendChild(row);
colIndex++;
}
//set col width
Columns columns = new Columns();
columns.Append(new Column()
{
Min = 1, Max = 1, Width = 20, CustomWidth = true
});
columns.Append(new Column()
{
Min = 2, Max = 100, Width = 12, CustomWidth = true
});
newWorksheetPart.Worksheet.Append(columns);
//merging cells
newWorksheetPart.Worksheet.InsertAfter(mergeCells, newWorksheetPart.Worksheet.Elements <SheetData>().First());
newWorksheetPart.Worksheet.Save();
return(sheet);
}
public void WriteExcelModeOfPaymentFile(DataTable table, string path, string firstLine)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
WorkbookStylesPart stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
SheetData sheetData = new SheetData();
//add a row
Row firstRow = new Row();
//firstRow.RowIndex = (UInt32)1;
//create a cell in C1 (the upper left most cell of the merged cells)
Cell dataCell = new Cell();
dataCell.CellReference = "A1";
dataCell.DataType = CellValues.String;
CellValue cellValue = new CellValue();
cellValue.Text = firstLine;
dataCell.Append(cellValue);
firstRow.AppendChild(dataCell);
sheetData.AppendChild(firstRow);
// Add a WorkbookPart to the document.
worksheetPart.Worksheet = new Worksheet(sheetData);
//create a MergeCells class to hold each MergeCell
MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
mergeCells.Append(new MergeCell()
{
Reference = new StringValue("A1:J1")
});
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements <SheetData>().First());
//this is the part that was missing from your code
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Sheet1"
});
Row headerRow = new Row();
List <String> columns = new List <string>();
//skip//
foreach (System.Data.DataColumn column in table.Columns)
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(column.ColumnName);
headerRow.AppendChild(cell);
}
sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == " SALES" || col == "CASH" || col == "M-PESA" || col == "CREDIT CARD" || col == "DEBIT CARD" || col == "CHQEUE")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
workbookpart.Workbook.Save();
}
}
public void Export(DataTable dt, string excelFilePath, string header)
{
if (dt == null || excelFilePath == string.Empty)
{
throw new ArgumentNullException();
}
var fi = new FileInfo(excelFilePath);
#region 文件已存在
if (File.Exists(excelFilePath) && fi.Length != 0)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Open(excelFilePath, true))
{
Sheet sheet =
document.WorkbookPart.Workbook.Descendants <Sheet>()
.FirstOrDefault(s => s.Name == EscapeSheetNameInvalidChar(dt.TableName));
if (sheet != null)
{
var worksheetPart = (WorksheetPart)(document.WorkbookPart.GetPartById(sheet.Id));
sheet.Remove();
document.WorkbookPart.DeletePart(worksheetPart);
document.WorkbookPart.Workbook.Save();
}
// document.AddWorkbookPart();
// document.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
// document.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
// WorkbookStylesPart workbookStylesPart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
// Stylesheet stylesheet = new Stylesheet();
// workbookStylesPart.Stylesheet = stylesheet;
uint sheetId = 1;
var newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
var sheets = document.WorkbookPart.Workbook.GetFirstChild <Sheets>();
if (sheets == null)
{
throw new OpenXmlPackageException("已有Excel文件损坏，请删除");
}
// create the worksheet to workbook relation
//if (worksheetNumber == 1)
// document.WorkbookPart.Workbook.AppendChild(new DocumentFormat.OpenXml.Spreadsheet.Sheets());
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
sheets.AppendChild(new Sheet
{
Id = document.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = sheetId,
Name = EscapeSheetNameInvalidChar(dt.TableName)
});
document.WorkbookPart.Workbook.Save();
Trace.WriteLine("成功创建: " + excelFilePath);
}
}
#endregion
#region 新建文件
else
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(excelFilePath,
SpreadsheetDocumentType.Workbook))
{
document.AddWorkbookPart();
document.WorkbookPart.Workbook = new Workbook();
// My thanks to James Miera for the following line of code (which prevents crashes in Excel 2010)
// document.WorkbookPart.Workbook.Append(new BookViews(new WorkbookView()));
// If we don't add a "WorkbookStylesPart", OLEDB will refuse to connect to this .xlsx file !
// WorkbookStylesPart workbookStylesPart = document.WorkbookPart.AddNewPart<WorkbookStylesPart>("rIdStyles");
// Stylesheet stylesheet = new Stylesheet();
// workbookStylesPart.Stylesheet = stylesheet;
uint sheetId = 1;
var newWorksheetPart = document.WorkbookPart.AddNewPart <WorksheetPart>();
newWorksheetPart.Worksheet = new Worksheet(new SheetData());
HeaderFooter headerFooter = new HeaderFooter();
headerFooter.FirstHeader = new FirstHeader();
headerFooter.FirstHeader.Text = header;
newWorksheetPart.Worksheet.AppendChild <HeaderFooter>(headerFooter);;
// save worksheet
WriteDataTableToExcelWorksheet(dt, newWorksheetPart);
newWorksheetPart.Worksheet.Save();
var sheets = new Sheets();
document.WorkbookPart.Workbook.AppendChild(sheets);
if (sheets.Elements <Sheet>().Any())
{
sheetId = sheets.Elements <Sheet>().Select(s => s.SheetId.Value).Max() + 1;
}
sheets.AppendChild(new Sheet
{
Id = document.WorkbookPart.GetIdOfPart(newWorksheetPart),
SheetId = sheetId,
Name = EscapeSheetNameInvalidChar(dt.TableName)
});
document.WorkbookPart.Workbook.Save();
}
Trace.WriteLine("成功创建: " + excelFilePath);
}
#endregion
}
public async Task <string> GetXlsxAsync(IReadOnlyCollection <Department> departments)
{
if (departments == null)
{
throw new InvalidOperationException("尚未進行選股，無法匯出。");
}
if (departments.Count == 0)
{
throw new InvalidOperationException("股別數為零，無法匯出。");
}
if (departments.Select(x => x.Students).Sum(x => x.Count) == 0)
{
throw new InvalidOperationException("股內人數加總為零，無法匯出。");
}
var date = DateTime.Now;
var ms = new MemoryStream();
using (var spreadsheet = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookPart = spreadsheet.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
// Insert stylesheet to make the top row bold
var workbookStylesPart = workbookPart.AddNewPart <WorkbookStylesPart>();
workbookStylesPart.Stylesheet = CreateStylesheet();
// Define sheet
var sheetName = $"{date:yyyy}-股員名單";
WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
Sheets sheets = spreadsheet.WorkbookPart.Workbook.AppendChild(new Sheets());
Sheet sheet = new Sheet {
Id = spreadsheet.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = sheetName
};
sheets.AppendChild(sheet);
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild <SheetData>();
// Insert data
var row = new Row {
RowIndex = 1
};
row.Append(
new Cell {
CellValue = new CellValue("學號"), DataType = CellValues.String
},
new Cell {
CellValue = new CellValue("姓名"), DataType = CellValues.String
},
new Cell {
CellValue = new CellValue("股別"), DataType = CellValues.String
});
sheetData.AppendChild(row);
uint rowIndex = 2;
foreach (var department in departments)
{
foreach (var student in department.Students)
{
var entryRow = new Row {
RowIndex = rowIndex
};
entryRow.Append(
new Cell {
CellValue = new CellValue(student.Id), DataType = CellValues.String, StyleIndex = 1
},
new Cell {
CellValue = new CellValue(student.Name), DataType = CellValues.String, StyleIndex = 1
},
new Cell {
CellValue = new CellValue(student.Department.Name), DataType = CellValues.String, StyleIndex = 1
});
sheetData.AppendChild(entryRow);
rowIndex++;
}
}
// Add title
spreadsheet.PackageProperties.Title = $"{date:yyyy} 選股結果";
spreadsheet.PackageProperties.Creator = "RaffleBlazor 選股程式";
}
return(await ReadStreamToBase64(ms));
}
private static void BuildWorkbook(string filename, int count = 3)
{
try
{
using (SpreadsheetDocument doc = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = doc.AddWorkbookPart();
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
WorkbookStylesPart workbookStylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
workbookpart.Workbook = new Workbook();
worksheetPart.Worksheet = new Worksheet();
workbookStylesPart.Stylesheet = CreateStylesheet();
Sheets sheets = doc.WorkbookPart.Workbook.AppendChild(new Sheets());
Columns columns = worksheetPart.Worksheet.AppendChild(new Columns());
//顺序在columns之后
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
#region sheets
var sheet = sheets.AppendChild(new Sheet
{
Name = "产品",
SheetId = 1,
Id = doc.WorkbookPart.GetIdOfPart(worksheetPart),
});
#endregion
#region cols
columns.Append(new Column()
{
Min = 1,
Max = 1,
Width = 38.22d,
CustomWidth = true
});
columns.Append(CreateColumnData(2, 4, 20));
columns.Append(CreateColumnData(6, 6, 6.5703125));
#endregion
#region autofilter
worksheetPart.Worksheet.AppendChild(new AutoFilter
{
Reference = "B1"
});
#endregion autofilter
#region header
Row r;
Cell c;
// header
r = new Row();
c = new Cell();
c.DataType = CellValues.String;
c.CellReference = "A1";
c.CellValue = new CellValue("产品ID");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
c.CellReference = "B1";
c.CellValue = new CellValue("产品说明");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
c.CellReference = "C1";
c.CellValue = new CellValue("订单说明");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
c.CellReference = "D1";
c.CellValue = new CellValue("折扣说明");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
c.CellReference = "E1";
c.CellValue = new CellValue("货币");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
c.CellReference = "F1";
c.CellValue = new CellValue("费用");
r.Append(c);
sheetData.Append(r);
#endregion
#region data
for (int i = 0; i < count; i++)
{
r = new Row();
c = new Cell();
c.DataType = CellValues.String;
// c.CellReference = "A2";
c.CellValue = new CellValue("15D05473-742F-4691-8BF4-5124F2D66176");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
// c.CellReference = "B2";
c.CellValue = new CellValue("Iced Lemon Tea");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
// c.CellReference = "C2";
c.CellValue = new CellValue("Special Iced Lemon Tea");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
// c.CellReference = "D2";
c.CellValue = new CellValue("Iced Lemon Tea (50% off)");
r.Append(c);
c = new Cell();
c.DataType = CellValues.String;
// c.CellReference = "E2";
c.CellValue = new CellValue("USD");
r.Append(c);
c = new Cell();
c.StyleIndex = 3;
c.DataType = CellValues.Number;
// c.CellReference = "F2";
c.CellValue = new CellValue(i.ToString());
r.Append(c);
c = new Cell();
c.DataType = CellValues.Boolean;
// c.CellReference = "G2";
c.CellValue = new CellValue("0");
r.Append(c);
sheetData.Append(r);
}
#endregion
doc.Save();
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.ReadLine();
}
}
public JsonResult ExportExcel(string SSN = "", string DEPSEQ = "", string claim = "", string Dependent = "", string SortingColumn = "", string Orderby = "", DateTime? Fromdate = null, DateTime? Todate = null)
{
DataTable ExcelDatatable = new DataTable();
var fileName = "ClaimDetails" + ".xlsx";
string fullPath = Path.Combine(Server.MapPath("~/ExcelFile"), fileName);
try
{
DataTable dt = new DataTable();
if (DEPSEQ == null || DEPSEQ == "0")
{
dt = Db2Connnect.GetDataTable(GetSqlQuery.GeTMemberClaims(SSN, claim, Fromdate, Todate, Dependent, SortingColumn, Orderby, 0, 0), CommandType.Text);
}
else
{
dt = Db2Connnect.GetDataTable(GetSqlQuery.GeTDependentClaims(SSN, DEPSEQ, claim, Fromdate, Todate, Dependent, SortingColumn, Orderby, 0, 0), CommandType.Text);
}
ExcelDatatable = GetClaimDetailExportDatatable(dt);
using (SpreadsheetDocument myDoc = SpreadsheetDocument.Create(fullPath, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = myDoc.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
Worksheet ws = new Worksheet();
SheetData sheetData = new SheetData();
WorkbookStylesPart wbsp = workbookpart.AddNewPart<WorkbookStylesPart>();
wbsp.Stylesheet = GenerateStylesheet();
wbsp.Stylesheet.Save();
Columns columns = new Columns();
columns.Append(CreateColumnData(1, 1, 11));
columns.Append(CreateColumnData(2, 3, 16));
columns.Append(CreateColumnData(4, 4, 20));
columns.Append(CreateColumnData(5, 5, 11));
columns.Append(CreateColumnData(6, 6, 28));
columns.Append(CreateColumnData(7, 9, 11));
ws.Append(columns);
//add a row
Row firstRow = new Row();
firstRow.RowIndex = (UInt32)1;
Cell dataCell = new Cell();
CellValue cellValue = new CellValue();
firstRow = new Row();
dataCell = new Cell();
dataCell.DataType = CellValues.String;
dataCell.CellReference = "A1";
dataCell.StyleIndex = 2;
dataCell.CellValue = new CellValue("Filtered By");
firstRow.Append(dataCell);
sheetData.Append(firstRow);
firstRow = new Row();
dataCell = new Cell();
dataCell.DataType = CellValues.String;
dataCell.CellReference = "A2";
dataCell.StyleIndex = 0;
dataCell.CellValue = new CellValue("From Date : " + ((Fromdate == null) ? "ALL" : Fromdate?.ToString("MM/dd/yyyy")));
firstRow.Append(dataCell);
sheetData.Append(firstRow);
firstRow = new Row();
dataCell = new Cell();
dataCell.DataType = CellValues.String;
dataCell.CellReference = "A3";
dataCell.StyleIndex = 0;
dataCell.CellValue = new CellValue("To Date : " + ((Todate == null) ? "ALL" : Todate?.ToString("MM/dd/yyyy")));
firstRow.Append(dataCell);
sheetData.Append(firstRow);
firstRow = new Row();
dataCell = new Cell();
dataCell.DataType = CellValues.String;
dataCell.CellReference = "A4";
dataCell.StyleIndex = 0;
dataCell.CellValue = new CellValue("DEPN# : " + ((DEPSEQ == "") ? "ALL" : DEPSEQ));
firstRow.Append(dataCell);
sheetData.Append(firstRow);
firstRow = new Row();
dataCell = new Cell();
dataCell.DataType = CellValues.String;
dataCell.CellReference = "A5";
dataCell.StyleIndex = 0;
dataCell.CellValue = new CellValue("CLAIM# : " + ((claim == "") ? "ALL" : claim));
firstRow.Append(dataCell);
sheetData.Append(firstRow);
firstRow = new Row();
List<String> columns1 = new List<string>();
foreach (System.Data.DataColumn column in ExcelDatatable.Columns)
{
columns1.Add(column.ColumnName);
dataCell = new Cell();
dataCell.DataType = CellValues.String;
dataCell.StyleIndex = 2;
dataCell.CellValue = new CellValue(column.ColumnName);
firstRow.Append(dataCell);
}
sheetData.Append(firstRow);
foreach (DataRow dsrow in ExcelDatatable.Rows)
{
firstRow = new Row();
foreach (String col in columns1)
{
dataCell = new Cell();
dataCell.DataType = CellValues.String;
//c.CellReference = "A2";
dataCell.StyleIndex = 1;
dataCell.CellValue = new CellValue(dsrow[col].ToString());
firstRow.Append(dataCell);
}
sheetData.Append(firstRow);
}
ws.Append(sheetData);
worksheetPart.Worksheet = ws;
MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:I1") });
mergeCells.Append(new MergeCell() { Reference = new StringValue("A2:I2") });
mergeCells.Append(new MergeCell() { Reference = new StringValue("A3:I3") });
mergeCells.Append(new MergeCell() { Reference = new StringValue("A4:I4") });
mergeCells.Append(new MergeCell() { Reference = new StringValue("A5:I5") });
worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
//this is the part that was missing from your code
Sheets sheets = myDoc.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = myDoc.WorkbookPart.GetIdOfPart(myDoc.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Sheet1"
});
//myDoc.WorkbookPart.Workbook = workbookpart.Workbook;
myDoc.WorkbookPart.Workbook.Save();
myDoc.Close();
}
}
catch (Exception)
{
throw;
}
return Json(new { fileName = fileName });
}
public void WriteExcelProductWiseSaleFile(DataTable table, string path, string firstLine)
{
using (SpreadsheetDocument document = SpreadsheetDocument.Create(path, SpreadsheetDocumentType.Workbook))
{
WorkbookPart workbookpart = document.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
WorkbookStylesPart stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();
stylesPart.Stylesheet = GenerateStyleSheet();
stylesPart.Stylesheet.Save();
SheetData sheetData = new SheetData();
//add a row
Row firstRow = new Row();
//firstRow.RowIndex = (UInt32)1;
//create a cell in C1 (the upper left most cell of the merged cells)
//Cell dataCell = new Cell();
//dataCell.CellReference = "A1";
//dataCell.DataType = CellValues.String;
//CellValue cellValue = new CellValue();
//cellValue.Text = firstLine;
//dataCell.Append(cellValue);
//firstRow.AppendChild(dataCell);
//sheetData.AppendChild(firstRow);
// Add a WorkbookPart to the document.
worksheetPart.Worksheet = new Worksheet(sheetData);
//create a MergeCells class to hold each MergeCell
//MergeCells mergeCells = new MergeCells();
//append a MergeCell to the mergeCells for each set of merged cells
//mergeCells.Append(new MergeCell() { Reference = new StringValue("A1:J1") });
//worksheetPart.Worksheet.InsertAfter(mergeCells, worksheetPart.Worksheet.Elements<SheetData>().First());
//this is the part that was missing from your code
Sheets sheets = document.WorkbookPart.Workbook.AppendChild(new Sheets());
sheets.AppendChild(new Sheet()
{
Id = document.WorkbookPart.GetIdOfPart(document.WorkbookPart.WorksheetParts.First()),
SheetId = 1,
Name = "Productwise Sales Report"
});
Row headerRow = new Row();
List <String> columns = new List <string>();
//Hide Coulumn name//
foreach (System.Data.DataColumn column in table.Columns)
{
if (column.ColumnName == "Id" || column.ColumnName == "RowNumber" | column.ColumnName == "FoodMenuName")
{
}
else
{
columns.Add(column.ColumnName);
Cell cell = new Cell();
cell.DataType = CellValues.String;
cell.CellValue = new CellValue(null);// (column.ColumnName);
headerRow.AppendChild(cell);
}
}
//sheetData.AppendChild(headerRow);
foreach (DataRow dsrow in table.Rows)
{
if (dsrow[4].ToString().Contains("Total"))
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == "Total" || col == "FoodMenuQty" || col == "SalesPrice")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
else if (dsrow[0].ToString().Contains("=="))
{
}
else
{
Row newRow = new Row();
foreach (String col in columns)
{
Cell cell = new Cell();
if (col == "Total" || col == "FoodMenuQty" || col == "SalesPrice")
{
cell.DataType = CellValues.Number;
cell.StyleIndex = 3;
}
else
{
cell.DataType = CellValues.String;
}
cell.CellValue = new CellValue(dsrow[col].ToString());
newRow.AppendChild(cell);
}
sheetData.AppendChild(newRow);
}
}
workbookpart.Workbook.Save();
}
}
