private static string CreateExcelSampleContent(string fileName, string content64, ref int sheetsNumber) { string tempPath = HttpRuntime.AppDomainAppPath + "_Template\\Export";// System.Configuration.ConfigurationManager.AppSettings["iMarkets.Service.Core.ExcelService.TempPath"]; if (!Directory.Exists(tempPath)) { Directory.CreateDirectory(tempPath); } byte[] content = Convert.FromBase64String(content64); File.WriteAllBytes(String.Format("{0}\\{1}", tempPath, fileName), content); CMixExcel mixExcel = new CMixExcel(String.Format("{0}\\{1}", tempPath, fileName)); sheetsNumber = GetSheetNumber(mixExcel); mixExcel.CloseStream(); return(content64); }
private static string BussinessWork2Ex(string pathFile, DataSet contentExcel) { CExcelCSVOutput output = new CExcelCSVOutput(); string result = string.Empty; try { CMixExcel mixExcel = new CMixExcel(pathFile, true); result = output.CreateOutput2Ex(mixExcel, contentExcel); mixExcel.CloseStream(); //File.Delete(pathFile); } catch (Exception ex) { result = output.CreateOutput("0", "1", "Error BussinessWork2Ex", ex.Message + ex.StackTrace, null); } return(result); }
private static int GetSheetNumber(CMixExcel mixExcel) { int sheetsNumber = 0; if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage) { ExcelPackage excelPackage = (ExcelPackage)mixExcel.ExcelMixCore; ExcelWorksheets workSheets = excelPackage.Workbook.Worksheets; sheetsNumber = workSheets.Count; } else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook) { HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore; sheetsNumber = hssWorkbook.NumberOfSheets; } return(sheetsNumber); }
private static string BussinessWork(string pathFile, bool isReplaceSpecialString = true) { CExcelCSVOutput output = new CExcelCSVOutput(); string result = string.Empty; try { //FileInfo newFile = new FileInfo(pathFile); CMixExcel mixExcel = new CMixExcel(pathFile, false); result = output.CreateOutput("0", "0", "Success", "Success", mixExcel, isReplaceSpecialString); mixExcel.CloseStream(); File.Delete(pathFile); } catch (Exception ex) { result = output.CreateOutput("0", "1", " BussinessWork Error", ex.Message + ex.StackTrace, null); } return(result); }
private static int GetSheetNumber(CMixExcel mixExcel) { int sheetsNumber = 0; if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage) { ExcelPackage excelPackage = (ExcelPackage)mixExcel.ExcelMixCore; ExcelWorksheets workSheets = excelPackage.Workbook.Worksheets; sheetsNumber = workSheets.Count; } else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook) { HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore; sheetsNumber = hssWorkbook.NumberOfSheets; } return sheetsNumber; }
private static string MakeFileExcelSample(CMixExcel mixExcel, ref int sheetsNumber) { string result = string.Empty; Stream _stream = null; string fileNameTail = mixExcel.PathFile.Substring(mixExcel.PathFile.LastIndexOf('.')); string samplefileName = mixExcel.PathFile.Substring(0, mixExcel.PathFile.LastIndexOf('.')); string pathsamplefileName = String.Format("{0}_Sample{1}", samplefileName, fileNameTail); FileStream fstream = File.Open(pathsamplefileName, FileMode.OpenOrCreate); fstream.Close(); File.Copy(mixExcel.PathFile, pathsamplefileName, true); CMixExcel mixExcelSample = new CMixExcel(pathsamplefileName); if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage) { #region Excel 2007++ ExcelPackage excelSamplePackage = (ExcelPackage)mixExcelSample.ExcelMixCore; ExcelPackage excelPackage = (ExcelPackage)mixExcel.ExcelMixCore; ExcelWorksheets workSheetsSample = excelSamplePackage.Workbook.Worksheets; ExcelWorksheets workSheets = excelPackage.Workbook.Worksheets; sheetsNumber = workSheetsSample.Count; for (int i = 1; i <= workSheetsSample.Count; i++) { var worksheetSample = workSheetsSample[i]; var worksheet = workSheets[i]; System.Xml.XmlNodeList test = worksheetSample.WorksheetXml.GetElementsByTagName("dimension"); if (test != null && test.Count > 0) { System.Xml.XmlNode value = test[0]; //<dimension ref="A1:J17" /> string outerXml = value.OuterXml.Substring("<dimension ref=\"".Length); string rangesize = outerXml.Substring(0, outerXml.IndexOf('"')); if (rangesize != "A1") { string[] arrayRange = rangesize.Split(':'); int indextemp = 0; int temp; string MinRow = string.Empty, MaxRow = string.Empty; int MinColumn = 0, MaxColumn = 0; string range1 = arrayRange[0]; while (!int.TryParse(range1[indextemp].ToString(), out temp) && indextemp < range1.Length) { MinColumn += (System.Text.Encoding.ASCII.GetBytes(range1[indextemp].ToString())[0] - 64); indextemp++; } MinRow = range1.Substring(indextemp); indextemp = 0; string range2 = arrayRange[1]; while (!int.TryParse(range2[indextemp].ToString(), out temp) && indextemp < range2.Length) { MaxColumn += (System.Text.Encoding.ASCII.GetBytes(range2[indextemp].ToString())[0] - 64); indextemp++; } MaxRow = range2.Substring(indextemp); if (!string.IsNullOrEmpty(MinRow) && MinColumn > 0 && !string.IsNullOrEmpty(MaxRow) && MaxColumn > 0) { int intMinRow = int.Parse(MinRow); int intMaxRow = int.Parse(MaxRow); for (int r = intMinRow; r <= intMaxRow; r++) { for (int c = MinColumn; c <= MaxColumn + 1; c++) { if (c > MinColumn) worksheetSample.Cells[r, c].Value = worksheet.Cells[r, c - 1].Value; else if (r == intMinRow) worksheetSample.Cells[r, c].Value = "Heading"; else if (r == intMinRow + 1) worksheetSample.Cells[r, c].Value = "Schema"; else worksheetSample.Cells[r, c].Value = string.Empty; } } } }//(rangesize != "A1") } } excelSamplePackage.Save(); _stream = excelSamplePackage.Stream; #endregion } else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook) { #region Excel 2003-- HSSFWorkbook hssWorkbookSample = (HSSFWorkbook)mixExcelSample.ExcelMixCore; HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore; sheetsNumber = hssWorkbookSample.NumberOfSheets; for (int i = 0; i < hssWorkbookSample.NumberOfSheets; i++) { NPOI.HSSF.UserModel.HSSFSheet excelSheetSample = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbookSample.GetSheetAt(i); NPOI.HSSF.UserModel.HSSFSheet excelSheet = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbook.GetSheetAt(i); NPOI.HSSF.Record.DimensionsRecord sheetDementionSample = excelSheetSample.Sheet.Dimensions; NPOI.HSSF.Record.DimensionsRecord sheetDemention = excelSheet.Sheet.Dimensions; for (int r = sheetDementionSample.FirstRow; r <= sheetDementionSample.LastRow; r++) { IRow rowSample = excelSheetSample.GetRow(r); if (rowSample != null) excelSheetSample.RemoveRow(rowSample); } for (int r = sheetDemention.FirstRow; r <= sheetDemention.LastRow; r++) { int maxCol = sheetDementionSample.LastCol; int minCol = sheetDementionSample.FirstCol; for (int c = minCol; c <= maxCol + 1; c++) { try { IRow rowSample = excelSheetSample.GetRow(r); IRow row = excelSheet.GetRow(r); if (row != null) { ICell excelSheetGetRowGetCellSample = rowSample.GetCell(c); if (excelSheetGetRowGetCellSample == null) excelSheetGetRowGetCellSample = rowSample.CreateCell(c); if (c > sheetDementionSample.FirstCol) { ICell excelSheetGetRowGetCell = row.GetCell(c - 1); if (excelSheetGetRowGetCell != null) { switch (excelSheetGetRowGetCell.CellType) { case CellType.String: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue); break; case CellType.Numeric: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue); break; case CellType.Boolean: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue); break; case CellType.Blank: excelSheetGetRowGetCellSample.SetCellValue(string.Empty); break; case CellType.Error: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue); break; case CellType.Formula: { switch (excelSheetGetRowGetCellSample.CachedFormulaResultType) { case CellType.Numeric: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue); break; case CellType.String: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue); break; case CellType.Boolean: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue); break; case CellType.Error: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue); break; } } break; case CellType.Unknown: excelSheetGetRowGetCellSample.SetCellValue("Unknown"); break; } } } else //if (c > sheetDementionSample.FirstCol) { if (r == sheetDementionSample.FirstRow) { excelSheetGetRowGetCellSample.SetCellValue("Heading"); } else if (r == sheetDementionSample.FirstRow + 1) { excelSheetGetRowGetCellSample.SetCellValue("Schema"); } else excelSheetGetRowGetCellSample.SetCellValue(string.Empty); } } } catch { } } } } FileStream fs = new FileStream(mixExcelSample.PathFile, FileMode.OpenOrCreate); hssWorkbookSample.Write(fs); _stream = fs; #endregion } if (_stream != null) { byte[] binaryData = new byte[_stream.Length]; long bytesRead = _stream.Read(binaryData, 0, (int)_stream.Length); _stream.Close(); result = Convert.ToBase64String(binaryData); } mixExcelSample.CloseStream(); return result; }
private static string CreateExcelSampleContent(string fileName, string content64, ref int sheetsNumber) { string tempPath = HttpRuntime.AppDomainAppPath + "_Template\\Export";// System.Configuration.ConfigurationManager.AppSettings["iMarkets.Service.Core.ExcelService.TempPath"]; if (!Directory.Exists(tempPath)) Directory.CreateDirectory(tempPath); byte[] content = Convert.FromBase64String(content64); File.WriteAllBytes(String.Format("{0}\\{1}", tempPath, fileName), content); CMixExcel mixExcel = new CMixExcel(String.Format("{0}\\{1}", tempPath, fileName)); sheetsNumber = GetSheetNumber(mixExcel); mixExcel.CloseStream(); return content64; }
private static string MakeFileExcelSample(CMixExcel mixExcel, ref int sheetsNumber) { string result = string.Empty; Stream _stream = null; string fileNameTail = mixExcel.PathFile.Substring(mixExcel.PathFile.LastIndexOf('.')); string samplefileName = mixExcel.PathFile.Substring(0, mixExcel.PathFile.LastIndexOf('.')); string pathsamplefileName = String.Format("{0}_Sample{1}", samplefileName, fileNameTail); FileStream fstream = File.Open(pathsamplefileName, FileMode.OpenOrCreate); fstream.Close(); File.Copy(mixExcel.PathFile, pathsamplefileName, true); CMixExcel mixExcelSample = new CMixExcel(pathsamplefileName); if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage) { #region Excel 2007++ ExcelPackage excelSamplePackage = (ExcelPackage)mixExcelSample.ExcelMixCore; ExcelPackage excelPackage = (ExcelPackage)mixExcel.ExcelMixCore; ExcelWorksheets workSheetsSample = excelSamplePackage.Workbook.Worksheets; ExcelWorksheets workSheets = excelPackage.Workbook.Worksheets; sheetsNumber = workSheetsSample.Count; for (int i = 1; i <= workSheetsSample.Count; i++) { var worksheetSample = workSheetsSample[i]; var worksheet = workSheets[i]; System.Xml.XmlNodeList test = worksheetSample.WorksheetXml.GetElementsByTagName("dimension"); if (test != null && test.Count > 0) { System.Xml.XmlNode value = test[0]; //<dimension ref="A1:J17" /> string outerXml = value.OuterXml.Substring("<dimension ref=\"".Length); string rangesize = outerXml.Substring(0, outerXml.IndexOf('"')); if (rangesize != "A1") { string[] arrayRange = rangesize.Split(':'); int indextemp = 0; int temp; string MinRow = string.Empty, MaxRow = string.Empty; int MinColumn = 0, MaxColumn = 0; string range1 = arrayRange[0]; while (!int.TryParse(range1[indextemp].ToString(), out temp) && indextemp < range1.Length) { MinColumn += (System.Text.Encoding.ASCII.GetBytes(range1[indextemp].ToString())[0] - 64); indextemp++; } MinRow = range1.Substring(indextemp); indextemp = 0; string range2 = arrayRange[1]; while (!int.TryParse(range2[indextemp].ToString(), out temp) && indextemp < range2.Length) { MaxColumn += (System.Text.Encoding.ASCII.GetBytes(range2[indextemp].ToString())[0] - 64); indextemp++; } MaxRow = range2.Substring(indextemp); if (!string.IsNullOrEmpty(MinRow) && MinColumn > 0 && !string.IsNullOrEmpty(MaxRow) && MaxColumn > 0) { int intMinRow = int.Parse(MinRow); int intMaxRow = int.Parse(MaxRow); for (int r = intMinRow; r <= intMaxRow; r++) { for (int c = MinColumn; c <= MaxColumn + 1; c++) { if (c > MinColumn) { worksheetSample.Cells[r, c].Value = worksheet.Cells[r, c - 1].Value; } else if (r == intMinRow) { worksheetSample.Cells[r, c].Value = "Heading"; } else if (r == intMinRow + 1) { worksheetSample.Cells[r, c].Value = "Schema"; } else { worksheetSample.Cells[r, c].Value = string.Empty; } } } } }//(rangesize != "A1") } } excelSamplePackage.Save(); _stream = excelSamplePackage.Stream; #endregion } else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook) { #region Excel 2003-- HSSFWorkbook hssWorkbookSample = (HSSFWorkbook)mixExcelSample.ExcelMixCore; HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore; sheetsNumber = hssWorkbookSample.NumberOfSheets; for (int i = 0; i < hssWorkbookSample.NumberOfSheets; i++) { NPOI.HSSF.UserModel.HSSFSheet excelSheetSample = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbookSample.GetSheetAt(i); NPOI.HSSF.UserModel.HSSFSheet excelSheet = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbook.GetSheetAt(i); NPOI.HSSF.Record.DimensionsRecord sheetDementionSample = excelSheetSample.Sheet.Dimensions; NPOI.HSSF.Record.DimensionsRecord sheetDemention = excelSheet.Sheet.Dimensions; for (int r = sheetDementionSample.FirstRow; r <= sheetDementionSample.LastRow; r++) { IRow rowSample = excelSheetSample.GetRow(r); if (rowSample != null) { excelSheetSample.RemoveRow(rowSample); } } for (int r = sheetDemention.FirstRow; r <= sheetDemention.LastRow; r++) { int maxCol = sheetDementionSample.LastCol; int minCol = sheetDementionSample.FirstCol; for (int c = minCol; c <= maxCol + 1; c++) { try { IRow rowSample = excelSheetSample.GetRow(r); IRow row = excelSheet.GetRow(r); if (row != null) { ICell excelSheetGetRowGetCellSample = rowSample.GetCell(c); if (excelSheetGetRowGetCellSample == null) { excelSheetGetRowGetCellSample = rowSample.CreateCell(c); } if (c > sheetDementionSample.FirstCol) { ICell excelSheetGetRowGetCell = row.GetCell(c - 1); if (excelSheetGetRowGetCell != null) { switch (excelSheetGetRowGetCell.CellType) { case CellType.String: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue); break; case CellType.Numeric: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue); break; case CellType.Boolean: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue); break; case CellType.Blank: excelSheetGetRowGetCellSample.SetCellValue(string.Empty); break; case CellType.Error: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue); break; case CellType.Formula: { switch (excelSheetGetRowGetCellSample.CachedFormulaResultType) { case CellType.Numeric: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.NumericCellValue); break; case CellType.String: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.StringCellValue); break; case CellType.Boolean: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.BooleanCellValue); break; case CellType.Error: excelSheetGetRowGetCellSample.SetCellValue(excelSheetGetRowGetCell.ErrorCellValue); break; } } break; case CellType.Unknown: excelSheetGetRowGetCellSample.SetCellValue("Unknown"); break; } } } else //if (c > sheetDementionSample.FirstCol) { if (r == sheetDementionSample.FirstRow) { excelSheetGetRowGetCellSample.SetCellValue("Heading"); } else if (r == sheetDementionSample.FirstRow + 1) { excelSheetGetRowGetCellSample.SetCellValue("Schema"); } else { excelSheetGetRowGetCellSample.SetCellValue(string.Empty); } } } } catch { } } } } FileStream fs = new FileStream(mixExcelSample.PathFile, FileMode.OpenOrCreate); hssWorkbookSample.Write(fs); _stream = fs; #endregion } if (_stream != null) { byte[] binaryData = new byte[_stream.Length]; long bytesRead = _stream.Read(binaryData, 0, (int)_stream.Length); _stream.Close(); result = Convert.ToBase64String(binaryData); } mixExcelSample.CloseStream(); return(result); }
private static string BussinessWork2Ex(string pathFile, DataSet contentExcel) { CExcelCSVOutput output = new CExcelCSVOutput(); string result = string.Empty; try { CMixExcel mixExcel = new CMixExcel(pathFile, true); result = output.CreateOutput2Ex(mixExcel, contentExcel); mixExcel.CloseStream(); //File.Delete(pathFile); } catch (Exception ex) { result = output.CreateOutput("0", "1", "Error BussinessWork2Ex", ex.Message + ex.StackTrace, null); } return result; }
private static string BussinessWork(string pathFile,bool isReplaceSpecialString = true) { CExcelCSVOutput output = new CExcelCSVOutput(); string result = string.Empty; try { //FileInfo newFile = new FileInfo(pathFile); CMixExcel mixExcel = new CMixExcel(pathFile,false); result = output.CreateOutput("0", "0", "Success", "Success", mixExcel, isReplaceSpecialString); mixExcel.CloseStream(); File.Delete(pathFile); } catch (Exception ex) { result = output.CreateOutput("0", "1", " BussinessWork Error", ex.Message + ex.StackTrace, null); } return result; }