public void ChangeExcel2Image(string filename)
{
Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
workbook.LoadFromFile(filename);
Spire.Xls.Worksheet sheet = workbook.Worksheets[0];
sheet.SaveToImage("性能报表.jpg"); //图片后缀.bmp ,imagepath自己设置
}
private void modernButton1_Click(object sender, EventArgs e)
{
Spire.Xls.Workbook wb;
Spire.Xls.Worksheet ws = null;
wb = new Spire.Xls.Workbook();
wb.LoadFromFile("Data\\Test\\c5.xlsx");
wb.ActiveSheetIndex = 0 + 1;
ws = wb.ActiveSheet;
//var dgv=new DataGridView();
//dgv.Dock = DockStyle.Fill;
//dgv.DoubleBuffered(true);
//try { dgv.DataSource = ws.ExportDataTable(); } catch(Exception ex) { Log(Logger.GetExceptionInfo(ex));}
//Form frm = new Form();
//frm.Controls.Add(dgv);
//frm.ShowDialog();
ScoreTable st = new ScoreTable(new Dictionary <string, ScoreModel>(), new InputDataIndicator());
var data = ws.ExportDataTable();
//for (int i = 0; i < data.Rows.Count; i++)
//{
// data.Rows[i][1] = Convert.ToInt32(data.Rows[i][1].ToString());
//}
GridEditWindow gew = new GridEditWindow("编辑成绩");
gew.DataGridView.DataSource = data;
gew.Show();
}
/// <summary>
/// Convert excel to html with picture files
/// </summary>
/// <param name="inputFileName">Absolute name of input file</param>
/// <param name="outputFolder">Folder for saving results</param>
public static void ConvertToHtml(string inputFileName, string outputFolder, Dictionary <string, string> parameters)
{
Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
workbook.LoadFromFile(inputFileName);
int excelMaxRows = GetIntValue(parameters, Parameter.ExcelMaxRows.Value);
int excelMaxColumns = GetIntValue(parameters, Parameter.ExcelMaxColumns.Value);
int excelMaxSheetSize = GetIntValue(parameters, Parameter.ExcelMaxSheetSize.Value);
foreach (Spire.Xls.Worksheet sheet in workbook.Worksheets)
{
int lastRow = sheet.LastRow;
int lastColumn = sheet.LastColumn;
lastRow = excelMaxRows > 0 && lastRow > excelMaxRows ? excelMaxRows : lastRow;
lastRow = excelMaxSheetSize > 0 && lastRow > excelMaxSheetSize ? excelMaxSheetSize : lastRow;
lastColumn = excelMaxColumns > 0 && lastColumn > excelMaxColumns ? excelMaxColumns : lastColumn;
lastColumn = excelMaxSheetSize > 0 && lastColumn > excelMaxSheetSize ? excelMaxSheetSize : lastColumn;
sheet.LastRow = lastRow;
sheet.LastColumn = lastColumn;
using (var stream = new MemoryStream())
{
sheet.SaveToHtml(stream);
string result = System.Text.Encoding.UTF8.GetString(stream.ToArray());
if (result.IndexOf(replaceValueExcel) >= 0)
{
result = result.Replace(replaceValueExcel, "");
}
else if (result.IndexOf(replaceValueWithoutTag) >= 0)
{
result = result.Replace(replaceValueWithoutTag, "");
}
else if (result.IndexOf(replaceValue) >= 0)
{
result = result.Replace(replaceValue, "");
}
//result = SetBorder(result, 1);
if (workbook.Worksheets.Count > 1)
{
if (result.IndexOf("<td") >= 0)
{
File.WriteAllText(outputFolder + "/" + new FileInfo(inputFileName).Name + "." + sheet.Name + ".html", result, Encoding.UTF8);
}
}
else
{
if (result.IndexOf("<td") >= 0)
{
File.WriteAllText(outputFolder + "/" + new FileInfo(inputFileName).Name + ".html", result, Encoding.UTF8);
}
}
result = null;
sheet.Dispose();
}
}
workbook.Dispose();
}
public Form1()
{
InitializeComponent();
var excel = new Spire.Xls.Workbook();
excel.LoadFromFile("../../test.xlsx");
Stream stream = new MemoryStream();
excel.SaveToStream(stream);
Excel_PDF.ExcelToPDF excelToPDF = new Excel_PDF.ExcelToPDF(stream);
}
/// <summary>
/// 添加材料
/// </summary>
/// <param name="input"></param>
/// <returns></returns>between types 'System.String' and 'System.Nullable`1[System.Guid]'.”
public IListResult <MaterialDto> AddMaterial(string input)
{
try
{
// var material = new Material
// {
// Name = input.Name,
// Model = input.Model,
// Strength = input.Strength,
// MaterialType = input.MaterialType,
// Date = input.Date,
// TypicalPartId = input.TypicalPartId,
// ManufactoryId = input.ManufactoryId,
// FileString = input.FileString
//};
//创建Workbook对象
Workbook wb = new Workbook();
//加载Excel文档//@"C:\Users\Administrator\Desktop\新建 XLSX 工作表.xlsx"
wb.LoadFromFile(input);
//获取第一个工作表
Worksheet sheet = wb.Worksheets[0];
//获取指定单元格的值
string value = sheet.Range["A2"].Value2.ToString();
//自定义时间格式
DateTimeFormatInfo dtFormat = new DateTimeFormatInfo();
dtFormat.ShortDatePattern = "yyyy/MM/dd";
var material = new MaterialDto
{
Name = sheet.Range["A2"].Value2.ToString(),
//Model = sheet.Range["C2"].Value2.ToString(),
TypicalPartId = new Guid(sheet.Range["a2"].Value2.ToString()),
//Date= Convert.ToDateTime(sheet.Range["b2"].Value2.ToString(), dtFormat)//注意:string格式有要求,yyyy/MM/dd
};
//释放资源
wb.Dispose();
//await _materialRepository.InsertAsync(material);
return(null);
}
catch (Exception)
{
return(null);
}
}
public ExcelFile(string filePath)
{
ExcelSheets = new MvxObservableCollection <ExcelSheet>();
//For image
excelFileSpire = new Spire.Xls.Workbook();
excelFileSpire.LoadFromFile(filePath);
//For PDF
excelFileInterop = new Application();
excelBook = excelFileInterop.Workbooks.Open(filePath);
for (int idx = 0; idx < excelFileSpire.Worksheets.Count; idx++)
{
ExcelSheets.Add(new ExcelSheet(excelFileSpire.Worksheets[idx], excelBook.Sheets[idx + 1]));
}
}
private void button1_Click(object sender, EventArgs e)
{
try {
if (openFileDialog1.ShowDialog().Equals(CancelButton) == false)
{
textBox1.Text = openFileDialog1.FileName;
Spire.Xls.Workbook wb = new Spire.Xls.Workbook();
wb.LoadFromFile(textBox1.Text);
Spire.Xls.Worksheet ws = wb.Worksheets[0];
dataGridView1.DataSource = ws.ExportDataTable();
}
else
{
}
}catch (Exception ex)
{
MessageBox.Show("Cagaste en algo chavon");
}
}
void fixexcel(string foldername, string savename)
{
cleancache();
String dtPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) + "\\" + foldername + "\\";
DirectoryInfo dir = new DirectoryInfo(dtPath);
foreach (FileInfo flInfo in dir.GetFiles())
{
//Instantiate a Workbook object
Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
//Load the Excel file
workbook.LoadFromFile(flInfo.FullName);
//Get the first worksheet
foreach (Spire.Xls.Worksheet sheet in workbook.Worksheets)
{
//Delete blanks rows
for (int i = sheet.Rows.Count() - 1; i >= 0; i--)
{
if (sheet.Rows[i].IsBlank)
{
sheet.DeleteRow(i + 1); //Index parameter in DeleteRow method starts from 1
}
}
//Delete blank columns
for (int j = sheet.Columns.Count() - 1; j >= 0; j--)
{
if (sheet.Columns[j].IsBlank)
{
sheet.DeleteColumn(j + 1); //Index parameter in DeleteColumn method starts from 1
}
}
}
//Save the file
workbook.SaveToFile(savename + ".xlsx", Spire.Xls.ExcelVersion.Version2013);
}
}
/// <summary>
/// 文件上传
/// </summary>
public void UploadFiles()
{
bool result = false;
if (Request.Form["option"].ToString() == "update")
{
string fn = Request.Form["filename"].ToString();
string keyword = Request.Form["keyword"].ToString();
string status = Request.Form["ispublic"].ToString() == "checked" ? "1" : "0";
string id = Request.Form["id"].ToString();
string sql = string.Format("update fileinfo set filename='{0}',keyword='{1}',status='{2}' where keyid={3}", fn, keyword, status, id);
int i = DAL.Commons.Instance.chooseFactory("sql").ExecuteNonQuery(sql);
result = i > 0 ? true : false;
}
else
{
if (Request.Files.Count > 0)
{
loginUser lu = Session["user"] as loginUser;
string dir = string.Format("{0}//{1}//{2}", "upload", DateTime.Now.ToString("yyyy-MM-dd"), lu.LoginName);
string extName = Path.GetExtension(Request.Files[0].FileName);//.Split('.')[1].ToLower();
extName = extName.Substring(1);
string filename = DAL.Commons.Instance.GetUploadFileName(dir, extName);
Request.Files[0].SaveAs(AppDomain.CurrentDomain.BaseDirectory + filename);
string fn = Request.Form["filename"].ToString();
string keyword = Request.Form["keyword"].ToString();
string dirid = Request.Form["dirid"].ToString();
string userid = lu.ID;
string status = Request.Form["ispublic"].ToString() == "checked" ? "1" : "0";
string f1 = "doc,docx";
string f2 = "xls,xlsx";
string f3 = "txt,jpeg,png";
string f4 = "pdf";
int viewmode = 3;
string viewdir = "";
if (f1.IndexOf(extName) >= 0)
{
viewmode = 1;
Spire.Doc.Document doc = new Spire.Doc.Document();
doc.LoadFromFile(AppDomain.CurrentDomain.BaseDirectory + filename);
string newname = DAL.Commons.Instance.GetUploadFileName(dir, "pdf");
doc.SaveToFile(AppDomain.CurrentDomain.BaseDirectory + newname, Spire.Doc.FileFormat.PDF);
viewdir = newname;
doc.Close();
}
else if (f2.IndexOf(extName) >= 0)
{
viewmode = 1;
Spire.Xls.Workbook book = new Spire.Xls.Workbook();
book.LoadFromFile(AppDomain.CurrentDomain.BaseDirectory + filename);
string newname = DAL.Commons.Instance.GetUploadFileName(dir, "pdf");
book.ConverterSetting.SheetFitToPage = true;
book.SaveToFile(AppDomain.CurrentDomain.BaseDirectory + newname, Spire.Xls.FileFormat.PDF);
viewdir = newname;
}
else if (f3.IndexOf(extName) >= 0)
{
viewmode = 2;
viewdir = filename;
}
else if (f4.IndexOf(extName) >= 0)
{
viewmode = 1;
viewdir = filename;
}
Spire.Pdf.PdfDocument pdf = new Spire.Pdf.PdfDocument();
string sql = string.Format("insert into fileinfo (filename,keyword,dirid,userid,status,localdir,viewdir,viewmode,extname) values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')", fn, keyword, dirid, userid, status, filename, viewdir, viewmode, extName);
int i = DAL.Commons.Instance.chooseFactory("sql").ExecuteNonQuery(sql);
result = i > 0 ? true : false;
}
}
var par = new { sucess = result };
Response.Write(DAL.Commons.Instance.ToJson(par));
}
public ActionResult viewPRExcel(Int16 fiscalYear, Int16? coeID)
{
ModelState.Clear();
var viewModel = new PRViewModel
{
//allCoEs = db.CoEs.ToList(),
allCoEs = db.CoEs.ToList(),
allMaps = db.Indicator_CoE_Maps.ToList(),
allFootnoteMaps = db.Indicator_Footnote_Maps.ToList()
};
// Create the workbook
SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
var ef = new ExcelPackage();
var wb = ef.Workbook;
var prBlue = ExcelColor.FromArgb(0, 51, 102);
var prGreen = ExcelColor.FromArgb(0, 118, 53);
var prYellow = ExcelColor.FromArgb(255, 192, 0);
var prRed = ExcelColor.FromArgb(255, 0, 0);
var prHeader1Fill = prBlue;
var prHeader1Font = ExcelColor.White;
var prHeader2Fill = ExcelColor.White;
var prHeader2Font = ExcelColor.Black;
var prBorder = ExcelColor.FromArgb(0, 0, 0);
var prAreaFill = ExcelColor.FromArgb(192, 192, 192);
var prAreaFont = ExcelColor.Black;
var prBorderWidth = XLBorderStyleValues.Thin;
var prFontSize = 10;
var prTitleFont = 20;
var prFootnoteSize = 8;
var prHeightSeperator = 7.5;
var prAreaObjectiveFontsize = 8;
var indentLength = 24;
var firstIndentLength = 20;
var innerIndentLength = 5;
var newLineHeight = 12.6;
var defNote = "Portal data from the Canadian Institute for Health Information (CIHI) has been used to generate data within this report with acknowledgement to CIHI, the Ministry of Health and Long-Term Care (MOHLTC) and Stats Canada (as applicable). Views are not those of the acknowledged sources. Facility identifiable data other than Mount Sinai Hospital (MSH) is not to be published without the consent of that organization (except where reported at an aggregate level). As this is not a database supported by MSH, please demonstrate caution with use and interpretation of the information. MSH is not responsible for any changes derived from the source data/canned reports. Data may be subject to change.";
var prNumberWidth = 4;
var prIndicatorWidth = 55;
var prValueWidth = 11;
var prDefWidth = 100;
var prRatiWidth = 50;
var prCompWidth = 50;
//var fitRatio = 3.77;
var fitRatio = 1.7;
List<int> fitAdjustableRows = new List<int>();
var prFootnoteCharsNewLine = 125;
var prObjectivesCharsNewLine = 226;
//DELETE THIS
//coeID = null;
var allCoes = new List<CoEs>();
if (coeID != 0 && coeID != null)
{
allCoes = viewModel.allCoEs.Where(x => x.CoE_ID == coeID).ToList();
}
else
{
allCoes = viewModel.allCoEs.ToList();
}
foreach (var coe in allCoes)
{
var wsPRName = coe.CoE_Abbr;
var wsDefName = "Def_" + coe.CoE_Abbr;
var wsPR = wb.Worksheets.Add(wsPRName);
var wsDef = wb.Worksheets.Add(wsDefName);
List<OfficeOpenXml.ExcelWorksheet> wsList = new List<OfficeOpenXml.ExcelWorksheet>();
wsList.Add(wsPR);
wsList.Add(wsDef);
foreach (var ws in wsList)
{
var currentRow = 4;
ws.Row(2).Height = 21;
int startRow;
int indicatorNumber = 1;
ws.PrinterSettings.TopMargin = 0;
ws.PrinterSettings.HeaderMargin = 0;
ws.PrinterSettings.BottomMargin = 0.5M;
ws.PrinterSettings.LeftMargin = 0;
ws.PrinterSettings.RightMargin = 0;
ws.PrinterSettings.Orientation = eOrientation.Landscape;
ws.PrinterSettings.PaperSize = ePaperSize.Legal;
ws.PrinterSettings.FitToHeight = 1;
ws.PrinterSettings.FitToWidth = 1;
//ws.PageSetup.Margins.Top = 0;
//ws.PageSetup.Margins.Header = 0;
//ws.PageSetup.Margins.Left = 0.5;
//ws.PageSetup.Margins.Right = 0.5;
//ws.PageSetup.Margins.Bottom = 0.5;
//ws.PageSetup.PageOrientation = XLPageOrientation.Landscape;
//ws.PageSetup.PaperSize = XLPaperSize.LegalPaper;
//ws.PageSetup.FitToPages(1, 1);
string[,] columnHeaders = new string[0, 0];
if (ws.Name == wsPRName)
{
var prHeadder2Title = FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance";
prHeadder2Title = prHeadder2Title.Replace("_", " ");
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_3", fiscalYear), ""},
{FiscalYear.FYStrFull("FY_2", fiscalYear),""},
{FiscalYear.FYStrFull("FY_1", fiscalYear),""},
{prHeadder2Title,"Q1"},
{prHeadder2Title,"Q2"},
{prHeadder2Title,"Q3"},
{prHeadder2Title,"Q4"},
{prHeadder2Title,"YTD"},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Performance_Threshold",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator",""}
};
}
else if (ws.Name == wsDefName)
{
columnHeaders = new string[,]{
{"Number",""},
{"Indicator",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale",""},
{FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source",""}
};
}
var currentCol = 1;
var prHeader2ColStart = 99;
var prHeader2ColEnd = 1;
int maxCol = columnHeaders.GetUpperBound(0) + 1;
var prTitle = ws.Cells[currentRow, 1];
prTitle.Value = coe.CoE;
prTitle.Style.Font.Size = prTitleFont;
prTitle.Style.Font.Bold = true;
prTitle.Style.Font.Color.SetColor(prHeader1Font);
prTitle.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prTitle.Style.Fill.BackgroundColor.SetColor(prHeader1Fill);
ws.Cells[currentRow, 1, currentRow, maxCol].Merge = true;
ws.Cells[currentRow + 1, 1, currentRow + 1, maxCol].Merge = true;
ws.Row(currentRow + 1).Height = prHeightSeperator;
currentRow += 2;
startRow = currentRow;
for (int i = 0; i <= columnHeaders.GetUpperBound(0); i++)
{
if (columnHeaders[i, 1] == "")
{
var columnField = columnHeaders[i, 0];
string cellValue;
Type t = typeof(Indicators);
cellValue = t.GetProperty(columnField) != null ?
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicators), columnField).DisplayName :
ModelMetadataProviders.Current.GetMetadataForProperty(null, typeof(Indicator_CoE_Maps), columnField).DisplayName;
ws.Cells[currentRow, currentCol].Value = cellValue;
ws.Cells[currentRow, currentCol, currentRow + 1, currentCol].Merge = true;
currentCol++;
}
else
{
var columnField = columnHeaders[i, 1];
var columnFieldTop = columnHeaders[i, 0];
ws.Cells[currentRow + 1, currentCol].Value = columnField;
ws.Cells[currentRow, currentCol].Value = columnFieldTop;
if (currentCol < prHeader2ColStart) { prHeader2ColStart = currentCol; }
if (currentCol > prHeader2ColEnd) { prHeader2ColEnd = currentCol; }
currentCol++;
}
}
currentCol--;
var prHeader1 = ws.Cells[currentRow, 1, currentRow + 1, currentCol];
if (prHeader2ColStart != 99)
{
ws.Cells[currentRow, prHeader2ColStart, currentRow, prHeader2ColEnd].Merge = true;
var prHeader2 = ws.Cells[currentRow + 1, prHeader2ColStart, currentRow + 1, prHeader2ColEnd];
prHeader2.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prHeader2.Style.Fill.BackgroundColor.SetColor(prHeader2Fill);
prHeader2.Style.Font.Color.SetColor(prHeader2Font);
}
prHeader1.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
prHeader1.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
prHeader1.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prHeader1.Style.Fill.BackgroundColor.SetColor(prHeader1Fill);
prHeader1.Style.Font.Color.SetColor(prHeader1Font);
currentRow += 2;
List<Footnotes> footnotes = new List<Footnotes>();
foreach (var areaMap in coe.Area_CoE_Map.Where(x=>x.Fiscal_Year == fiscalYear).OrderBy(x => x.Area.Sort))
{
var cellLengthObjective = 0;
var prArea = ws.Cells[currentRow, 1, currentRow, maxCol];
//fitAdjustableRows.Add(currentRow);
prArea.Merge = true;
prArea.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
prArea.Style.Fill.BackgroundColor.SetColor(prAreaFill);
prArea.Style.Font.Color.SetColor(prAreaFont);
prArea.FirstOrDefault().RichText.Add(areaMap.Area.Area).Bold = true;
cellLengthObjective += areaMap.Area.Area.Length;
if (ws == wsPR)
{
var indent = new string('_', indentLength);
var innerIndent = new string('_', innerIndentLength);
var firstIndent = indent.Substring(0, firstIndentLength - areaMap.Area.Area.Length);
var stringSeperators = new string[] { "•" };
/*
if (areaMap.Objective != null)
{
var objectives = areaMap.Objective.Split(stringSeperators, StringSplitOptions.None);
for (var i = 1; i < objectives.Length; i++)
{
if (i == 1)
{
prArea.FirstOrDefault().RichText.Add(firstIndent).Size = prAreaObjectiveFontsize;
cellLengthObjective += firstIndent.Length;
}
//var innerIndentAdj = new string('_', maxObjectiveLength < objectives[i].Length ? 0 : maxObjectiveLength - objectives[i].Length);
var innerIndentAdj = "";
cellLengthObjective += objectives[i].Length + innerIndent.Length + innerIndentAdj.Length;
if (cellLengthObjective > prObjectivesCharsNewLine)
{
prArea.FirstOrDefault().RichText.Add("\r\n");
ws.Row(currentRow).Height += (int)newLineHeight;
prArea.FirstOrDefault().RichText.Add(indent).Color = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstOrDefault().RichText.Add(indent).SetFontColor( ClosedXML.Excel.XLColor.FromColor(prAreaFont)).SetFontSize(prAreaObjectiveFontsize);
cellLengthObjective = indent.Length;
}
prArea.FirstCell().RichText.AddText(innerIndent + innerIndentAdj).FontColor = ClosedXML.Excel.XLColor.FromColor(prAreaFill);
prArea.FirstCell().RichText.AddText(" •" + objectives[i]).FontSize = prAreaObjectiveFontsize;
cellLengthObjective += objectives[i].Length;
}
}*/
}
currentRow++;
foreach (var map in viewModel.allMaps.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator.Area.Equals(areaMap.Area)).Where(d => d.CoE.CoE.Contains(coe.CoE)).OrderBy(f => f.Number))
{
fitAdjustableRows.Add(currentRow);
currentCol = 1;
ws.Cells[currentRow, currentCol].Value = indicatorNumber;
indicatorNumber++;
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
currentCol++;
int j = 0;
ws.Cells[currentRow, currentCol].Value = map.Indicator.Indicator;
foreach (var footnote in map.Indicator.Indicator_Footnote_Map.Where(x => x.Fiscal_Year == fiscalYear).Where(e => e.Indicator_ID == map.Indicator_ID).OrderBy(e => e.Indicator_ID))
{
if (!footnotes.Contains(footnote.Footnote)) { footnotes.Add(footnote.Footnote); }
if (j != 0)
{
ws.Cells[currentRow, currentCol].RichText.Add(",").VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
}
ws.Cells[currentRow, currentCol].RichText.Add(footnote.Footnote.Footnote_Symbol).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
j++;
}
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
currentCol++;
if (ws.Name == wsPRName)
{
for (var i = 3; i <= 15; i++)
{
ws.Column(i).Width = ws.Name == wsPRName ? prValueWidth : prDefWidth;
}
var obj = map.Indicator;
var type = obj.GetType();
string[,] columnIndicators = new string[,]{
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_3",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_2",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear)).GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_1",fiscalYear) + "_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q1_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q2_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q3_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Q4_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Sup").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "YTD_Color").GetValue(obj,null),
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Target_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Performance_Threshold_Sup").GetValue(obj,null),
""
},
{(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator").GetValue(obj,null),
(string)type.GetProperty(FiscalYear.FYStrFull("FY_",fiscalYear) + "Comparator_Sup").GetValue(obj,null),
""
},
};
var startCol = currentCol;
int k = 1;
for (var i = 0; i <= columnIndicators.GetUpperBound(0); i++)
{
for (j = 0; j <= columnIndicators.GetUpperBound(1); j++)
{
if (columnIndicators[i, j] != null)
{
columnIndicators[i, j] = columnIndicators[i, j].Replace("<b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</b>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</u>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</i>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sup>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("<sub>", "");
columnIndicators[i, j] = columnIndicators[i, j].Replace("</sub>", "");
}
}
if (i != columnIndicators.GetUpperBound(0) && columnIndicators[i, 0] == "=")
{
k = 1;
while (columnIndicators[i + k, 0] == "=") { k++; }
ws.Cells[currentRow, startCol + i - 1, currentRow, startCol + i + k - 1].Merge = true;
i += k - 1;
k = 1;
}
else if (columnIndicators[i, 0] != "=")
{
var cell = ws.Cells[currentRow, currentCol + i];
string cellValue = "";
if (columnIndicators[i, 0] != null)
{
cellValue = columnIndicators[i, 0].ToString();
}
if (cellValue.Contains("$"))
{
}
cell.Value = "'" + cellValue;
if (columnIndicators[i, 1] != null)
{
cell.RichText.Add(columnIndicators[i, 1]).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
}
switch (columnIndicators[i, 2])
{
case "cssWhite":
//cell.RichText.SetFontColor(XLColor.Black);
cell.Style.Font.Color.SetColor(ExcelColor.Black);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(ExcelColor.White);
break;
case "cssGreen":
cell.Style.Font.Color.SetColor(ExcelColor.White);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prGreen);
break;
case "cssYellow":
cell.Style.Font.Color.SetColor(ExcelColor.Black);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prYellow);
break;
case "cssRed":
cell.Style.Font.Color.SetColor(ExcelColor.White);
cell.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
cell.Style.Fill.BackgroundColor.SetColor(prRed);
break;
}
cell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
}
currentRow++;
}
else if (ws.Name == wsDefName)
{
ws.Column(3).Width = prDefWidth;
ws.Column(4).Width = prRatiWidth;
ws.Column(5).Width = prCompWidth;
var obj = map.Indicator;
var type = obj.GetType();
string defn = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Definition_Calculation").GetValue(obj, null);
string rationale = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Target_Rationale").GetValue(obj, null);
string comp = (string)type.GetProperty(FiscalYear.FYStrFull("FY_", fiscalYear) + "Comparator_Source").GetValue(obj, null);
double maxLines = 1;
double lines;
var listColumnStrings = new List<string>();
listColumnStrings.Add(defn);
listColumnStrings.Add(rationale);
listColumnStrings.Add(comp);
foreach (var str in listColumnStrings)
{
if (str != null)
{
lines = str.Length / ws.Column(currentCol).Width;
maxLines = maxLines < lines ? lines : maxLines;
ws.Cells[currentRow, currentCol].Value = str;
}
ws.Cells[currentRow, currentCol].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
currentCol++;
}
ws.Row(currentRow).Height = newLineHeight*Math.Ceiling(maxLines);
currentRow++;
}
}
}
var footnoteRow = ws.Cells[currentRow, 1, currentRow, maxCol];
footnoteRow.Merge = true;
footnoteRow.Style.Font.Size = prFootnoteSize;
/*Footnotes defaultFootnote = db.Footnotes.FirstOrDefault(x => x.Footnote_Symbol == "*");
if (!footnotes.Contains(defaultFootnote))
{
footnotes.Add(defaultFootnote);
}*/
int cellLengthFootnote = 0;
if (ws.Name == wsPRName)
{
foreach (var footnote in footnotes)
{
ws.Cells[currentRow, 1].RichText.Add(footnote.Footnote_Symbol).VerticalAlign = OfficeOpenXml.Style.ExcelVerticalAlignmentFont.Superscript;
ws.Cells[currentRow, 1].RichText.Add(" " + footnote.Footnote + ";");
ws.Cells[currentRow, 1].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Top;
cellLengthFootnote += footnote.Footnote_Symbol.ToString().Length + footnote.Footnote.ToString().Length + 2;
if (cellLengthFootnote > prFootnoteCharsNewLine)
{
ws.Cells[currentRow, 1].RichText.Add("\r\n");;
cellLengthFootnote = 0;
ws.Row(currentRow).Height += newLineHeight;
}
}
}
else
{
ws.Cells[currentRow, 1].Value = defNote;
ws.Row(currentRow).Height = 28;
}
var pr = ws.Cells[startRow, 1, currentRow - 1, maxCol];
//pr.Style.Border.InsideBorder = prBorderWidth;
//pr.Style.Border.InsideBorderColor = prBorder;
//pr.Style.Border.OutsideBorder = prBorderWidth;
//pr.Style.Border.OutsideBorderColor = prBorder;
pr.Style.Font.Size = prFontSize;
pr = ws.Cells[startRow, 1, currentRow, maxCol];
pr.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
pr.Style.WrapText = true;
ws.Column(1).Width = prNumberWidth;
ws.Column(2).Width = prIndicatorWidth;
footnotes.Clear();
indicatorNumber = 1;
/*var totalHeight = ExcelFunctions.getTotalHeight(ws,4);
var totalWidth = ExcelFunctions.getTotalWidth(ws,1);
var fitHeight = (int)(totalWidth / fitRatio);
var fitWidth = (int)(totalHeight * fitRatio);
if (ws.Name == "Def_WIH Obs") { System.Diagnostics.Debugger.Break(); }
if (fitHeight > totalHeight)
{
var fitAddHeightTotal = (fitHeight - totalHeight);
var fitAddHeightPerRow = fitAddHeightTotal / fitAdjustableRows.Count;
foreach (var row in fitAdjustableRows)
{
ws.Row(row).Height += fitAddHeightPerRow;
}
}
else
{
while ((fitWidth - totalWidth) / fitWidth > 0.001 )
{
var fitAddWidthTotal = (fitWidth - totalWidth) / 10;
var fitAddWidthPerRow = fitAddWidthTotal / (ws.LastColumnUsed().ColumnNumber() - 1);
foreach (var col in ws.Columns(2, ws.LastColumnUsed().ColumnNumber()))
{
col.Width += fitAddWidthPerRow / 5.69;
}
ExcelFunctions.AutoFitWorksheet(ws, 2, 3, newLineHeight);
totalHeight = ExcelFunctions.getTotalHeight(ws, 4);
totalWidth = ExcelFunctions.getTotalWidth(ws, 1);
fitHeight = (int)(totalWidth / fitRatio);
fitWidth = (int)(totalHeight * fitRatio);
}
}*/
}
}
FileStream fs = new FileStream(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.xlsx"), FileMode.Create);
ef.SaveAs(fs);
fs.Close();
//var test = GemBox.Spreadsheet.ExcelFile.Load(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test.xlsx"), LoadOptions.XlsxDefault);
var test = new Spire.Xls.Workbook();
test.LoadFromFile(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.xlsx"));
// Set PDF template
Spire.Pdf.PdfDocument pdfDocument = new Spire.Pdf.PdfDocument();
pdfDocument.PageSettings.Orientation = Spire.Pdf.PdfPageOrientation.Landscape;
pdfDocument.PageSettings.Width = 970;
pdfDocument.PageSettings.Height = 850;
//Convert Excel to PDF using the template above
Spire.Xls.Converter.PdfConverter pdfConverter = new Spire.Xls.Converter.PdfConverter(test);
Spire.Xls.Converter.PdfConverterSettings settings = new Spire.Xls.Converter.PdfConverterSettings();
settings.TemplateDocument = pdfDocument;
pdfDocument = pdfConverter.Convert(settings);
// Save and preview PDF
pdfDocument.SaveToFile(this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/test1.pdf"));
//wb.SaveAs(preImage);
MemoryStream postImage = new MemoryStream();
SLDocument postImageWb = new SLDocument(preImage);
string picPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logo.png");
SLPicture picLogo = new SLPicture(picPath);
string picPathOPEO = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/logoOPEO.png");
SLPicture picLogoOPEO = new SLPicture(picPathOPEO);
string picMonthlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/Monthly.png");
SLPicture picMonthly = new SLPicture(picMonthlyPath);
string picQuaterlyPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/quaterly.png");
SLPicture picQuaterly = new SLPicture(picQuaterlyPath);
string picNAPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/na.png");
SLPicture picNA = new SLPicture(picNAPath);
string picTargetPath = this.HttpContext.ApplicationInstance.Server.MapPath("~/App_Data/target.png");
SLPicture picTarget = new SLPicture(picTargetPath);
/*
foreach (var ws in wb.Worksheets)
{
postImageWb.SelectWorksheet(ws.Name);
for (int i = 1; i < 20; ++i)
{
var a = postImageWb.GetRowHeight(i);
}
picLogo.SetPosition(0, 0);
picLogo.ResizeInPercentage(25, 25);
postImageWb.InsertPicture(picLogo);
picLogoOPEO.SetRelativePositionInPixels(0, ws.LastColumnUsed().ColumnNumber() + 1, -140, 0);
picLogoOPEO.ResizeInPercentage(45, 45);
postImageWb.InsertPicture(picLogoOPEO);
if (ws.Name.Substring(0, 3) != "Def")
{
picTarget.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -240, 1);
picNA.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -400, 1);
picMonthly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -500, 1);
picQuaterly.SetRelativePositionInPixels(ws.LastRowUsed().RowNumber() + 1, ws.LastColumnUsed().ColumnNumber() + 1, -490, 1);
picMonthly.ResizeInPercentage(70, 70);
picQuaterly.ResizeInPercentage(70, 70);
picNA.ResizeInPercentage(70, 70);
picTarget.ResizeInPercentage(70, 70);
postImageWb.InsertPicture(picMonthly);
postImageWb.InsertPicture(picQuaterly);
postImageWb.InsertPicture(picNA);
postImageWb.InsertPicture(picTarget);
}
}*/
// Prepare the response
HttpResponse httpResponse = this.HttpContext.ApplicationInstance.Context.Response;
httpResponse.Clear();
httpResponse.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.xlsx\"");
//httpResponse.ContentType = "application/pdf";
//httpResponse.AddHeader("content-disposition", "attachment;filename=\"test.pdf\"");
// Flush the workbook to the Response.OutputStream
using (MemoryStream memoryStream = new MemoryStream())
{
postImageWb.SaveAs(memoryStream);
memoryStream.WriteTo(httpResponse.OutputStream);
memoryStream.Close();
}
httpResponse.End();
return View(viewModel);
}
public static byte[] ConvertToPdf(string fileName)
{
string contentType = MimeMapping.GetMimeMapping(fileName);
MemoryStream mem = new MemoryStream();
switch (contentType.ToLower())
{
case "image/gif":
case "image/jpg":
case "image/png":
case "image/jpeg":
case "application/BMP":
case "image/bmp":
case "image/tiff":
if (!string.IsNullOrEmpty(fileName))
{
Document document = new Document(PageSize.LETTER, 10, 10, 10, 10);
//using ( var stream = new MemoryStream() )
//{
PdfWriter.GetInstance(document, mem);
document.Open();
using (var imageStream = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
var image = Image.GetInstance(imageStream);
image.ScaleAbsolute(585, 750);
//image.ScaleAbsoluteHeight(PageSize.A4.Height - 20);
//image.ScaleToFit(PageSize.LETTER);
document.Add(image);
}
document.Close();
// return stream.ToArray();
//}
}
break;
case "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet":
case "application/vnd.ms-excel":
case "application/xlsx":
case "application/xls":
case "application/vnd.ms-excel.sheet.macroEnabled.12":
case "application/XLSM":
if (!string.IsNullOrEmpty(fileName))
{
Spire.Xls.Workbook workbook = new Spire.Xls.Workbook();
workbook.LoadFromFile(fileName, true);
workbook.SaveToStream(mem, Spire.Xls.FileFormat.PDF);
}
break;
case "application/vnd.openxmlformats-officedocument.wordprocessingml.document":
case "application/msword":
case "application/docx":
case "application/doc":
if (!string.IsNullOrEmpty(fileName))
{
Spire.Doc.Document document = new Spire.Doc.Document();
document.LoadFromFile(fileName, Spire.Doc.FileFormat.Auto);
document.SaveToStream(mem, Spire.Doc.FileFormat.PDF);
document.Close();
}
break;
case "application/html":
case "application/txt":
case "application/htm":
case "text/html":
case "application/xml":
case "text/plain":
if (!string.IsNullOrEmpty(fileName))
{
Spire.Pdf.PdfDocument pdf = new Spire.Pdf.PdfDocument();
PdfHtmlLayoutFormat htmlLayoutFormat = new PdfHtmlLayoutFormat
{
Layout = PdfLayoutType.Paginate,
FitToPage = Clip.Width,
LoadHtmlTimeout = 60 * 1000
};
htmlLayoutFormat.IsWaiting = true;
PdfPageSettings setting = new PdfPageSettings();
setting.Size = PdfPageSize.A4;
Thread thread = new Thread(() => { pdf.LoadFromHTML(fileName, true, true, true); });
thread.SetApartmentState(ApartmentState.STA);
thread.Start();
thread.Join();
pdf.SaveToStream(mem, Spire.Pdf.FileFormat.PDF);
}
break;
case "application/msg":
case "application/octet-stream":
case "multipart/related":
case "application/ZIP":
case "application/VCF":
default:
break;
}
return(mem.ToArray());
}
