public async void InsertDataIntoSheet(string fileName, string sheetName, ExcelData data)
{
try
{
//Fix for https://github.com/OfficeDev/Open-XML-SDK/issues/221
// Environment.SetEnvironmentVariable("MONO_URI_DOTNETRELATIVEORABSOLUTE", "true");
await Task.Run(() =>
{
// Open the document for editing
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, true))
{
WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
SetSheetStyle(ref workbookPart);
//Set the sheet name on the first sheet
Sheets sheets = workbookPart.Workbook.GetFirstChild <Sheets>();
Sheet sheet = sheets.Elements <Sheet>().FirstOrDefault();
sheet.Name = sheetName;
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
SheetData sheetData = worksheetPart.Worksheet.Elements <SheetData>().First();
Row headerRow = ConstructHeader(data.Headers.ToArray());
sheetData.AppendChild(headerRow);//增加标题行
foreach (List <string> dataList in data.Values)
{
Row dataRow = sheetData.AppendChild(new Row());
foreach (string dataElement in dataList)
{
Cell cell = ConstructCell(dataElement, CellValues.String);
dataRow.Append(cell);
}
}
workbookPart.Workbook.Save();
}
});
}
catch (Exception ex)
{
await MainPage.ShowErrorMessage(ex.Message);
return;
}
}
public async void ExportToExcel(List <PageQueryEntry> datasource)
{
string[] filterFields = { "Id", "isFirst" };
try
{
var fileName = "ExportPageQuery" + DateTime.Now.ToString("yyyyMMddhhmmss");
var dsfile = await GenerateExcel(fileName + ".xlsx");
if (String.IsNullOrEmpty(dsfile))
{
return;
}
ExcelData ds = new ExcelData()
{
Headers = typeof(PageQueryEntry).GetProperties().Where(x => !filterFields.Contains(x.Name)).Select(x => x.Name).ToList(),
Values = datasource.Select(x => GetValusFromSource(x)).ToList()
};
InsertDataIntoSheet(dsfile, "sheet1", ds);
var dialog = new ContentDialog()
{
Title = "Tips!",
Content = new TextBlock()
{
Text = "download successfully！" + Environment.NewLine + "loaclPath:" + dsfile,
TextWrapping = Windows.UI.Xaml.TextWrapping.Wrap,
IsTextSelectionEnabled = true,
},
PrimaryButtonText = "OpenFile",
PrimaryButtonCommandParameter = dsfile,
SecondaryButtonText = "Cancel",
FullSizeDesired = false,
};
dialog.PrimaryButtonClick += Dialog_PrimaryButtonClick;
await dialog.ShowAsync();
}
catch (Exception ex)
{
await MainPage.ShowErrorMessage(ex.Message);
return;
}
}
public async void GenerateTempOrderExcel <T>(List <T> datasource)
{
try
{
var fileName = "Export" + typeof(T).Name + DateTime.Now.ToString("yyyyMMddhhmmss");
var dsfile = await GenerateExcel(fileName + ".xlsx");
if (String.IsNullOrEmpty(dsfile))
{
return;
}
var exportFields = typeof(T).GetProperties().Where(x => x.CustomAttributes.Select(t => t.AttributeType.Name).ToList().Contains("DisplayNameAttribute")).ToList();
ExcelData ds = new ExcelData();
if (typeof(T) == typeof(Order))
{
var CelaMaxCount = (datasource as List <Order>).Select(x => x.CelaCounts).Max();
exportFields.ForEach(item =>
{
if (item.Name == "CelaExplains")
{
int i = 1;
while (i <= CelaMaxCount)
{
ds.Headers.Add(String.Format("Cela建议{0} -指令详情", i));
ds.Headers.Add(String.Format("Cela建议{0} -处理方式", i));
i++;
}
}
else
{
var fieldName = item.CustomAttributes.Where(t => t.AttributeType.Name == "DisplayNameAttribute").Select(x => x.ConstructorArguments.FirstOrDefault().Value).FirstOrDefault();
ds.Headers.Add(fieldName.ToString() ?? item.Name);
}
});
ds.Values = (datasource as List <Order>).Select(x => GetValusFromSource(x, CelaMaxCount)).ToList();
}
else
{
exportFields.ForEach(item =>
{
var fieldName = item.CustomAttributes.Where(t => t.AttributeType.Name == "DisplayNameAttribute").Select(x => x.ConstructorArguments.FirstOrDefault().Value).FirstOrDefault();
ds.Headers.Add(fieldName.ToString() ?? item.Name);
});
ds.Values = datasource.Select(x => GetValusFromSource(x)).ToList();
}
InsertDataIntoSheet(dsfile, "sheet1", ds);
var dialog = new ContentDialog()
{
Title = "Tips!",
Content = new TextBlock()
{
Text = "download successfully！" + Environment.NewLine + "loaclPath:" + dsfile,
TextWrapping = Windows.UI.Xaml.TextWrapping.Wrap,
IsTextSelectionEnabled = true,
},
PrimaryButtonText = "OpenFile",
PrimaryButtonCommandParameter = dsfile,
SecondaryButtonText = "Cancel",
FullSizeDesired = false,
};
dialog.PrimaryButtonClick += Dialog_PrimaryButtonClick;
await dialog.ShowAsync();
}
catch (Exception ex)
{
await MainPage.ShowErrorMessage(ex.Message);
return;
}
}