/// <summary>
/// Sets a column heading to a cell
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="stringValue">String value to set</param>
/// <param name="useSharedString">Use shared strings? If true and the string isn't found in shared strings, it will be added</param>
/// <param name="save">Save the worksheet</param>
/// <returns>True if succesful</returns>
public static bool SetColumnHeadingValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, string stringValue, bool useSharedString, bool save = true)
{
string columnValue = stringValue;
DocumentFormat.OpenXml.Spreadsheet.CellValues cellValueType;
// Add the shared string if necessary
if (useSharedString)
{
if (ExcelProc.IndexOfSharedString(spreadsheet, stringValue) == -1)
{
ExcelProc.AddSharedString(spreadsheet, stringValue, true);
}
columnValue = ExcelProc.IndexOfSharedString(spreadsheet, stringValue).ToString();
cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.SharedString;
}
else
{
cellValueType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
}
return(SetCellValue(spreadsheet, worksheet, columnIndex, 1, cellValueType, columnValue, 4, save));
//return SetCellValue(spreadsheet, worksheet, columnIndex, 2, cellValueType, columnValue, 4, save);
}
/// <summary>
/// Adds a list of strings to the shared strings table.
/// </summary>
/// <param name="spreadsheet">The spreadsheet</param>
/// <param name="stringList">Strings to add</param>
/// <returns></returns>
public static bool AddSharedStrings(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, System.Collections.Generic.List <string> stringList)
{
foreach (string item in stringList)
{
ExcelProc.AddSharedString(spreadsheet, item, false);
}
spreadsheet.WorkbookPart.SharedStringTablePart.SharedStringTable.Save();
return(true);
}
/// <summary>
/// Sets a cell value. The row and the cell are created if they do not exist. If the cell exists, the contents of the cell is overwritten
/// </summary>
/// <param name="spreadsheet">Spreadsheet to use</param>
/// <param name="worksheet">Worksheet to use</param>
/// <param name="columnIndex">Index of the column</param>
/// <param name="rowIndex">Index of the row</param>
/// <param name="valueType">Type of the value</param>
/// <param name="value">The actual value</param>
/// <param name="styleIndex">Index of the style to use. Null if no style is to be defined</param>
/// <param name="save">Save the worksheet?</param>
/// <returns>True if succesful</returns>
private static bool SetCellValue(DocumentFormat.OpenXml.Packaging.SpreadsheetDocument spreadsheet, DocumentFormat.OpenXml.Spreadsheet.Worksheet worksheet, uint columnIndex, uint rowIndex, DocumentFormat.OpenXml.Spreadsheet.CellValues valueType, string value, uint?styleIndex, bool save = true)
{
DocumentFormat.OpenXml.Spreadsheet.SheetData sheetData = worksheet.GetFirstChild <DocumentFormat.OpenXml.Spreadsheet.SheetData>();
DocumentFormat.OpenXml.Spreadsheet.Row row;
DocumentFormat.OpenXml.Spreadsheet.Row previousRow = null;
DocumentFormat.OpenXml.Spreadsheet.Cell cell;
DocumentFormat.OpenXml.Spreadsheet.Cell previousCell = null;
DocumentFormat.OpenXml.Spreadsheet.Columns columns;
DocumentFormat.OpenXml.Spreadsheet.Column previousColumn = null;
string cellAddress = ExcelProc.ColumnNameFromIndex(columnIndex) + rowIndex;
// Check if the row exists, create if necessary
if (sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).Count() != 0)
{
row = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == rowIndex).First();
}
else
{
row = new DocumentFormat.OpenXml.Spreadsheet.Row()
{
RowIndex = rowIndex
};
//sheetData.Append(row);
for (uint counter = rowIndex - 1; counter > 0; counter--)
{
previousRow = sheetData.Elements <DocumentFormat.OpenXml.Spreadsheet.Row>().Where(item => item.RowIndex == counter).FirstOrDefault();
if (previousRow != null)
{
break;
}
}
sheetData.InsertAfter(row, previousRow);
}
// Check if the cell exists, create if necessary
if (row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).Count() > 0)
{
cell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == cellAddress).First();
}
else
{
// Find the previous existing cell in the row
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousCell = row.Elements <DocumentFormat.OpenXml.Spreadsheet.Cell>().Where(item => item.CellReference.Value == ExcelProc.ColumnNameFromIndex(counter) + rowIndex).FirstOrDefault();
if (previousCell != null)
{
break;
}
}
cell = new DocumentFormat.OpenXml.Spreadsheet.Cell()
{
CellReference = cellAddress
};
row.InsertAfter(cell, previousCell);
}
// Check if the column collection exists
columns = worksheet.Elements <DocumentFormat.OpenXml.Spreadsheet.Columns>().FirstOrDefault();
if (columns == null)
{
columns = worksheet.InsertAt(new DocumentFormat.OpenXml.Spreadsheet.Columns(), 0);
}
// Check if the column exists
if (columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == columnIndex).Count() == 0)
{
// Find the previous existing column in the columns
for (uint counter = columnIndex - 1; counter > 0; counter--)
{
previousColumn = columns.Elements <DocumentFormat.OpenXml.Spreadsheet.Column>().Where(item => item.Min == counter).FirstOrDefault();
if (previousColumn != null)
{
break;
}
}
columns.InsertAfter(
new DocumentFormat.OpenXml.Spreadsheet.Column()
{
Min = columnIndex,
Max = columnIndex,
CustomWidth = true,
Width = 9
}, previousColumn);
}
// Add the value
cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(value);
if (styleIndex != null)
{
cell.StyleIndex = styleIndex;
}
if (valueType != DocumentFormat.OpenXml.Spreadsheet.CellValues.Date)
{
cell.DataType = new DocumentFormat.OpenXml.EnumValue <DocumentFormat.OpenXml.Spreadsheet.CellValues>(valueType);
}
if (save)
{
worksheet.Save();
}
return(true);
}
/// <summary>
/// Data Model To Excel
/// </summary>
/// <param name="model"></param>
/// <param name="data"></param>
/// <param name="title"></param>
/// <returns></returns>
public void DataToExcelEx(List <Dictionary <string, object> > data, string title, List <GridHeaderModel> headers)
{
if (data != null)
{
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
int colIdx = 0;
SpreadsheetDocument spreadsheet = ExcelProc.CreateWorkbook(stream);
ExcelProc.AddBasicStyles(spreadsheet);
ExcelProc.AddAdditionalStyles(spreadsheet);
if (title.Length > 31)
{
title = title.Substring(0, 28) + "...";
}
ExcelProc.AddWorksheet(spreadsheet, title);
Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
if (data.Count > 0)
{
// 타이틀 만들기
foreach (KeyValuePair <string, object> header in data[0])
{
foreach (var p in header.Value.GetType().GetProperties())
{
if (p.GetCustomAttributes(true).Contains(new ExcelExceptDataAttribute()) == false)
{
var match = headers.Find(c => c.field == p.Name);
if (match != null)
{
ExcelProc.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), match.title, false, false);
ExcelProc.SetColumnWidth(worksheet, colIdx + 1, 25);
colIdx++;
}
}
}
}
ExcelProc.SetCellValue(spreadsheet, worksheet, 1, 1, title + " (Restricted Document)", 4, false, false);
int rowIndex = 1;
// 내용 만들기
foreach (var list in data)
{
colIdx = 0;
foreach (KeyValuePair <string, object> body in list)
{
//colIdx = 0;
foreach (var p in body.Value.GetType().GetProperties())
{
if (p.GetCustomAttributes(true).Contains(new ExcelExceptDataAttribute()) == false)
{
var match = headers.Find(c => c.field == p.Name);
if (match != null)
{
ExcelProc.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), Convert.ToUInt32(rowIndex + 2), p.GetValue(body.Value, null) == null ? string.Empty : p.GetValue(body.Value, null).ToString(), false, false);
colIdx++;
}
}
}
}
rowIndex++;
}
}
worksheet.Save();
spreadsheet.Close();
byte[] buffer = stream.ToArray();
string filename = string.Format("{0}.xlsx", title);
//return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
}
}
else
{
throw new Exception("Datatype Error");
}
}
/// <summary>
/// Data Model To Excel
/// </summary>
/// <param name="model"></param>
/// <param name="data"></param>
/// <param name="title"></param>
/// <returns></returns>
public byte[] DataToExcel <T>(T data, string title)
{
if (data is IList)
{
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
SpreadsheetDocument spreadsheet = ExcelProc.CreateWorkbook(stream);
ExcelProc.AddBasicStyles(spreadsheet);
ExcelProc.AddAdditionalStyles(spreadsheet);
if (title.Length > 31)
{
title = title.Substring(0, 28) + "...";
}
ExcelProc.AddWorksheet(spreadsheet, title);
Worksheet worksheet = spreadsheet.WorkbookPart.WorksheetParts.First().Worksheet;
IList excelData = (data as IList);
if (excelData.Count > 0)
{
IList <PropertyInfo> PropertiesList = new List <PropertyInfo>();
int colIdx = 0;
foreach (var p in excelData[0].GetType().GetProperties())
{
if (p.GetCustomAttributes(true).Contains(new ExcelExceptDataAttribute()) == false)
{
PropertiesList.Add(p);
//
var display = p.GetCustomAttribute <DisplayAttribute>();
ExcelProc.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), display.Name, false, false); // 컬럼 헤더 ####
//ExcelProc.SetColumnHeadingValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), p.Name, false, false); // 컬럼 헤더 ####
ExcelProc.SetColumnWidth(worksheet, colIdx + 1, 25);
colIdx++;
}
}
//ExcelProc.SetCellValue(spreadsheet, worksheet, 1, 1, title + " (Restricted Document)", 4, false, false); //제목 ####
int rowIndex = 1;
foreach (var d in excelData)
{
colIdx = 0;
foreach (var p in PropertiesList)
{
ExcelProc.SetCellValue(spreadsheet, worksheet, Convert.ToUInt32(colIdx + 1), Convert.ToUInt32(rowIndex + 1), p.GetValue(d, null) == null ? string.Empty : p.GetValue(d, null).ToString(), false, false);
colIdx++;
}
rowIndex++;
}
}
worksheet.Save();
spreadsheet.Close();
byte[] buffer = stream.ToArray();
string filename = string.Format("{0}.xlsx", title);
return(buffer);
//return File(buffer, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename);
}
}
else
{
return(null);
}
}
