public int convertExceltoEMF(string path) { double sWidth = Command.thisCommand.getSheetWidth(); setSheetParameters(sWidth); string sheetSize = Command.thisCommand.getSheetSize(); if (sheetSize == "24 x 36") { formatHeight = 22.25; centerLine = 12.0; imagePerSheet = 4; } else if (sheetSize == "30 x 42") { formatHeight = 28.25; centerLine = 15.0; imagePerSheet = 5; } else if (sheetSize == "36 x 48") { formatHeight = 34.25; centerLine = 18.0; imagePerSheet = 6; } else { } double cellB; double cellC; double cellD; int resolution = 150; int pWidth; var savePath = Path.GetDirectoryName(path) + @"\Master Schedule (Images)\"; if (Directory.Exists(savePath)) { removeExistingImages(savePath); } else { Directory.CreateDirectory(savePath); } var saveName = "Master Schedule"; Command.thisCommand.dialog.ConvertingWord(); //Load the document. ExcelEngine excelEngine = new ExcelEngine(); IApplication application = excelEngine.Excel; application.DefaultVersion = ExcelVersion.Excel2013; IWorkbook workbook = excelEngine.Excel.Workbooks.OpenReadOnly(path); IWorksheet sheet = workbook.Worksheets[1]; //read widths of the cells we care about, B, C, and D cellB = sheet.GetColumnWidthInPixels(2); cellC = sheet.GetColumnWidthInPixels(3); cellD = sheet.GetColumnWidthInPixels(4); double cellWidth = cellB + cellC + cellD; float cellInches = Convert.ToSingle(cellWidth) / 72.0f; //store document width in pixels @ 150 DPI pWidth = Convert.ToInt32((cellInches * (float)resolution)); double multiplier = 994.0 / pWidth; sheet.SetColumnWidthInPixels(2, Convert.ToInt32(cellB * multiplier)); sheet.SetColumnWidthInPixels(3, Convert.ToInt32(cellC * multiplier)); sheet.SetColumnWidthInPixels(4, Convert.ToInt32(cellD * multiplier)); cellB = sheet.GetColumnWidthInPixels(2); cellC = sheet.GetColumnWidthInPixels(3); cellD = sheet.GetColumnWidthInPixels(4); cellWidth = cellB + cellC + cellD; cellInches = Convert.ToSingle(cellWidth) / 72.0f; //store document width in pixels @ 150 DPI pWidth = Convert.ToInt32((cellInches * (float)resolution)); //Read the last used row in the doc int lastRow = sheet.UsedRange.LastRow; //Resize fonts in every cell for (int x = 1; x <= lastRow; x++) { string searchRow = "A" + x.ToString(); double fntSize = sheet.Range[searchRow].CellStyle.Font.Size; var fgColor = sheet.Range[searchRow].CellStyle.PatternColorIndex; var bgColor = sheet.Range[searchRow].CellStyle.ColorIndex; //ExcelKnownColors checkColor = (ExcelKnownColors)65; fixCellSize(sheet, x); /*if (fntSize != 18) * { * if (bgColor == checkColor) * { * string searchRowB = "B" + x.ToString(); * double fontSize = sheet.Range[searchRowB].CellStyle.Font.Size; * if (fontSize != 10) * { * fixCellSize(sheet, x); * } * } * }*/ } string fullRange = "B1:D" + lastRow.ToString(); string lastRange = "D1:D" + lastRow.ToString(); // do some setup on the sheet sheet.IsGridLinesVisible = false; sheet.Range[lastRange].WrapText = true; //sheet.Range[lastRange].AutofitRows(); //sheet.Range[fullRange].AutofitRows(); //sheet.Range[lastRange].AutofitRows(); //delete hidden cells for (int rows = 1; rows <= lastRow; rows++) { bool row = sheet.IsRowVisible(rows); if (row == false) { sheet.DeleteRow(rows); rows--; } } lastRow = sheet.UsedRange.LastRow; //setup to figure out pages to publish double runningHeight = 0.0; ArrayList page = new ArrayList(); ArrayList startCell = new ArrayList(); ArrayList endCell = new ArrayList(); int sPage = 1; int sCell = 1; //seperate rows into pages for (int rows = 1; rows <= lastRow; rows++) { string content = sheet.Range["B1"].Text; double tempRow1 = sheet.GetRowHeight(1); double tempRow = sheet.GetRowHeightInPixels(rows) / 72.0; runningHeight = runningHeight + tempRow; if (runningHeight > formatHeight) { page.Add(sPage); startCell.Add(sCell); endCell.Add(rows - 1); sPage++; sCell = rows; runningHeight = tempRow; } if (rows == lastRow) { page.Add(sPage); startCell.Add(sCell); endCell.Add(rows); } } Command.thisCommand.dialog.setPageCount(page.Count); Command.thisCommand.dialog.SetupProgress(page.Count, "Task: Converting Excel Document to Images"); //save document by row ranges as images for (int i = 0; i <= page.Count - 1; i++) { int pageValue = i + 1; runningHeight = 0.0; int startingCell = int.Parse(startCell[i].ToString()); int endingCell = int.Parse(endCell[i].ToString()); for (int x = startingCell; x < endingCell; x++) { double tempRow = sheet.GetRowHeightInPixels(x) / 72.0; runningHeight = runningHeight + tempRow; } float pageHeight = Convert.ToSingle(runningHeight); pageHeight = pageHeight * 150; Image image = sheet.ConvertToImage(startingCell, 2, endingCell, 4, ImageType.Metafile, null); Bitmap bitmap = null; bitmap = new Bitmap(pWidth, Convert.ToInt32(pageHeight)); bitmap.SetResolution((float)resolution, (float)resolution); using (Graphics g = Graphics.FromImage(bitmap)) { g.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High; g.CompositingQuality = System.Drawing.Drawing2D.CompositingQuality.HighQuality; g.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.AntiAlias; g.TextRenderingHint = System.Drawing.Text.TextRenderingHint.AntiAlias; g.DrawImage(image, 0, 0, pWidth, Convert.ToInt32(pageHeight)); g.Dispose(); } bitmap.Save(savePath + saveName + pageValue.ToString("D2") + ".png", ImageFormat.Png); bitmap.Dispose(); //setup progress for export //int percentage = (int)Math.Round(((double)i / (double)page.Count) * 100.0); //if (OnProgressUpdate != null) //{ // OnProgressUpdate(Convert.ToInt32(percentage)); //} //i++; Command.thisCommand.dialog.IncrementProgress(); bitmap = null; } //close document without saving workbook.Close(); excelEngine.ThrowNotSavedOnDestroy = false; excelEngine.Dispose(); return(page.Count); }
private void fixCellSize(IWorksheet sheet, int x) { double initialRowHeight = sheet.GetRowHeight(x); string searchRowB = "B" + x.ToString(); string searchRowC = "C" + x.ToString(); string searchRowD = "D" + x.ToString(); double tempRowHeight = sheet.GetRowHeight(x); //double foundFontSize = sheet.Range[searchRowB].CellStyle.Font.Size; //double newFontSize = (foundFontSize * multiplier); //double newFontSize = (foundFontSize - 1.75); //sheet.Range[searchRow].CellStyle.Font.Size = newFontSize; //sheet.Range[searchRowB].WrapText = true; sheet.Range[searchRowC].WrapText = true; sheet.Range[searchRowD].WrapText = true; sheet.Range[searchRowC].AutofitRows(); double RowHeightC = sheet.GetRowHeight(x); //sheet.AutofitRow(x); sheet.Range[searchRowD].AutofitRows(); double RowHeightD = sheet.GetRowHeight(x); if (RowHeightC > RowHeightD) { sheet.Range[searchRowC].AutofitRows(); } //sheet.Range[searchRow].AutofitRows(); //double tempRowHeight = sheet.GetRowHeight(x); /*if (tempRowHeight > initialRowHeight) * { * initialRowHeight = tempRowHeight; * } * * searchRow = "C" + x.ToString(); * foundFontSize = sheet.Range[searchRow].CellStyle.Font.Size; * //double newFontSize = (foundFontSize * multiplier); * //double newFontSize = (foundFontSize - 1.75); * //sheet.Range[searchRow].CellStyle.Font.Size = newFontSize; * sheet.Range[searchRow].WrapText = true; * sheet.Range[searchRow].AutofitRows(); * * if (tempRowHeight > initialRowHeight) * { * initialRowHeight = tempRowHeight; * } * * searchRow = "D" + x.ToString(); * foundFontSize = sheet.Range[searchRow].CellStyle.Font.Size; * //double newFontSize = (foundFontSize * multiplier); * //double newFontSize = (foundFontSize - 1.75); * //sheet.Range[searchRow].CellStyle.Font.Size = newFontSize; * sheet.Range[searchRow].WrapText = true; * sheet.Range[searchRow].AutofitRows(); * * if (tempRowHeight > initialRowHeight) * { * initialRowHeight = tempRowHeight; * } * * sheet.Range[searchRow].RowHeight = initialRowHeight;*/ }