using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace friction
{
	public class ExcelHelper
	{
		//The correct method to convert width to pixel is:
		//Pixel =Truncate(((256 * {width} + Truncate(128/{Maximum DigitWidth}))/256)*{Maximum Digit Width})

		//The correct method to convert pixel to width is:
		//1. use the formula =Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100 
		//    to convert pixel to character number.
		//2. use the formula width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 
		//    to convert the character number to width.

		public const int MTU_PER_PIXEL = 9525;

		public static int ColumnWidth2Pixel(ExcelWorksheet ws, double excelColumnWidth)
		{
			//The correct method to convert width to pixel is:
			//Pixel =Truncate(((256 * {width} + Truncate(128/{Maximum DigitWidth}))/256)*{Maximum Digit Width})

			//get the maximum digit width
			decimal mdw = ws.Workbook.MaxFontWidth;

			//convert width to pixel
			decimal pixels = decimal.Truncate(((256 * (decimal)excelColumnWidth + decimal.Truncate(128 / mdw)) / 256) * mdw);
			//double columnWidthInTwips = (double)(pixels * (1440f / 96f));

			return Convert.ToInt32(pixels);
		}

		public static int Column2Pixel(ExcelWorksheet ws, int iColumn)
		{
			double dX = 0;
			for (int i = 1; i < iColumn; i++)
				dX += ColumnWidth2Pixel(ws, ws.Column(i).Width);

			return (int)dX;
		}

		public static double Pixel2ColumnWidth(ExcelWorksheet ws, int pixels)
		{
			//The correct method to convert pixel to width is:
			//1. use the formula =Truncate(({pixels}-5)/{Maximum Digit Width} * 100+0.5)/100 
			//    to convert pixel to character number.
			//2. use the formula width = Truncate([{Number of Characters} * {Maximum Digit Width} + {5 pixel padding}]/{Maximum Digit Width}*256)/256 
			//    to convert the character number to width.

			//get the maximum digit width
			decimal mdw = ws.Workbook.MaxFontWidth;

			//convert pixel to character number
			decimal numChars = decimal.Truncate(decimal.Add((pixels - 5) / mdw * 100, (decimal)0.5)) / 100;
			//convert the character number to width
			decimal excelColumnWidth = decimal.Truncate((decimal.Add(numChars * mdw, 5)) / mdw * 256) / 256;

			return Convert.ToDouble(excelColumnWidth);
		}

		public static int Pixel2NextColumn(ExcelWorksheet ws, int iCol, int iPixels)
		{
			while (iPixels > 0)
				iPixels -= ColumnWidth2Pixel(ws, ws.Column(iCol++).Width);

			return iCol;
		}

		public static int RowHeight2Pixel(double excelRowHeight)
		{
			//convert height to pixel
			decimal pixels = decimal.Truncate((decimal)(excelRowHeight / 0.75));

			return Convert.ToInt32(pixels);
		}

		public static int Row2Pixel(ExcelWorksheet ws, int iRow)
		{
			int iY = 0;
			for (int i = 1; i < iRow; i++)
				iY += RowHeight2Pixel(ws.Row(i).Height);

			return iY;
		}

		public static double Pixel2RowHeight(int pixels)
		{
			//convert height to pixel
			double excelRowHeight = pixels * 0.75;

			return excelRowHeight;
		}

		public static int Pixel2NextRow(ExcelWorksheet ws, int iRow, int iPixels)
		{
			double dPixels = iPixels;
			while(dPixels > 0)
				dPixels -= RowHeight2Pixel(ws.Row(iRow++).Height);

			return iRow;
		}

		public static int MTU2Pixel(int mtus)
		{
			//convert MTU to pixel
			decimal pixels = decimal.Truncate(mtus / MTU_PER_PIXEL);

			return Convert.ToInt32(pixels);
		}

		public static int Pixel2MTU(int pixels)
		{
			//convert pixel to MTU
			int mtus = pixels * MTU_PER_PIXEL;

			return mtus;
		}
	}
}