public string WriteCellData(IDictionary <string, object> data)
{
var cellRefRegex = new Regex(@"^([a-zA-Z]*)(\d*)$");
if (sharedStringTable == null)
{
sharedStringTable = workbookPart.AddNewPart <SharedStringTablePart>();
}
foreach (var key in data.Keys)
{
var sheetName = key.Split("!")[0];
var cellRef = key.Split("!")[1];
WorksheetPart wsPart = null;
var sheet = workbookPart.Workbook.Descendants <Sheet>().Where(s => s.Name == sheetName).FirstOrDefault();
if (sheet == null)
{
wsPart = InsertWorksheet(workbookPart, sheetName);
}
else
{
wsPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
}
var cellRefMatch = cellRefRegex.Matches(cellRef).FirstOrDefault();
var columnName = cellRefMatch.Groups[1].Value;
var rowIndex = (uint)Int32.Parse(cellRefMatch.Groups[2].Value);
var cell = InsertCellInWorksheet(columnName, rowIndex, wsPart);
var value = data[key];
int strIndex = -1;
if (value is string)
{
strIndex = InsertSharedStringItem(value.ToString());
cell.CellValue = new CellValue(strIndex.ToString());
cell.DataType = new EnumValue <CellValues>(CellValues.SharedString);
}
else
{
cell.CellValue = new CellValue(value.ToString());
}
wsPart.Worksheet.Save();
}
spreadsheet.WorkbookPart.Workbook.Save();
spreadsheet.Save();
spreadsheet.Close();
spreadsheet.Dispose();
return(Convert.ToBase64String(stream.ToArray()));
}
// 释放所有资源
public void Dispose()
{
if (stylesheet != null)
{
stylesheet.Save();
stylesheet = null;
}
if (workbookPart != null)
{
workbookPart.Workbook.Save();
workbookPart = null;
}
if (spreadsheetDocument != null)
{
spreadsheetDocument.Close();
spreadsheetDocument.Dispose();
spreadsheetDocument = null;
}
if (fileStream != null)
{
fileStream.Flush();
fileStream.Close();
fileStream.Dispose();
fileStream = null;
}
}
/// <summary>
/// Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
/// </summary>
public void Dispose()
{
if (_spreadsheetDocument != null)
{
_spreadsheetDocument.Dispose();
}
}
public MemoryStream Export <T>(IEnumerable <T> data, Dictionary <string, ExportInfo> exportInfos)
{
MemoryStream result = null;
if (data == null)
{
return(result);
}
SpreadsheetDocument spreadsheetDocument = null;
try
{
spreadsheetDocument = SpreadsheetDocument.Create(result = new MemoryStream(), SpreadsheetDocumentType.Workbook);
CreateParts(spreadsheetDocument, data, exportInfos);
spreadsheetDocument.Close();
}
finally
{
if (spreadsheetDocument != null)
{
spreadsheetDocument.Dispose();
}
}
return(result);
}
public void Dispose()
{
rowsWriter?.Dispose();
CreateAutoFilterDefinedName();
sheetWriter?.Close();
document?.Dispose();
}
protected virtual void Dispose(bool disposing)
{
if (!_disposed)
{
if (disposing)
{
if (_excelWkb != null)
{
Marshal.ReleaseComObject(_excelWkb);
}
if (_excelWkbs != null)
{
Marshal.ReleaseComObject(_excelWkbs);
}
if (_excelApp != null)
{
Marshal.ReleaseComObject(_excelApp);
}
if (_spreadsheetDoc != null)
{
_spreadsheetDoc.Dispose();
}
if (DeleteDecryptedFile && _decrypted)
{
File.Delete(_filename);
}
}
}
_disposed = true;
}
public void Dispose()
{
if (doc != null)
{
doc.Dispose();
}
}
protected internal override void HandleDataReadComplete(object sender, ExportCompleteArgs e)
{
if (_spreadSheetDocument != null)
{
_spreadSheetDocument.Dispose();
} // saves, flushes and disposes.
base.BubbleUpCompletedEvent(e);
}
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
doc?.Dispose();
}
disposed = true;
}
public void EndWriteHead()
{
_writer.WriteEndElement(); //end of SheetData
_writer.WriteEndElement(); //end of worksheet
_writer.Close();
_spreadsheet.Dispose();
}
/// <summary>
/// Clean up resources
/// </summary>
public void Dispose()
{
if (_doc != null)
{
_doc.Close();
_doc.Dispose();
_doc = null;
}
}
public int close()
{
_reader.Close();
_reader.Dispose();
_doc.Close();
_doc.Dispose();
_doc = null;
return(1);
}
public void Dispose()
{
if (_package != null)
_package.Dispose();
if (_outputStream != null)
_outputStream.Dispose();
if (!string.IsNullOrEmpty(_tempFilePath))
File.Delete(_tempFilePath);
}
// TODO: override a finalizer only if Dispose(bool disposing) above has code to free unmanaged resources.
// ~Reader() {
// // Do not change this code. Put cleanup code in Dispose(bool disposing) above.
// Dispose(false);
// }
// This code added to correctly implement the disposable pattern.
public void Dispose()
{
// Do not change this code. Put cleanup code in Dispose(bool disposing) above.
Dispose(true);
document.Close();
document.Dispose();
// TODO: uncomment the following line if the finalizer is overridden above.
GC.SuppressFinalize(this);
}
/// <summary>
/// Sauvegarde le fichier et le ferme.
/// L'excel builder n'est plus utilisable par la suite.
/// </summary>
public void SaveAndClose()
{
// Ne semble pas être correct : la validation lève des erreurs qui n'en sont en fait pas.
//var errors = new OpenXmlValidator(FileFormatVersions.Office2007).Validate(_package).ToArray();
//if (errors.Any())
// throw new InvalidOperationException("Des erreurs ont eu lieu car le fichier produit est incorrect");
_package.Dispose();
}
public void Dispose()
{
_workSheetWriter.Dispose();
_workBookWriter.Dispose();
_styleSheetWriter.Dispose();
_sharedStringWriter.Dispose();
_xl.Dispose();
}
/// <summary>
/// Dispose.
/// </summary>
public void Dispose( )
{
if (_document != null)
{
_document.Dispose( );
_document = null;
}
_stream.Dispose( );
}
public void Dispose()
{
_provider?.Dispose();
if (_ownStream)
{
_stream?.Dispose();
}
_document?.Dispose();
}
public void Dispose()
{
foreach (var key in _sheets.Keys)
{
_sheets[key].Save();
}
_spreadsheetDocument.Save();
_spreadsheetDocument.Dispose();
}
public Attachment AddFromList(string connString, string command)
{
SqlConnection sqlConnection = new SqlConnection(connString);
SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
sqlConnection.Open();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
MemoryStream memoryStream = new MemoryStream();
SpreadsheetDocument document = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);
WorkbookPart workbookPart = document.AddWorkbookPart();
workbookPart.Workbook = new Workbook();
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 = "Kullanıcılar"
};
sheets.Append(sheet);
workbookPart.Workbook.Save();
SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData());
Row row = new Row();
row.Append(getCell("Tam Adı", CellValues.String), getCell("Email", CellValues.String));
sheetData.AppendChild(row);
while (sqlDataReader.Read())
{
row = new Row();
row.Append(getCell(sqlDataReader.GetString(0), CellValues.String),
getCell(sqlDataReader.GetString(1), CellValues.String));
sheetData.AppendChild(row);
}
sqlConnection.Close();
worksheetPart.Worksheet.Save();
document.Save();
document.Close();
document.Dispose();
memoryStream.Seek(0, SeekOrigin.Begin);
Attachment attachment = new Attachment(memoryStream, "KullanıcıListesi.xlsx");
return(attachment);
}
public void Export(string outputFilePath, string sheetName, List <List <string> > contents, List <string> types, List <List <string> > describe)
{
SpreadsheetDocument document = CreateWorkbookPart(outputFilePath);
if (document != null)
{
CreateSheetPart(document.WorkbookPart, sheetName, contents, types, describe);
document.WorkbookPart.Workbook.Save();
document.Dispose();
}
}
public virtual void Dispose()
{
try
{
if (document != null)
{
document.Close();
document.Dispose();
}
}
catch { /*ignore any errors here*/ }
}
public void Dispose()
{
if (!_isDisposed)
{
_spreadsheetDocument.Dispose();
_spreadsheetDocument = default;
GC.SuppressFinalize(this);
_isDisposed = !_isDisposed;
}
}
public override void Dispose()
{
headerRow = null;
wbPart = null;
wsPart = null;
if (_SpreadsheetDocument != null)
{
_SpreadsheetDocument.Dispose();
}
base.Dispose();
}
/// <summary>
///
/// </summary>
/// <remarks></remarks>
/// <seealso cref=""/>
/// <param name="datasetId"></param>
/// <param name="datasetVersionOrderNr"></param>
/// <param name="dataStructureId"></param>
/// <param name="title"></param>
/// <param name="extention"></param>
/// <returns></returns>
public string CreateFile(long datasetId, long datasetVersionOrderNr, long dataStructureId, string title, string extention)
{
string dataPath = GetFullStorePath(datasetId, datasetVersionOrderNr, title, extention);
//Template will not be filtered by columns
if (this.VisibleColumns == null)
{
#region generate file with full datastructure
string dataStructureFilePath = GetDataStructureTemplatePath(dataStructureId, extention);
//dataPath = GetStorePath(datasetId, datasetVersionOrderNr, title, extention);
try
{
SpreadsheetDocument dataStructureFile = SpreadsheetDocument.Open(dataStructureFilePath, true);
SpreadsheetDocument dataFile = SpreadsheetDocument.Create(dataPath,
dataStructureFile.DocumentType);
foreach (OpenXmlPart part in dataStructureFile.GetPartsOfType <OpenXmlPart>())
{
OpenXmlPart newPart = dataFile.AddPart <OpenXmlPart>(part);
}
dataFile.WorkbookPart.Workbook.Save();
dataStructureFile.Dispose();
dataFile.Dispose();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
#endregion
}
// create a file with a subset of variables
if (this.VisibleColumns != null)
{
/// call templateprovider from rpm
ExcelTemplateProvider provider = new ExcelTemplateProvider();
string path = GetStorePath(datasetId, datasetVersionOrderNr);
string newTitle = GetNewTitle(datasetId, datasetVersionOrderNr, title, extention);
provider.CreateTemplate(getVariableIds(this.VisibleColumns), dataStructureId, path, newTitle);
}
return(dataPath);
}
public void Dispose()
{
if (_reader != null)
{
_reader.Close();
_reader.Dispose();
_reader = null;
}
if (_document != null)
{
_document.Dispose();
_document = null;
}
}
public void Dispose()
{
if (package != null)
{
package.Dispose();
}
if (outputStream != null)
{
outputStream.Dispose();
}
if (!string.IsNullOrEmpty(tempFilePath))
{
File.Delete(tempFilePath);
}
}
/// <summary>
/// Escribe la informacion de los grupos
/// </summary>
/// <param name="grupos">Nombre de la hoja que se quiere poner </param>
/// <param name="hoja">Hoja en la que se va a escribir</param>
public void EscribeGrupos(IList <Grupo> grupos, string hoja, IDictionary <string, string> materia = null, IDictionary <int, string> profesor = null)
{
SpreadsheetDocument doc = SpreadsheetDocument.Open(dir, true);
Worksheet worksheet = GetSheet(hoja, doc);
SheetData sheet = worksheet.GetFirstChild <SheetData>();
foreach (Grupo item in grupos)
{
EscribeGrupo(item, sheet, materia, profesor);
}
worksheet.Save();
doc.Close();
doc.Dispose();
}
private void DisposeOfSpreadSheet(bool closeItFirst)
{
if (_spreadSheet != null)
{
if (closeItFirst)
{
_spreadSheet.Close();
}
_spreadSheet.Dispose();
_spreadSheet = null;
_workbookpart = null;
_sheets = null;
_spreadSheetId = 0;
}
}
protected void Dispose(bool disposing)
{
if (!_disposed)
{
if (disposing)
{
this.Cleanup();
this.SharedStrings.Save();
}
// Clean unmanaged resources
_doc.Dispose();
_disposed = true;
}
}
