public void WhenCloneCell_ThenCellIsCloned()
{
string outputPath = Path.ChangeExtension(Path.Combine("TestData", MethodBase.GetCurrentMethod().Name), ".xlsx");
byte[] templateData = File.ReadAllBytes(@"TestData\StudentProfileExportTemplate.xltx");
using (MemoryStream stream = new MemoryStream())
{
stream.Write(templateData, 0, (int)templateData.Length);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
Worksheet worksheet = spreadsheetDoc.WorkbookPart.WorksheetParts.First().Worksheet;
ExcelUtility.CopyCell(worksheet, "D", 2, "E", 2);
}
File.WriteAllBytes(outputPath, stream.ToArray());
}
using (XLWorkbook workbook = new XLWorkbook(outputPath))
{
IXLWorksheet worksheet = workbook.Worksheet(1);
IXLCell originalCell = worksheet.Cell(2, "D");
IXLCell clonedCell = worksheet.Cell(2, "E");
Assert.AreEqual(originalCell.DataType, clonedCell.DataType);
Assert.AreEqual(originalCell.FormulaA1, clonedCell.FormulaA1);
Assert.AreEqual(originalCell.FormulaR1C1, clonedCell.FormulaR1C1);
Assert.AreEqual(originalCell.RichText.Text, clonedCell.RichText.Text);
Assert.AreEqual(originalCell.ShareString, clonedCell.ShareString);
Assert.AreEqual(originalCell.Style, clonedCell.Style);
Assert.AreEqual(originalCell.Value, clonedCell.Value);
Assert.AreEqual(originalCell.ValueCached, clonedCell.ValueCached);
}
}
private String ChangeExtension1(String filename)
{
String newName = filename;
byte[] byteArray = File.ReadAllBytes(filename);
using (MemoryStream stream = new MemoryStream())
{
stream.Write(byteArray, 0, (int)byteArray.Length);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
// Change from template type to workbook type
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
}
File.WriteAllBytes(newName = Path.ChangeExtension(filename, "xlsx"), stream.ToArray());
}
return(newName);
}
static void Main(string[] args)
{
if (args.Length != 2)
{
Console.WriteLine("Invalid number of arguments");
Console.WriteLine(@"Usage: Converter C:\FilePath\fileName.xlsm C:\filePath\fileNewName.xlsx");
Environment.Exit(1);
}
string fPath = args[0];
string fNewFile = args[1];
if (!File.Exists(fPath))
{
Console.WriteLine("File: " + fPath + " not found");
Environment.Exit(1);
}
try
{
byte[] byteArray = File.ReadAllBytes(fPath);
using (MemoryStream stream = new MemoryStream())
{
stream.Write(byteArray, 0, (int)byteArray.Length);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
spreadsheetDoc.DeletePartsRecursivelyOfType <VbaDataPart>();
spreadsheetDoc.DeletePartsRecursivelyOfType <VbaProjectPart>();
//Change from template type to workbook type
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
}
File.WriteAllBytes(fNewFile, stream.ToArray());
}
}
catch (Exception e)
{
Console.WriteLine("Error: " + e.Message);
Environment.Exit(1);
}
Environment.Exit(0);
}
public void InitializeFrom(string templatePath, IWorksheetWriter worksheetWriter)
{
if (worksheetWriter == null)
{
throw new ArgumentNullException("worksheetWriter");
}
byte[] byteArray = File.ReadAllBytes(templatePath);
MemoryStream stream = new MemoryStream(byteArray);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(stream, true))
{
// Change from template type to workbook type
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
WorksheetPart worksheetPart = ExcelUtility.GetWorksheetPartByName(spreadsheetDoc, worksheetWriter.SheetName);
if (worksheetPart != null)
{
worksheetWriter.CreateHeader(worksheetPart);
}
}
FileContentStream = stream;
}
public static void Run([BlobTrigger("files-stage/xlsm/{name}.xlsm")] Stream xlsmBlob, string name, ILogger log, [Blob("files-stage/files/{name}.xlsx", FileAccess.Write)] Stream xlsxBlob)
{
log.LogInformation($"C# Blob trigger function Processed blob\n Name:{name} \n Size: {xlsmBlob.Length} Bytes");
using (MemoryStream interim = new MemoryStream())
{
xlsmBlob.CopyTo(interim);
using (SpreadsheetDocument spreadsheetDoc = SpreadsheetDocument.Open(interim, true))
{
spreadsheetDoc.DeletePartsRecursivelyOfType <VbaDataPart>();
spreadsheetDoc.DeletePartsRecursivelyOfType <VbaProjectPart>();
// Change from template type to workbook type
spreadsheetDoc.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
}
var byteArray = interim.ToArray();
xlsxBlob.Write(byteArray, 0, byteArray.Length);
}
}
/// <summary>
/// Function to convert a macro enabled file to a non-macro enabled file
/// </summary>
/// <param name="fileName">file location</param>
/// <param name="app">app type</param>
/// <returns></returns>
public static string ConvertMacroEnabled2NonMacroEnabled(string fileName, string app)
{
bool fileChanged = false;
string newFileName = string.Empty;
string fileExtension;
if (app == "Word")
{
fileExtension = ".docx";
using (WordprocessingDocument document = WordprocessingDocument.Open(fileName, true))
{
// Access the main document part.
var docPart = document.MainDocumentPart;
// Look for the vbaProject part. If it is there, delete it.
var vbaPart = docPart.VbaProjectPart;
if (vbaPart != null)
{
// Delete the vbaProject part and then save the document.
docPart.DeletePart(vbaPart);
docPart.Document.Save();
// Change the document type to not macro-enabled.
document.ChangeDocumentType(WordprocessingDocumentType.Document);
// Track that the document has been changed.
fileChanged = true;
}
}
}
else if (app == "PowerPoint")
{
fileExtension = ".pptx";
using (PresentationDocument document = PresentationDocument.Open(fileName, true))
{
var docPart = document.PresentationPart;
var vbaPart = docPart.VbaProjectPart;
if (vbaPart != null)
{
docPart.DeletePart(vbaPart);
docPart.Presentation.Save();
document.ChangeDocumentType(PresentationDocumentType.Presentation);
fileChanged = true;
}
}
}
else
{
fileExtension = ".xlsx";
using (SpreadsheetDocument document = SpreadsheetDocument.Open(fileName, true))
{
var docPart = document.WorkbookPart;
var vbaPart = docPart.VbaProjectPart;
if (vbaPart != null)
{
docPart.DeletePart(vbaPart);
docPart.Workbook.Save();
document.ChangeDocumentType(SpreadsheetDocumentType.Workbook);
fileChanged = true;
}
}
}
// If anything goes wrong in this file handling,
// the code will raise an exception back to the caller.
if (fileChanged)
{
// Create the new filename.
newFileName = Path.ChangeExtension(fileName, fileExtension);
// If it already exists, it will be deleted!
if (File.Exists(newFileName))
{
File.Delete(newFileName);
}
// Rename the file.
File.Move(fileName, newFileName);
}
return(newFileName);
}
/// <summary>
/// Creates the workbook to which all sheets will be added, using the specified template file
/// </summary>
/// <returns>The newly created <see cref="SpreadsheetDocument"/></returns>
private SpreadsheetDocument CreateTargetWorkbook()
{
Trace.Indent();
try
{
SpreadsheetDocument targetWorkbook = null;
SpreadsheetDocument workbook = null;
try
{
// we will either use a template from a path they specify OR one of the built in templates
if (!string.IsNullOrEmpty(Settings.TemplateFilePath) && File.Exists(Settings.TemplateFilePath))
{
Trace.WriteLine(string.Format("Creating target workbook from template at path ({0})", Settings.TemplateFilePath), TraceCategory);
workbook = SpreadsheetDocument.CreateFromTemplate(Settings.TemplateFilePath);
workbook.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
targetWorkbook = (SpreadsheetDocument)workbook.SaveAs(OutputPath);
}
else
{
Trace.WriteLine(string.Format("Creating target workbook from compiled template ({0})", Settings.TemplateName), TraceCategory);
// this is done to juggle limitations in open xml creating a file from a stream where the stream is readonly
var resourceName = string.Format(BuiltInTemplateNameFormat, Settings.TemplateName);
if (Assembly.GetExecutingAssembly().GetManifestResourceNames().Contains(resourceName, StringComparer.OrdinalIgnoreCase))
{
using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream(resourceName))
{
workbook = SpreadsheetDocument.Open(stream, false);
var temp = workbook.SaveAs(OutputPath);
workbook.Close();
temp.Close(); // this allows us to reopen the file below
}
targetWorkbook = SpreadsheetDocument.Open(OutputPath, true);
targetWorkbook.ChangeDocumentType(SpreadsheetDocumentType.MacroEnabledWorkbook);
targetWorkbook.Save();
}
else
{
var message = string.Format("Could not locate embedded resource template {0}.", resourceName);
Trace.TraceError(message);
throw new Exception(message);
}
}
Trace.WriteLine(string.Format("Created target workbook at {0}", OutputPath), TraceCategory);
}
finally
{
if (workbook != null)
{
workbook.Dispose();
}
}
return(targetWorkbook);
}
catch (Exception err)
{
Trace.TraceError("Error creating output workbook from template");
Trace.TraceError(err.ToString());
throw;
}
finally
{
Trace.Unindent();
}
}
