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;
}
