private string Table2(CMixExcel mixExcel)
{
string result = string.Empty;
result = "Name;Description\n";
if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage)
{
ExcelPackage epk = (ExcelPackage)mixExcel.ExcelMixCore;
for (int i = 1; i <= epk.Workbook.Worksheets.Count; i++)
result += String.Format("{0};{1}\n", epk.Workbook.Worksheets[i].Name.Replace(CSVCELLDELEMETER, REPLACEDELEMETER), epk.Workbook.Worksheets[i].Name.Replace(CSVCELLDELEMETER, REPLACEDELEMETER));
}
else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook)
{
HSSFWorkbook hssWorkBook = (HSSFWorkbook)mixExcel.ExcelMixCore;
for (int i = 0; i < hssWorkBook.NumberOfSheets; i++)
{
ISheet excelSheet = hssWorkBook.GetSheetAt(i);
result += String.Format("{0};{1}\n", excelSheet.SheetName.Replace(CSVCELLDELEMETER, REPLACEDELEMETER), excelSheet.SheetName.Replace(CSVCELLDELEMETER, REPLACEDELEMETER));
}
}
else
{
result = string.Empty;
}
return result;
}
public string CreateOutput2Ex(CMixExcel mixExcel, System.Data.DataSet ds)
{
string result = string.Empty;
try
{
if (mixExcel.ExcelMixCore != null && mixExcel.ExcelMixCore is ExcelPackage)
{
#region Excel 2007++
ExcelPackage pck = (ExcelPackage)mixExcel.ExcelMixCore;
if (pck.Workbook.Worksheets.Count == ds.Tables.Count && ds != null)
{
for (int i = 0; i < pck.Workbook.Worksheets.Count; i++)
{
var worksheet = pck.Workbook.Worksheets[i + 1];//do worksheets bat dau tu index 1
System.Xml.XmlNodeList test = worksheet.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);
System.Data.DataTable dt = ds.Tables[i];
if (dt != null)
{
int indexSchema = 1;
for (int r = 1; r <= intMaxRow; r++)
if (worksheet.Cells[r, 1].Value != null && worksheet.Cells[r, 1].Value.ToString().ToLower() == "schema")
{
indexSchema = r; break;
}
List<int> lMappingIndex = new List<int>();
for (int c = MinColumn; c <= MaxColumn; c++)
{
lMappingIndex.Add(-1);
for (int j = 0; j < dt.Rows[0].Table.Columns.Count; j++)
if (worksheet.Cells[indexSchema, c].Value != null && dt.Rows[0][j] != null)
{
if (worksheet.Cells[indexSchema, c].Value.ToString() == dt.Rows[0][j].ToString())
{
lMappingIndex[c - MinColumn] = j;
break;
}
}
}
int indexStartInsert = 1;
ExcelRange excelRangeTemplate = null;
for (int r = 1; r <= intMaxRow; r++)
{
if (worksheet.Cells[r, 1].Value == null)
worksheet.Cells[r, 1].Value = string.Empty;
if (worksheet.Cells[r, 1].Value.ToString().ToLower() == "rowtemplate")
{
excelRangeTemplate = worksheet.Cells[r, MinColumn + 1, r, MaxColumn];
indexStartInsert = r; break;
}
}
ExcelRange excelRangeSummary = worksheet.Cells[indexStartInsert + 1, MinColumn, indexStartInsert + 1, MaxColumn];
ExcelRange excelRangeSummaryNew = worksheet.Cells[indexStartInsert + dt.Rows.Count, MinColumn, indexStartInsert + dt.Rows.Count, MaxColumn];
excelRangeSummary.Copy(excelRangeSummaryNew);
excelRangeSummary.Clear();
int startValueIndentity = 1;
for (int r = indexStartInsert + 1; r <= indexStartInsert + dt.Rows.Count - 1; r++)
{
ExcelRange excelRangeNew = worksheet.Cells[r, MinColumn + 1, r, MaxColumn];
excelRangeTemplate.Copy(excelRangeNew);
for (int c = MinColumn; c <= MaxColumn; c++)
{
ExcelRange worksheetCells = worksheet.Cells[r, c];
int indexDT = lMappingIndex[c - MinColumn];
if (indexDT != -1)
{
string insertValue = dt.Rows[r - indexStartInsert][indexDT].ToString();
//if (insertValue.StartsWith("'") && insertValue.EndsWith("'"))
worksheetCells.Value = insertValue.Replace("'", string.Empty);
//else
// worksheetCells.Value = Convert.ToInt64(insertValue);
//worksheetCells.AutoFitColumns();
ExcelColumn excelColumn = worksheet.Column(r);
if (excelColumn != null && !excelColumn.Hidden)
excelColumn.AutoFit();
}
else if (worksheetCells.Value != null && !string.IsNullOrEmpty(worksheetCells.Value.ToString()) && worksheetCells.Value.ToString().ToUpper() == "IDENTITY")
{
worksheetCells.Value = startValueIndentity;
startValueIndentity++;
}
}
}
worksheet.DeleteRow(indexSchema, 1);
worksheet.DeleteRow(indexStartInsert - 1, 1);
}
}
}//(rangesize != "A1")
else
{
worksheet.Cells[1, 1].Value = ds.Tables[i].Rows[0][0];
}
}
//else
//{
// result = "<OutputValue Result=\"0\" ErrorMessage=\"Excel file have error page!!!!\"/>";
// break;
//}
worksheet.Column(1).Hidden = true;
}
FileStream fs = new FileStream(mixExcel.PathFile + "_Temp.xlsx", FileMode.OpenOrCreate);
if (fs != null)
{
pck.SaveAs(fs);
fs.Close();
fs = new FileStream(mixExcel.PathFile + "_Temp.xlsx", FileMode.OpenOrCreate);
byte[] binaryData = new byte[fs.Length];
long bytesRead = fs.Read(binaryData, 0, (int)fs.Length);
fs.Close();
string base64Data = Convert.ToBase64String(binaryData);
result = String.Format("<OutputValue Result=\"1\" FileContent=\"{0}\" />", base64Data);
}
}
else //(pck.Workbook.Worksheets.Count == ds.Tables.Count && ds != null)
{
result = "<OutputValue Result=\"0\" ErrorMessage=\"Excel file have not suit with data CSV tables!!!!\"/>";
}
#endregion
}
else if (mixExcel.ExcelMixCore != null && mixExcel.ExcelMixCore is HSSFWorkbook)
{
#region Excel 2003--
HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore;
if (hssWorkbook.NumberOfSheets == ds.Tables.Count && ds != null)
{
for (int i = 0; i < hssWorkbook.NumberOfSheets; i++)
{
NPOI.HSSF.UserModel.HSSFSheet excelSheet = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbook.GetSheetAt(i);
NPOI.HSSF.Record.DimensionsRecord sheetDemention = excelSheet.Sheet.Dimensions;
System.Data.DataTable dt = ds.Tables[i];
int indexSchema = 0;
for (int r = 0; r < sheetDemention.LastRow; r++)
{
try
{
IRow row = excelSheet.GetRow(r);
if (row != null)
{
ICell excelSheetGetRowGetCell = row.GetCell(0);
string cellValues = GetCell2003Value(excelSheetGetRowGetCell);
if (!string.IsNullOrEmpty(cellValues) && cellValues.ToLower() == "schema")
{
indexSchema = r;
break;
}
}
}
catch { }
}
List<int> lMappingIndex = new List<int>();
for (int c = sheetDemention.FirstCol; c <= sheetDemention.LastCol; c++)
{
lMappingIndex.Add(-1);
for (int j = 0; j < dt.Rows[0].Table.Columns.Count; j++)
{
try
{
IRow row = excelSheet.GetRow(indexSchema);
if (row != null)
{
ICell excelSheetGetRowGetCell = row.GetCell(c);
string cellValue = GetCell2003Value(excelSheetGetRowGetCell);
if (!string.IsNullOrEmpty(cellValue) && dt.Rows[0][j] != null)
{
if (cellValue == dt.Rows[0][j].ToString())
{
lMappingIndex[c - sheetDemention.FirstRow] = j;
break;
}
}
}
}
catch { }
}
}
int indexStartInsert = 0;
IRow excelRowTemplate = null;
for (int r = 0; r < sheetDemention.LastRow; r++)
{
try
{
IRow row = excelSheet.GetRow(r);
if (row != null)
{
ICell excelSheetGetRowGetCell = row.GetCell(0);
string cellValues = GetCell2003Value(excelSheetGetRowGetCell);
if (!string.IsNullOrEmpty(cellValues) && cellValues.ToLower() == "rowtemplate")
{
excelRowTemplate = excelSheet.GetRow(r);
indexStartInsert = r; break;
}
}
}
catch { }
}
///phan code ben excel 207 tro len
IRow excelRowSummary = excelSheet.GetRow(indexStartInsert + 1);
//ExcelRange excelRangeSummary = worksheet.Cells[indexStartInsert + 1, MinColumn, indexStartInsert + 1, MaxColumn];
IRow excelRowSummaryNew = excelSheet.GetRow(indexStartInsert + dt.Rows.Count);
excelRowSummaryNew = excelRowSummary.CopyRowTo(indexStartInsert + dt.Rows.Count);
excelSheet.RemoveRow(excelSheet.GetRow(indexStartInsert + 1));
int startValueIndentity = 1;
for (int r = indexStartInsert + 1; r <= indexStartInsert + dt.Rows.Count - 1; r++)
{
try
{
IRow excelRowNew = null;
excelRowNew = excelRowTemplate.CopyRowTo(r);
for (int c = sheetDemention.FirstCol; c < sheetDemention.LastCol; c++)
{
//ICell worksheetCells = excelRowNew.GetCell(c, MissingCellPolicy.CREATE_NULL_AS_BLANK);
ICell worksheetCells = excelRowNew.CreateCell(c);
//ExcelRange worksheetCells = worksheet.Cells[r, c];
string cellValue = GetCell2003Value(worksheetCells);
int indexMap = c - sheetDemention.FirstCol;
if (indexMap >= 0 && indexMap < lMappingIndex.Count)
{
int indexDT = lMappingIndex[c - sheetDemention.FirstCol];
if (indexDT != -1)
{
string insertValue = dt.Rows[r - indexStartInsert][indexDT].ToString();
if (insertValue.StartsWith("'") && insertValue.EndsWith("'"))
{
worksheetCells.SetCellValue(insertValue.Replace("'", string.Empty));
worksheetCells.SetCellType(CellType.String);
}
else
{
worksheetCells.SetCellValue(Convert.ToInt64(insertValue));
worksheetCells.SetCellType(CellType.Numeric);
}
worksheetCells.Sheet.AutoSizeColumn(c);
}
else if (!string.IsNullOrEmpty(cellValue) && cellValue.ToUpper() == "IDENTITY")
{
worksheetCells.SetCellValue(startValueIndentity);
worksheetCells.SetCellType(CellType.Numeric);
startValueIndentity++;
}
}
if (c == sheetDemention.FirstCol && cellValue == "RowTemplate")
{
worksheetCells.SetCellValue(string.Empty);
worksheetCells.SetCellType(CellType.String);
}
}
}
catch { }
}
excelSheet.RemoveRow(excelSheet.GetRow(indexSchema));
excelSheet.RemoveRow(excelSheet.GetRow(indexStartInsert));
for (int t = indexStartInsert; t < indexStartInsert + dt.Rows.Count; t++)
{
IRow tempRow = excelSheet.GetRow(t + 1);
if (tempRow != null)
{
tempRow.CopyRowTo(t);
excelSheet.RemoveRow(excelSheet.GetRow(t + 1));
}
}
for (int t = indexSchema; t < indexSchema + dt.Rows.Count; t++)
{
IRow tempRow = excelSheet.GetRow(t + 1);
if (tempRow != null)
{
tempRow.CopyRowTo(t);
excelSheet.RemoveRow(excelSheet.GetRow(t + 1));
}
}
///phan code ben excel 207 tro len
excelSheet.SetColumnHidden(0, true);
//excelSheet.RemoveColumn(0);
}
if (!string.IsNullOrEmpty(mixExcel.PathFile))
{
FileStream fs = new FileStream(mixExcel.PathFile, FileMode.OpenOrCreate);
hssWorkbook.Write(fs);
fs.Close();
if (fs != null)
{
fs = new FileStream(mixExcel.PathFile, FileMode.OpenOrCreate);
byte[] binaryData = new byte[fs.Length];
long bytesRead = fs.Read(binaryData, 0, (int)fs.Length);
fs.Close();
string base64Data = Convert.ToBase64String(binaryData);
result = String.Format("<OutputValue Result=\"1\" FileContent=\"{0}\" />", base64Data);
}
}
}
else //(hssWorkbook.NumberOfSheets == ds.Tables.Count && ds != null)
{
result = "<OutputValue Result=\"0\" ErrorMessage=\"Excel file have not suit with data CSV tables!!!!\"/>";
}
#endregion
}
}
catch (Exception ex)
{
result = String.Format("<OutputValue Result=\"0\" ErrorMessage=\"{0}\"/>", ex.Message + ex.StackTrace);
}
return result;
}
private string Table3(CMixExcel mixExcel)
{
StringBuilder result = new StringBuilder();
if (mixExcel != null && mixExcel.ExcelMixCore is ExcelPackage)
{
ExcelPackage excelPackage = (ExcelPackage)mixExcel.ExcelMixCore;
ExcelWorksheets workSheets = excelPackage.Workbook.Worksheets;
for (int i = 1; i <= workSheets.Count; i++)
{
var worksheet = workSheets[i];
System.Xml.XmlNodeList test = worksheet.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)
{
for (int c = MinColumn; c <= MaxColumn; c++)
{
ExcelColumn excelColumn = worksheet.Column(c);
result.Append(String.Format("column({0}:{1})", excelColumn.ColumnMin, excelColumn.ColumnMax));
if (c != MaxColumn)
result.Append(";");
}
result.Append("\n");
int intMinRow = int.Parse(MinRow);
int intMaxRow = int.Parse(MaxRow);
for (int r = intMinRow; r <= intMaxRow; r++)
{
for (int c = MinColumn; c <= MaxColumn; c++)
{
object valueCell = worksheet.Cells[r, c].Value;
if (valueCell != null)
result.Append(valueCell.ToString().Replace(CSVCELLDELEMETER, REPLACEDELEMETER));
else
result.Append(valueCell);
if (c != MaxColumn)
result.Append(";");
}
if(r < intMaxRow)
result.Append("\n");
}
result.Append("\n###\n");
}
}//(rangesize != "A1")
else
{
object valueCell = worksheet.Cells[1, 1].Value;
if (valueCell != null)
{
result.Append("column(1:1)" + "\n");
result.Append(valueCell.ToString().Replace(CSVCELLDELEMETER, REPLACEDELEMETER));
}
else
result.Append("[NULL]");
result.Append("\n###\n");
}
}
}
}
else if (mixExcel != null && mixExcel.ExcelMixCore is HSSFWorkbook)
{
HSSFWorkbook hssWorkbook = (HSSFWorkbook)mixExcel.ExcelMixCore;
for (int i = 0; i < hssWorkbook.NumberOfSheets; i++)
{
NPOI.HSSF.UserModel.HSSFSheet excelSheet = (NPOI.HSSF.UserModel.HSSFSheet)hssWorkbook.GetSheetAt(i);
NPOI.HSSF.Record.DimensionsRecord sheetDemention = excelSheet.Sheet.Dimensions;
for (int r = sheetDemention.FirstRow; r < sheetDemention.LastRow; r++)
{
for (int c = sheetDemention.FirstCol; c < sheetDemention.LastCol; c++)
{
try
{
IRow row = excelSheet.GetRow(r);
if (row != null)
{
ICell excelSheetGetRowGetCell = row.GetCell(c);
if (excelSheetGetRowGetCell != null)
{
switch (excelSheetGetRowGetCell.CellType)
{
case CellType.String:
result.Append(excelSheetGetRowGetCell.StringCellValue.Replace(CSVCELLDELEMETER,REPLACEDELEMETER) + ";"); break;
case CellType.Numeric:
result.Append(excelSheetGetRowGetCell.NumericCellValue + ";"); break;
case CellType.Boolean:
result.Append(excelSheetGetRowGetCell.BooleanCellValue + ";"); break;
case CellType.Blank:
result.Append("[Blank];"); break;
case CellType.Error:
result.Append(excelSheetGetRowGetCell.ErrorCellValue + ";"); break;
case CellType.Formula:
{
switch (excelSheetGetRowGetCell.CachedFormulaResultType)
{
case CellType.Numeric:
result.Append(excelSheetGetRowGetCell.NumericCellValue + ";"); break;
case CellType.String:
result.Append(excelSheetGetRowGetCell.StringCellValue.Replace(CSVCELLDELEMETER,REPLACEDELEMETER) + ";"); break;
case CellType.Boolean:
result.Append(excelSheetGetRowGetCell.BooleanCellValue + ";"); break;
case CellType.Error:
result.Append(excelSheetGetRowGetCell.ErrorCellValue + ";"); break;
}
}
break;
case CellType.Unknown:
result.Append("[Unknown];"); break;
}
}
else //excelSheetGetRowGetCell == null
{
result.Append("[NULL];");
}
}
}
catch { }
}
result.Append("\n");
}
result.Append("\n###\n");
}
}
// if (mixExcel != null)
// mixExcel.CloseStream();
return result.ToString();
}
public string CreateOutput(string ID, string Code, string Name, string Description, CMixExcel mixExcel,bool isReplaceSpectialString = true)
{
string result = string.Empty;
if (mixExcel != null)
result = String.Format("{0}\n###\n{1}\n###\n{2}", Table1(ID, Code, Name, Description), Table2(mixExcel), Table3(mixExcel));
else
result = String.Format("{0}", Table1(ID, Code, Name, Description));
if (isReplaceSpectialString)
result = result.Replace(REPLACEDELEMETER, CSVCELLDELEMETER);
return result;
}
