public void ExportExcel()
{
//导出
var path = Request.Params["path"];
var fileName = "Excel导出Demo";
var files = Directory.GetFiles(path);
ExcelDocument <FileInfo> fileinfos = new ExcelDocument <FileInfo>("目录文件列表");
foreach (var file in files)
{
fileinfos.WriteRow(new FileInfo(file));
}
Response.ClearHeaders();
Response.Clear();
Response.AppendHeader("content-disposition", "attachment; filename=" + (fileName + ".xls"));
Response.ContentType = "application/vnd.ms-excel";
Response.ContentEncoding = Encoding.UTF8;
Response.Write("<html>\r\n");
Response.Write("<head>\r\n");
Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>\r\n");
Response.Write("</head>\r\n");
Response.Write("<body>\r\n");
Response.Write(fileinfos.ToHtml());
Response.Write("</body>\r\n");
Response.Write("</html>\r\n");
Response.End();
}
/// <summary>
/// Input array: a1 b1 c1 Output: [] [] c1
/// 1 2 3 [] [] 3
/// true 1-1-2012 true true TRUE January 1, 2012 a1 b1
/// [] [] 1 2
/// </summary>
static void TestExcelCut()
{
ExcelDocument document = new ExcelDocument();
string[][] array = { new string[] { "a1", "b1", "c1" }, new string[] { "1", "2", "3" }, new string[] { "true", "1-1-2012", "true", "true" } };
document.AddArray(array, "Sheet1");
List <List <string> > vs = document.GetRange("Sheet1", "A1", "D4");
foreach (List <string> strs in vs)
{
foreach (string s in strs)
{
Console.Write($"{s}, ");
}
Console.WriteLine();
}
Console.WriteLine();
List <List <string> > a = document.CutRange("Sheet1", "A1", "B2");
document.PasteRange("Sheet1", "C3", a);
vs = document.GetRange("Sheet1", "A1", "D4");
foreach (List <string> strs in vs)
{
foreach (string s in strs)
{
Console.Write($"{s}, ");
}
Console.WriteLine();
}
}
public override void Export()
{
OverdueChargesReport report = ViewState["OverdueChargesReport"] as OverdueChargesReport;
if (report != null)
{
var document = new ExcelDocument();
document.WriteTitle(report.Title);
if (this.CurrentScope() == Scope.AllClientsScope)
{
reportGridView.Columns[0].Visible = false; //remove the client number from report
reportGridView.Columns[2].Visible = false; //remove the customer number from report
}
else if (this.CurrentScope() == Scope.ClientScope)
{
reportGridView.Columns[0].Visible = false; //remove the customer number from report
}
reportGridView.WriteToExcelDocument(document);
document.MoveToNextRow();
document.MoveToNextRow();
document.AddCell("Date Viewed");
document.AddCell(report.DateViewed.ToDateTimeString());
WriteToResponse(document.WriteToStream(), report.ExportFileName);
}
}
// STATUS: this works
/// <summary> OPTION 1 --> Set the width of a given column </summary>
/// <remarks> In option 1, you provide a letter; this is more intuitive than providing a number since Excel column headers are letters. A switch is used to convert the letter you provide to the right number so that the mapper understands it </remarks>
/// <param name="document"> Excel document that the column is in </param>
/// <param name="columnLetter"> The header letter (e.g, "A" or "AA" or "Z")</param>
/// <param name="columnWidth"> The width that you want the column to be </param>
private void SetColumnWidth(ExcelDocument document, string columnLetter, int columnWidth)
{
// document.ColumnWidth(0, 120);
int columnNumber = ColumnHeaderLetterToNumber(columnLetter);
document.ColumnWidth(columnNumber, columnWidth);
}
static void Main(string[] args)
{
ExcelDocument document = new ExcelDocument();
document.UserName = "******";
document.CodePage = CultureInfo.CurrentCulture.TextInfo.ANSICodePage;
document.ColumnWidth(0, 120);
document.ColumnWidth(1, 80);
document[0, 0].Value = "ExcelWriter Demo";
document[0, 0].Font = new Font("Tahoma", 10, FontStyle.Bold);
document[0, 0].ForeColor = ExcelColor.DarkRed;
document[0, 0].Alignment = Alignment.Centered;
document[0, 0].BackColor = ExcelColor.Silver;
document.WriteCell(1, 0, "int");
document.WriteCell(1, 1, 10);
document.Cell(2, 0).Value = "double";
document.Cell(2, 1).Value = 1.5;
document.Cell(3, 0).Value = "date";
document.Cell(3, 1).Value = DateTime.Now;
document.Cell(3, 1).Format = @"dd/mm/yyyy";
FileStream stream = new FileStream("demo.xls", FileMode.Create);
document.Save(stream);
stream.Close();
}
public static string GroupDiagramSave(ExcelDocument document, string dirPath, SeriesChartType type)
{
dirPath += "\\" + document.DocumentName;
foreach (var profileItem in document.ProfilesListContent)
{
string dirName = dirPath + "\\" + profileItem.Name;
Directory.CreateDirectory(dirName);
int chartNum = 1;
foreach (var questionItem in profileItem.Questions)
{
var questionInfo = GetQuestionInfo(questionItem, profileItem, document);
Chart currentChart = new Chart();
currentChart.Width = 1920;
currentChart.Height = 1080;
ChartArea chartArea = new ChartArea();
chartArea.Name = "ChartArea1";
currentChart.ChartAreas.Add(chartArea);
currentChart = CreateDefaultChart(currentChart, questionInfo, questionItem, type);
currentChart = SettingDefaultChart(currentChart, currentChart.Series.ToList());
currentChart.SaveImage(dirName + "\\Chart" + chartNum + ".png", ChartImageFormat.Png);
chartNum++;
}
}
return(dirPath);
}
public Dictionary <string, string> Post()
{
//HttpResponseMessage result = null;
var httpRequest = HttpContext.Current.Request;
HttpPostedFile file1 = httpRequest.Files[0];
var arr = new string[5];
for (int i = 0; i < arr.Length; i++)
{
arr[i] = httpRequest.Form["arr" + i];
}
//To save file, use SaveAs method
var filePath = HttpContext.Current.Server.MapPath("~/uploadedFiles\\" + file1.FileName);
file1.SaveAs(filePath); //File will be saved in application root
try
{
ExcelDocument e = new ExcelDocument();
return(e.ReadWorkbook(arr, filePath));
// return e.getExcelFile(arr, filePath);
}
catch (Exception ex)
{
throw new Exception(ex.Message + ", file path: " + filePath);
}
}
public CellInfo(ExcelDocument document)
{
BackColor = ExcelColor.Automatic;
ForeColor = ExcelColor.Automatic;
Font = document.DefaultFont;
this.Document = document;
}
public override void Export()
{
var report = ViewState["PromptReport"] as PromptReport;
if (report != null)
{
ExcelDocument document = new ExcelDocument();
document.WriteTitle(report.Title);
Hashtable hashtable = new Hashtable();
hashtable.Add("Client", "ClientName");
hashtable.Add("Customer", "CustomerName");
ReportGridView.WriteToExcelDocumentWithReplaceField(document, hashtable);
document.InsertEmptyRow();
document.AddCell("Funded Balance");
//document.AddCurrencyCell(report.FundedBalanceTotal, ReportGridView.Columns["Amount"].Index);
document.MoveToNextRow();
document.AddCell("Non Funded Balance");
//document.AddCurrencyCell(report.NonFundedBalancedTotal, ReportGridView.Columns["Amount"].Index);
document.InsertEmptyRow();
document.AddCell("Date Viewed");
document.AddCell(report.DateViewed.ToDateTimeString());
WriteToResponse(document.WriteToStream(), report.ExportFileName);
}
}
/// <summary>
/// Save MRU item list with values
/// </summary>
private void ExcelImportWizard_FormClosing(object sender, FormClosingEventArgs e)
{
if (_mus.MRUItems == null)
{
_mus.MRUItems = new List <string>();
}
else
{
_mus.MRUItems.Clear();
}
foreach (var item in FileSelectEdit.Properties.Items)
{
_mus.MRUItems.Add(item.ToString());
}
_mus.Save();
if (ObjectSpace.Session.InTransaction)
{
ObjectSpace.Session.RollbackTransaction();
}
if (ExcelDocument != null)
{
ExcelDocument.Close();
}
}
public ActionResult PriviewExcel()
{
//导入
if (Request.Files.Count == 0)
{
return(Redirect("/"));
}
var file = Request.Files[0];
var fileName = Guid.NewGuid().ToString() + Path.GetExtension(file.FileName);
var realPath = Server.MapPath(fileName);
if (!Directory.Exists(Path.GetDirectoryName(realPath)))
{
Directory.CreateDirectory(Path.GetDirectoryName(realPath));
}
file.SaveAs(realPath);
ExcelDocument <Models.FileInfo> fileinfos = ExcelDocument <Models.FileInfo> .LoadFromFile <Models.FileInfo>(realPath);
ViewData.Add("infos", fileinfos);
if (System.IO.File.Exists(realPath))
{
System.IO.File.Delete(realPath);
}
return(View());
}
public void TestWrite()
{
string file = Path.GetTempFileName() + ".xls";
using (ExcelDocument doc = ExcelDocument.CreateDocument())
{
ExcelWorksheet worksheet = doc.CreateWorksheet("Test");
ExcelCell cell = worksheet.GetCellAt(new ExcelCellName(new ColumnLetter(Letter.A), 1));
cell.Text = "Hello World";
ExcelFormatter formatter = worksheet.GetFormatterAt(
new ExcelCellName(new ColumnLetter(Letter.A), 1),
new ExcelCellName(new ColumnLetter(Letter.C), 3));
formatter.Background = Color.Red;
formatter.Foreground = Color.Yellow;
formatter.BorderColor = Color.Blue;
formatter.Font = new Font("Courier New", 12, FontStyle.Underline | FontStyle.Bold);
formatter.Merge();
formatter.ColumnWidth = 100;
formatter.RowHeight = 100;
Assert.AreEqual(Color.FromArgb(0, 255, 0, 0), formatter.Background);
Assert.AreEqual(Color.FromArgb(0, 255, 255, 0), formatter.Foreground);
//Assert.AreEqual(Color.FromArgb(0, 0, 0, 255), formatter.BorderColor); TODO
doc.Save(file);
}
}
/// <summary>
/// Exporta el listado de Productos a un excel
/// </summary>
public void ExportPresupuestoExcel(List <Producto> list)
{
try
{
DataTable dt = Methods.ConvertToDataTable(list);
dt.Columns.Remove("id");
dt.Columns.Remove("fk_id_categoria");
dt.Columns.Remove("categoria");
dt.Columns.Add("subtotal");
double tot = 0;
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["subtotal"] = Convert.ToDouble(dt.Rows[i]["cantidad"]) * Convert.ToDouble(dt.Rows[i]["precio"]);
tot += Convert.ToDouble(dt.Rows[i]["cantidad"]) * Convert.ToDouble(dt.Rows[i]["precio"]);
}
dt.Rows.Add("", "", "", "", "", "", "", "", "Total: $", tot.ToString());
DocumentAbstract excelDocument = new ExcelDocument();
excelDocument.CreateFileTemplate(dt, ConfigurationManager.AppSettings["FolderExcel"], ConfigurationManager.AppSettings["FileExcelPresupuesto"], new Dictionary <string, string>());
}
catch (Exception ex)
{
throw ex;
}
}
public override void Export()
{
var report = ViewState["AccountTransactionReport"] as AccountTransactionReport;
if (report != null)
{
var document = new ExcelDocument();
document.WriteTitle(report.Title);
Hashtable hashtable = new Hashtable();
hashtable.Add("Client", "ClientName");
ReportGridView.WriteToExcelDocumentWithReplaceField(document, hashtable);
document.InsertEmptyRow();
document.AddCell("Total Debits");
document.AddCurrencyCell(report.TotalCredit, 3); //ReportGridView.Columns["Debit"]
document.MoveToNextRow();
document.AddCell("Total Credits");
document.AddCurrencyCell(report.TotalCredit, 4); //ReportGridView.Columns["Credit"].Index)
document.MoveToNextRow();
document.AddCell("Movement");
document.AddCurrencyCell(report.TotalCredit, 5); //ReportGridView.Columns["Credit"].Index
document.InsertEmptyRow();
document.AddCell("Date Viewed");
document.AddCell(DateTime.Now.ToShortDateString());
WriteToResponse(document.WriteToStream(), report.ExportFileName);
}
}
private void createExcel(Stream resp)
{
wb = ExcelDocument.CreateWorkbook(resp);
sheet = wb.Workbook.Worksheets.Add("First Sheet");
wb.EnsureStylesDefined();
}
protected void Page_Load(object sender, EventArgs e)
{
string filename = "";
if (Request.QueryString["filename"] != null)
{
filename = Request.QueryString["filename"].ToString();
if (filename.EndsWith("xlsx", StringComparison.InvariantCultureIgnoreCase))
{
SheetList.Visible = true;
dataGrid.Visible = true;
Exceldocument = new ExcelDocument(filename);
SheetList.DataSource = Exceldocument.WorkSheets;
SheetList.DataBind();
}
else if (filename.EndsWith("pdf", StringComparison.InvariantCultureIgnoreCase))
{
pdfview.Style["display"] = "block";
pdfview.Attributes["src"] = "sender.ashx?filename=" + filename;
}
else if (filename.EndsWith("jpg", StringComparison.InvariantCultureIgnoreCase))
{
image.Style["display"] = "block";
image.Style["background-image"] = "url(\"sender.ashx?filename=" + filename + "\")";
}
}
else
{
Response.Redirect("mobile.aspx");
}
}
public IActionResult TranslateExcelFile([FromForm] ExcelDocument excelDocument)
{
try
{
List <DataColumns> columnLists = new List <DataColumns>();
if (excelDocument.File != null && excelDocument.File.FirstOrDefault().Length > 0)
{
MemoryStream ms = new MemoryStream();
excelDocument.File.FirstOrDefault().CopyTo(ms);
excelDocument.Table = excelDocumentService.ExcelToDataTableUsingExcelDataReader(ms);
foreach (DataColumn item in excelDocument.Table.Columns)
{
columnLists.Add(new DataColumns()
{
ColumnName = item.ToString(), ColumnType = item.DataType.Name.ToString()
});
}
}
//return columnLists;
if (columnLists.Count == 0)
{
return(NotFound());
}
return(Ok(columnLists));
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Output : A7, A1, D4, A4
/// </summary>
static void TestExcelScan()
{
Console.WriteLine(ExcelDocument.Scan(ExcelDocument.Direction.down, "A4", 3)); //A7
Console.WriteLine(ExcelDocument.Scan(ExcelDocument.Direction.up, "A4", 5)); //A1
Console.WriteLine(ExcelDocument.Scan(ExcelDocument.Direction.right, "A4", 3)); //D4
Console.WriteLine(ExcelDocument.Scan(ExcelDocument.Direction.left, "A4", 3)); //A4
}
public List <string> Extract(ExcelDocument document, string lessonNumber)
{
var cells = document.Tables[0].Cells;
var movementNameColumnIndex = 0;
var lessonColumnIndex = 0;
for (var index = 0; index < cells.GetLength(1); index++)
{
if (cells[0, index].Value == lessonNumber)
{
lessonColumnIndex = index;
break;
}
}
if (lessonColumnIndex == 0)
{
return(new List <string>());
}
var movementNames = new List <string>(16);
for (var rowIndex = 3; rowIndex < cells.GetLength(0); rowIndex++)
{
if (!string.IsNullOrWhiteSpace(cells[rowIndex, lessonColumnIndex].Value))
{
movementNames.Add(cells[rowIndex, movementNameColumnIndex].Value);
}
}
return(movementNames);
}
public override void Export()
{
var report = ViewState["TransactionReport"] as TransactionReport;
if (report != null)
{
ExcelDocument document = new ExcelDocument();
document.WriteTitle(report.Title);
ReportGridView.Columns[0].Visible = false; //do not display in report
ReportGridView.WriteToExcelDocument(document);
document.InsertEmptyRow();
document.MoveToNextRow();
document.AddCell("Funded Total");
document.AddCurrencyCell(report.FundedTotal);
document.MoveToNextRow();
document.AddCell("Non Funded Total");
document.AddCurrencyCell(report.NonFundedTotal);
document.InsertEmptyRow();
document.AddCell("Date Viewed");
document.AddCell(report.DateViewed.ToDateTimeString());
WriteToResponse(document.WriteToStream(), report.ExportFileName);
}
}
public void CleanupComObjects()
{
if (_application != null || _workbook != null)
{
ExcelClose(_workbook, _application);
}
_xlDoc = null;
}
private void closeProfileBtn_Click(object sender, EventArgs e)//+
{
mainTab.TabPages.Clear();
Document = null;
saveBtn.Enabled = false;
closeProfileBtn.Enabled = false;
visDataBtn.Enabled = false;
questionBtn.Enabled = false;
}
/// <summary>
/// Make a best-effort at closing - it'll throw if it's already been disposed, no need to cause a scene.
/// </summary>
private void CloseExcelDocumentNoThrow()
{
if (ExcelDocument != null)
{
try { ExcelDocument.Close(); }
catch { }
ExcelDocument = null;
}
}
private void ShowOnTabControl(ExcelDocument document)
{
foreach (var profileItem in document.ProfilesListContent)
{
DataGridView infoDG = new DataGridView();
infoDG.Name = profileItem.Id + profileItem.Name + "DG";
infoDG.Tag = profileItem;
infoDG.Anchor = AnchorStyles.Top;
infoDG.Anchor = AnchorStyles.Right;
infoDG.Anchor = AnchorStyles.Bottom;
infoDG.Anchor = AnchorStyles.Left;
infoDG.Dock = DockStyle.Fill;
infoDG.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
infoDG.MultiSelect = true;//!!!!!!!!!!!!!!!!!!!!!!!!!
infoDG.AllowUserToAddRows = false;
infoDG.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
infoDG.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCells;
infoDG.MouseClick += InfoDG_MouseClick;
TabPage tabPage = new TabPage();
tabPage.Text = profileItem.Name;
tabPage.Name = profileItem.Id + profileItem.Name + "Tab";
tabPage.Tag = profileItem;
tabPage.Controls.Add(infoDG);
mainTab.TabPages.Add(tabPage);
InitDataGrid(profileItem.Type, infoDG);
foreach (var questionItem in profileItem.Questions)
{
switch (profileItem.Type)
{
case "range":
{
infoDG.Rows.Add(questionItem.Id, questionItem, questionItem.LeftLimit, questionItem.RightLimit, profileItem.Answers, 0);
break;
}
case "radio":
{
infoDG.Rows.Add(questionItem.Id, questionItem, profileItem.Answers, 0);
break;
}
case "checkbox":
{
infoDG.Rows.Add(questionItem.Id, questionItem, profileItem.Answers, 0);
break;
}
default:
break;
}
}
}
}
private ExcelDocument SortDocument(ExcelDocument document)
{
document.ProfilesListContent = document.ProfilesListContent.OrderBy(x => x.Id).ToList();
foreach (var profileItem in document.ProfilesListContent)
{
profileItem.Questions = profileItem.Questions.OrderBy(x => x.Id).ToList();
}
return(document);
}
private string _getExcelColValue(string value, int rowIndex, int colIndex, ExcelDocument excelDocument)
{
string returnValue = value;
if (excelDocument.Data.Count > 0)
{
returnValue = excelDocument.Data[rowIndex][colIndex].Data;
}
return(returnValue);
}
public override void Export()
{
var report = ViewState["InvoicesTransactionReport"] as InvoicesTransactionReport;
if (report != null)
{
var document = new ExcelDocument();
document.WriteTitle(report.Title);
Hashtable hashtable = new Hashtable();
hashtable.Add("Customer", "CustomerName");
ReportGridView.WriteToExcelDocumentWithReplaceField(document, hashtable);
document.InsertEmptyRow();
document.AddCell("Funded Total");
//document.AddCurrencyCell(report.FundedTotal,
// ReportGridView.Columns["Amount"].Index);
document.MoveToNextRow();
document.AddCell("Non Funded Total");
//document.AddCurrencyCell(report.NonFundedTotal,
// ReportGridView.Columns["Amount"].Index);
document.InsertEmptyRow();
document.AddCell("Based on");
document.AddCell("Invoice Date");
document.AddCell("BOM Following");
document.AddCell("Count");
document.MoveToNextRow();
document.AddCell("Mean Debtor Days");
document.AddNumericCellToCurrentRow(report.MeanDebtorDays);
document.AddNumericCellToCurrentRow(report.MeanDebtorDaysFromBeginningOfFollowingMonth);
document.AddNumericCellToCurrentRow(report.NumberOfRecordsPaidFor);
document.MoveToNextRow();
document.AddCell("Mean Age of Unpaid ");
document.AddNumericCellToCurrentRow(report.MeanAgeOfUnpaidRecords);
document.AddNumericCellToCurrentRow(report.MeanAgeOfUnpaidRecordsFromBeginningOfFollowingMonth);
document.AddNumericCellToCurrentRow(report.NumberOfRecordsNotPaidFor);
document.InsertEmptyRow();
document.AddCell("Date Viewed");
document.AddCell(report.DateViewed.ToDateTimeString());
WriteToResponse(document.WriteToStream(), report.ExportFileName);
}
}
static void Main(string[] args)
{
var workbook = new ExcelDocument();
Console.WriteLine($"Reading translated excel file: {TranslatedExcelFile}");
var ds = workbook.easy_ReadXLSXActiveSheet_AsDataSet(TranslatedExcelFile);
var dataTable = ds.Tables[0];
Console.WriteLine("Excel file reading completed");
Console.WriteLine($"Reading resource file: {EnglishResourceFile}");
var englishResource = GetDataNodes(EnglishResourceFile, out var englishNodes);
Console.WriteLine($"Reading resource file: {JapaneseResourceFile}");
var japaneseResource = GetDataNodes(JapaneseResourceFile, out var japaneseNodes);
Console.WriteLine($"Reading resource file: {ChineseResourceFile}");
var chineseResource = GetDataNodes(ChineseResourceFile, out var chineseNodes);
Console.WriteLine($"Reading resource file: {SpanishResourceFile}");
var spanishResource = GetDataNodes(SpanishResourceFile, out var spanishNodes);
Console.WriteLine("Updating resource files");
for (var row = 1; row < dataTable.Rows.Count; row++)
{
var name = dataTable.Rows[row].ItemArray[0].ToString();
if (string.IsNullOrWhiteSpace(name))
{
break;
}
UpdateDataNode(englishNodes, name, dataTable.Rows[row].ItemArray[1].ToString());
UpdateDataNode(japaneseNodes, name, dataTable.Rows[row].ItemArray[3].ToString());
UpdateDataNode(chineseNodes, name, dataTable.Rows[row].ItemArray[4].ToString());
UpdateDataNode(spanishNodes, name, dataTable.Rows[row].ItemArray[5].ToString());
}
Console.WriteLine($"Saving resource file: {EnglishResourceFile}");
englishResource.Save(EnglishResourceFile);
Console.WriteLine($"Saving resource file: {JapaneseResourceFile}");
japaneseResource.Save(JapaneseResourceFile);
Console.WriteLine($"Saving resource file: {ChineseResourceFile}");
chineseResource.Save(ChineseResourceFile);
Console.WriteLine($"Saving resource file: {SpanishResourceFile}");
spanishResource.Save(SpanishResourceFile);
Console.WriteLine("Completed.");
Console.ReadKey();
}
public void Complete()
{
//save all parts
ExcelStyleSheet.Save();
ExcelSharedStringsTable.SharedStringTable.Save();
ExcelWorkSheetPart.Worksheet.Save();
if (ExcelDocument != null)
{
ExcelDocument.Close();
}
}
public ExcelDocument GetExcelDocument(string filename)
{
string path = getPath();
try
{
ExcelDocument ed = new ExcelDocument(path + "\\" + filename);
return ed;
}
catch (Exception ex)
{
string ssdfsdf = ex.Message;
}
return null;
}
/// <summary>
/// Opens the given sheet of the given workbook from B1 to C10
/// </summary>
static void TestExcelOpen(string path, string sheetName)
{
ExcelDocument document = ExcelDocument.Open(path);
List <List <string> > vs = document.GetRange(sheetName, "B1", "C10");
foreach (List <string> strs in vs)
{
foreach (string s in strs)
{
Console.Write($"{s}, ");
}
Console.WriteLine();
}
}
protected void ExportResult_Click(object sender, EventArgs e)
{
DateTime min = MinDate;
DateTime max = MaxDate;
List<WMUserClickResult> list = WMUserClicks.GetResults(min, max);
if (!General.IsNullable(list))
{
string fileName = string.Format("~/share/upload/excel/clicks/{0:yyyyMMdd}_{1:yyyyMMdd}.xlsx", min, max);
ExcelDocument excel = new ExcelDocument(fileName, null);
if (excel.CreateExcel(list, "clickResult"))
FileHelper.DownLoadFile(fileName, MimeType.Excel);
}
}
private static void AddDocument(string[] attributes, string cmd)
{
Document textDocument;
switch (cmd)
{
case "AddTextDocument":
textDocument = new TextDocument();
break;
case "AddPDFDocument":
textDocument = new PDFDocument();
break;
case "AddWordDocument":
textDocument = new WordDocument();
break;
case "AddExcelDocument":
textDocument = new ExcelDocument();
break;
case "AddAudioDocument":
textDocument = new AudioDocument();
break;
case "AddVideoDocument":
textDocument = new VideoDocument();
break;
default:
textDocument = new TextDocument();
break;
}
if (attributes.Length > 0)
{
IList<KeyValuePair<string, object>> attrbList = new List<KeyValuePair<string, object>>();
foreach (var attribute in attributes)
{
string[] attrPair = attribute.Split(new char[] { '=' }, StringSplitOptions.RemoveEmptyEntries);
attrbList.Add(new KeyValuePair<string, object>(attrPair[0], attrPair[1] as object));
}
textDocument.SaveAllProperties(attrbList);
allDocuments.Add(textDocument);
Console.WriteLine("Document added: {0}", textDocument.Name);
}
else
{
Console.WriteLine("Document has no name");
}
}
private static void AddExcelDocument(string[] attributes)
{
ExcelDocument doc = new ExcelDocument();
foreach (string properties in attributes)
{
string[] prop = properties.Split('=');
doc.LoadProperty(prop[0], prop[1]);
}
if (doc.Name != null)
{
allDocuments.Add(doc);
Console.WriteLine("Document added: {0}", doc.Name);
}
else
{
Console.WriteLine("Document has no name");
}
}
public void ExportDataTable(DataTable table, string exportFile)
{
//create the empty spreadsheet template and save the file //using the class generated by the Productivity tool
ExcelDocument excelDocument = new ExcelDocument();
excelDocument.CreatePackage(exportFile);
//string filename = "";
//File.Copy(filename, filename, true);
//populate the data into the spreadsheet
using (SpreadsheetDocument spreadsheet = SpreadsheetDocument.Open(exportFile, true))
{
WorkbookPart workbook = spreadsheet.WorkbookPart;
//create a reference to Sheet1
WorksheetPart worksheet = workbook.WorksheetParts.Last();
SheetData data = worksheet.Worksheet.GetFirstChild<SheetData>();
//add column names to the first row
Row header = new Row();
header.RowIndex = (UInt32)1;
foreach (DataColumn column in table.Columns)
{
Cell headerCell = createTextCell(
table.Columns.IndexOf(column) + 1,
1,
column.ColumnName);
header.AppendChild(headerCell);
}
data.AppendChild(header);
//loop through each data row
DataRow contentRow;
for (int i = 0; i < table.Rows.Count; i++)
{
contentRow = table.Rows[i];
data.AppendChild(createContentRow(contentRow, i + 2));
}
}
}
public ViewWindow(file filename)
{
InitializeComponent();
if (filename.Name.EndsWith("xlsx"))
{
SheetList.Visibility = Visibility.Visible;
dataGrid.Visibility = Visibility.Visible;
fsc.GetExcelDocumentCompleted += (a, b) =>
{
Exceldocument = b.Result;
SheetList.SetBinding(ListBox.ItemsSourceProperty, new Binding("WorkSheets") { Source = Exceldocument, BindsDirectlyToSource = true, Mode = BindingMode.TwoWay, UpdateSourceTrigger = UpdateSourceTrigger.Default });
};
fsc.GetExcelDocumentAsync(filename.Name);
}
else if (filename.Name.EndsWith("pdf"))
{
UriBuilder ub = new UriBuilder(fsc.Endpoint.Address.Uri.AbsoluteUri.Replace("FileService.svc", "sender.ashx"));
ub.Query = string.Format("filename={0}", filename.Name);
webbrowser.Visibility = Visibility.Visible;
webbrowser.NavigateToString("<html><body><iframe src='"+ ub.Uri.AbsoluteUri +"&mime=true' id='ifrm' style='width:100%;height:100%;border:solid 10px green;margin:0;padding:0;'></iframe></body></html>");
}
else if (filename.Name.ToLower().EndsWith("jpg"))
{
UriBuilder ub = new UriBuilder(fsc.Endpoint.Address.Uri.AbsoluteUri.Replace("FileService.svc", "sender.ashx"));
ub.Query = string.Format("filename={0}", filename.Name);
ImageSourceConverter isc = new ImageSourceConverter();
image.Source= isc.ConvertFromString(ub.Uri.OriginalString) as ImageSource;
image.Visibility = Visibility.Visible;
}
}
private static void AddExcelDocument(string[] attributes)
{
var doc = new ExcelDocument();
foreach (var atr in attributes)
{
string[] splittedProp = atr.Split('=');
doc.LoadProperty(splittedProp[0], splittedProp[1]);
}
if (doc.Name == null)
{
Console.WriteLine("Document has no name");
}
else
{
Console.WriteLine("Document added: {0}", doc.Name);
allDocuments.Add(doc);
}
}
private static void AddExcelDocument(string[] attributes)
{
IDocument doc = new ExcelDocument("");
AddDocument(attributes, doc);
}
private static void AddExcelDocument(string[] attributes)
{
Document doc = new ExcelDocument();
CreateDocument(attributes, doc);
}
private static void AddExcelDocument(string[] attributes)
{
ExcelDocument excelDocument = new ExcelDocument();
AddDocument(attributes, excelDocument);
}
private static void AddDocument(string[] attributes, string type)
{
string name = string.Empty;
List<KeyValuePair<string, object>> documentAttributes = new List<KeyValuePair<string, object>>();
foreach (var item in attributes)
{
string[] splitAttributes = item.Split('=');
if (splitAttributes[0] == "name")
{
name = splitAttributes[1];
}
else
{
documentAttributes.Add(new KeyValuePair<string, object>(splitAttributes[0], splitAttributes[1]));
}
}
Document currentDocument = null;
if (name != string.Empty)
{
switch (type)
{
case "txt":
currentDocument = new TextDocument(name);
break;
case "pdf":
currentDocument = new PDFDocument(name);
break;
case "doc":
currentDocument = new WordDocument(name);
break;
case "xls":
currentDocument = new ExcelDocument(name);
break;
case "audio":
currentDocument = new AudioDocument(name);
break;
case "video":
currentDocument = new VideoDocument(name);
break;
}
Console.WriteLine("Document added: {0}", name);
currentDocument.SaveAllProperties(documentAttributes);
DocumentSystem.Add(currentDocument);
}
else
{
Console.WriteLine("Document has no name");
}
}
