public void SaveSheet(Package package, Sheet sheet)
{
var uri = new Uri($"/{sheet.Id}.json", UriKind.Relative);
package.DeletePart(uri);
var sheetPart = package.CreatePart(uri, "", CompressionOption.Normal);
using var stringStream = Streams.GenerateStreamFromString(JsonConvert.SerializeObject(sheet, Formatting.Indented));
using Stream dest = sheetPart.GetStream();
Streams.CopyStream(stringStream, dest);
}
private static VisionService GetVisionServiceByServiceAccount(
string googe_service_account_json,
string applicationName
)
{
string[] scopes = new string[] { VisionService.Scope.CloudPlatform }; // Put your scopes here
using (var s = Streams.GenerateStreamFromString(googe_service_account_json)) {
var credential = GoogleCredential.FromStream(s);
credential = credential.CreateScoped(scopes);
var service = new VisionService(new BaseClientService.Initializer()
{
HttpClientInitializer = credential,
ApplicationName = applicationName,
});
return(service);
}
}
public IActionResult Save(string file, string activeSheetId)
{
if (!System.IO.File.Exists(file))
{
return(BadRequest());
}
using (var p = Package.Open(file, FileMode.Open, FileAccess.ReadWrite, FileShare.Read))
{
var docUri = new Uri("/Document.json", UriKind.Relative);
var documentpart = p.GetPart(docUri);
Document document;
using (var stream = documentpart.GetStream(FileMode.Open))
{
var reader = new StreamReader(stream);
document = JsonConvert.DeserializeObject <Document>(reader.ReadToEnd());
}
document.Date = DateTime.Now;
var cachedSheets = cache.GetSheets();
foreach (var cachedSheet in cachedSheets)
{
var sheet = cachedSheet;
if (document.Sheets.ContainsKey(cachedSheet.Id))
{
if (sheet.Deleted)
{
document.Sheets.Remove(sheet.Id);
documentService.RemoveSheet(p, sheet.Id);
continue;
}
sheet = documentService.LoadSheet(p, cachedSheet.Id);
}
document.Sheets[sheet.Id] = sheet.Name;
if (sheet.Id == activeSheetId)
{
document.ActiveSheetId = activeSheetId;
}
foreach (var cahcedRow in cachedSheet.Rows.ToList())
{
if (sheet.Rows.ContainsKey(cahcedRow.Key))
{
var row = sheet.Rows[cahcedRow.Key];
foreach (var cachedCell in cahcedRow.Value.Cells.ToList())
{
row.Cells[cachedCell.Key] = cachedCell.Value;
}
}
else
{
sheet.Rows[cahcedRow.Key] = cahcedRow.Value;
}
}
documentService.SaveSheet(p, sheet);
}
p.DeletePart(docUri);
var docPart = p.CreatePart(docUri, "", CompressionOption.Normal);
using var stringStream = Streams.GenerateStreamFromString(JsonConvert.SerializeObject(document, Formatting.Indented));
using var dest = docPart.GetStream();
Streams.CopyStream(stringStream, dest);
}
return(Ok());
}
public static int Convert(string inputFile, string outFile)
{
if (!File.Exists(inputFile))
{
Console.Out.WriteLine($"File {inputFile} does not exist");
return(-1);
}
if (File.Exists(outFile))
{
Console.Out.WriteLine($"File {outFile} already exist");
return(-1);
}
Console.Out.WriteLine($"Processing file {inputFile}...");
using (var zip = Package.Open(outFile, FileMode.CreateNew))
{
var document = new Document
{
Date = DateTime.Now,
IsArchived = false,
Sheets = new Dictionary <string, string>()
};
using (var stream = File.Open(inputFile, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration
{
UseHeaderRow = false,
FilterRow = (rowReader) => rowReader.ResultsCount > 0,
FilterColumn = (rowReader, columnIndex) => columnIndex < 3
}
});
foreach (DataTable table in result.Tables)
{
if (table.Rows.Count == 0)
{
Console.Out.WriteLine($"Skipping empty sheet {table.TableName}");
continue;
}
Console.Out.WriteLine($"Processing sheet {table.TableName}");
var sheet = new Sheet
{
Id = Guid.NewGuid().ToString("D"),
Name = table.TableName,
Columns = new List <Column>
{
new Column
{
Name = "A"
},
new Column
{
Name = "B"
},
new Column
{
Name = "C"
}
}
};
document.Sheets.Add(sheet.Id, sheet.Name);
var i = 0;
foreach (DataRow r in table.Rows)
{
var row = new Row
{
Cells = new Dictionary <string, string>()
};
var y = 0;
foreach (var o in r.ItemArray.Take(3))
{
row.Cells.Add(y.ToString(), o.ToString());
y++;
}
sheet.Rows.Add(i.ToString(), row);
i++;
}
var sheetUri = PackUriHelper.CreatePartUri(new Uri(".\\" + Path.GetFileName(sheet.Id + ".json"), UriKind.Relative));
if (zip.PartExists(sheetUri))
{
zip.DeletePart(sheetUri);
}
var sheetPart = zip.CreatePart(sheetUri, "", CompressionOption.Normal);
using (var stringStream = Streams.GenerateStreamFromString(JsonConvert.SerializeObject(sheet, Formatting.Indented)))
{
using (Stream dest = sheetPart.GetStream())
{
Streams.CopyStream(stringStream, dest);
}
}
}
}
}
var uri = PackUriHelper.CreatePartUri(new Uri(".\\" + Path.GetFileName("Document.json"), UriKind.Relative));
if (zip.PartExists(uri))
{
zip.DeletePart(uri);
}
var docPart = zip.CreatePart(uri, "", CompressionOption.Normal);
using (var stringStream = Streams.GenerateStreamFromString(JsonConvert.SerializeObject(document, Formatting.Indented)))
{
using (Stream dest = docPart.GetStream())
{
Streams.CopyStream(stringStream, dest);
}
}
}
return(0);
}