using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Xml;
using System.Xml.Linq;
using ActValue.OpenXml.OpenXmlParts;
using DocumentFormat.OpenXml.Packaging;

namespace ActValue.OpenXml.Excel
{
	/// <summary>
	/// It fills data into cells.
	/// </summary>
	public class ExcelFiller : IDisposable
	{
		internal static XNamespace ns_s = XNamespace.Get("http://schemas.openxmlformats.org/spreadsheetml/2006/main");
		internal static XNamespace ns_r = XNamespace.Get("http://schemas.openxmlformats.org/officeDocument/2006/relationships");
		internal static XNamespace ns_c = XNamespace.Get("http://schemas.openxmlformats.org/drawingml/2006/chart");

		/// <summary>
		/// Create an empty document ready to be written to disk.
		/// </summary>
		private SpreadsheetDocument myWorkbook;

		/// <summary>
		/// 
		/// </summary>
		/// <param name="originalDocumentPath"></param>
		/// <param name="destinationDocumentPath"></param>
		public ExcelFiller(String originalDocumentPath, String destinationDocumentPath)
		{
			//Make a copy of the template file
			File.Copy(originalDocumentPath, destinationDocumentPath, true);

			//Open up the copied template workbook
			myWorkbook = SpreadsheetDocument.Open(destinationDocumentPath, true);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="sheetName"></param>
		/// <returns></returns>
		public ExcelFillerSheet FillASheet(String sheetName)
		{
			return new ExcelFillerSheet(myWorkbook, sheetName, this);
		}



		#region IDisposable Members

		public void Dispose()
		{
			myWorkbook.Close();
			myWorkbook.Dispose();
		}

		#endregion
	}

	/// <summary>
	/// Inner classes used for filling data.
	/// </summary>
	public class ExcelFillerSheet
	{
		private XElement XmlData { get; set; }
		private XElement XmlWorkBook { get; set; }

		private SpreadsheetDocument document { get; set; }
		private ExcelFiller Filler { get; set; }
		private String SheetName { get; set; }

		private WorksheetPart WorksheetPart { get; set; }


		/// <summary>
		/// constructor, grab a reference and fills data
		/// </summary>
		/// <param name="document"></param>
		/// <param name="sheetName"></param>
		/// <param name="filler"></param>
		public ExcelFillerSheet(SpreadsheetDocument document, String sheetName, ExcelFiller filler)
		{
			SheetName = sheetName;
			this.document = document;
			Filler = filler;

			WorkbookPart workbookPart = document.WorkbookPart;
			using (XmlReader xmlr = XmlReader.Create(workbookPart.GetStream()))
				XmlWorkBook = XElement.Load(xmlr);

			WorksheetPart = GetWorksheetByName(sheetName);
			using (XmlReader xmlr = XmlReader.Create(this.WorksheetPart.GetStream()))
				XmlData = XElement.Load(xmlr);

			values = new List<List<Object>>();
			InnerCloseRow();
		}

		private WorksheetPart GetWorksheetByName(String name)
		{
			XElement relNode = (from XElement e in XmlWorkBook.Descendants(ExcelFiller.ns_s + "sheet")
									  where e.Attribute("name").Value == name
									  select e).Single();

			WorkbookPart workbookPart = document.WorkbookPart;
			WorksheetPart retvalue = (WorksheetPart)workbookPart.GetPartById(
				relNode.Attribute(ExcelFiller.ns_r + "id").Value);
			return retvalue;
		}

		private void InnerCloseRow()
		{
			currentList = new List<Object>();
			values.Add(currentList);
		}

		private List<List<Object>> values;
		private List<Object> currentList;

		public ExcelFillerSheet AddData(Object value)
		{
			currentList.Add(value);
			return this;
		}

		public ExcelFillerSheet CloseRow()
		{
			InnerCloseRow();
			return this;
		}

		public ExcelFillerSheet AdjustGraphData(Int32 numRows)
		{
			ChartPart part = WorksheetPart.DrawingsPart.ChartParts.First();
			XElement XmlChart;
			using (XmlReader xmlr = XmlReader.Create(part.GetStream()))
				XmlChart = XElement.Load(xmlr);
			//now we can manipulate the charts value, find the category
			var catrange = from cat in XmlChart.Descendants(ExcelFiller.ns_c + "cat")
								from f in cat.Descendants(ExcelFiller.ns_c + "f")
								select f;
			//ora ho i nodi
			foreach (var element in catrange)
			{
				String basevalue = element.Value.Split('$')[0];
				element.Value = basevalue + "$A$2:$A$" + (numRows + 1);
			}

			var valrange = from cat in XmlChart.Descendants(ExcelFiller.ns_c + "val")
								from f in cat.Descendants(ExcelFiller.ns_c + "f")
								select f;
			//ora ho i nodi
			Char startChar = 'B';
			foreach (var element in valrange)
			{
				String basevalue = element.Value.Split('$')[0];
				element.Value = basevalue + "$" + startChar + "$2:$" + startChar + "$" + (numRows + 1);
				startChar++;
			}

			using (Stream s = part.GetStream(FileMode.Create, FileAccess.Write))
			{
				using (XmlWriter xmlw = XmlWriter.Create(s))
				{
					XmlChart.WriteTo(xmlw);
				}
			}

			return this;
		}

		/// <summary>
		/// Save all data into the file.
		/// </summary>
		/// <param name="numOfRowsToSkip"></param>
		public ExcelFiller SaveData(Int32 numOfRowsToSkip)
		{
			XElement sheetData = XmlData.Descendants(ExcelFiller.ns_s + "sheetData").Single();
			//now we have a series of row, first of all build the new data
			List<XElement> elements = new List<XElement>();
			elements.AddRange(sheetData.Elements().Take(numOfRowsToSkip));
			Int32 currentRowNum = numOfRowsToSkip + 1;
			Char currentColumn;
			foreach (List<Object> lo in values)
			{
				//this is a line
				currentColumn = 'A';
				XElement row = new XElement(ExcelFiller.ns_s + "row",
				 new XAttribute("r", currentRowNum),
				 new XAttribute("spans", "1:" + lo.Count));
				foreach (var obj in lo)
				{
					row.Add(new XElement(ExcelFiller.ns_s + "c",
												new XAttribute("r", currentColumn + currentRowNum.ToString()),
												new XElement(ExcelFiller.ns_s + "v", new XText(obj.ToString()))));
					currentColumn++;
				}
				elements.Add(row);
				currentRowNum++;
			}
			//Now we have all elements
			sheetData.Elements().Remove();
			sheetData.Add(elements);
			using (Stream s = GetWorksheetByName(SheetName).GetStream(FileMode.Create, FileAccess.Write))
			{
				using (XmlWriter xmlw = XmlWriter.Create(s))
				{
					XmlData.WriteTo(xmlw);
				}
			}
			return Filler;
		}
		//XElement sheetData = XmlData.Descendants(ExcelFiller.ns_s + "sheetData").Single();
		//   //now we have a series of row, first of all build the new data
		//   List<XElement> elements = new List<XElement>();
		//   elements.AddRange(sheetData.Elements().Take(numOfRowsToSkip));
		//   Int32 currentRowNum = numOfRowsToSkip + 1;
		//   Char currentColumn;
		//   foreach (List<Object> lo in values)
		//   {
		//      //this is a line
		//      currentColumn = 'A';
		//      XElement row = new XElement(ExcelFiller.ns_s + "row",
		//       new XAttribute("r", currentRowNum),
		//       new XAttribute("spans", "1:" + lo.Count));
		//      foreach (var obj in lo)
		//      {
		//         row.Add(new XElement(ExcelFiller.ns_s + "c",
		//                              new XAttribute("r", currentColumn + currentRowNum),
		//                              new XElement(ExcelFiller.ns_s + "v", new XText(obj.ToString()))));
		//         currentColumn++;
		//      }
		//      elements.Add(row);
		//      currentRowNum++;
		//   }
		//   //Now we have all elements
		//   XElement newSheetData;
		//   XElement root = new XElement(ExcelFiller.ns_s + "Root",
		//       new XAttribute("xmlns", ExcelFiller.ns_s),
		//       new XAttribute(XNamespace.Xmlns + "r", ExcelFiller.ns_r),
		//      newSheetData = new XElement(ExcelFiller.ns_s + "sheetData"));
		//   newSheetData.Add(elements);
		//   sheetData.AddAfterSelf(newSheetData);
		//   sheetData.Remove();
		//   List<XAttribute> res = sheetData.Attributes().ToList();
			
		//   using (Stream s = GetWorksheetByName(SheetName).GetStream(FileMode.Create, FileAccess.Write))
		//   {
		//      using (XmlWriter xmlw = XmlWriter.Create(s))
		//      {
		//         XmlData.WriteTo(xmlw);
		//      }
		//   }
		//   return Filler;

	}
}