public static string generate_excel_report(ref DMS.Interface.oExcel objExcel, System.Data.DataView dv, string ExcelFileTmpl, string ExcelFileSaveAs, object sheetnm, string pwd)
{
ExcelFileTmpl = FixupPath(ExcelFileTmpl);
ExcelFileSaveAs = FixupPath(ExcelFileSaveAs);
if (objExcel == null)
{
objExcel = new oExcel();
if (ExcelFileTmpl == ExcelFileSaveAs)
{
objExcel.OpenFile(ExcelFileTmpl, "", false);
}
else
{
objExcel.OpenFile(ExcelFileTmpl, "");
}
}
try
{
objExcel.excelWorksheet = (Worksheet)objExcel.excelSheets[sheetnm];
if (dv.Count == 0)
{
objExcel.excelWorksheet.Delete();
}
else
{
System.Collections.Generic.List <oField> objFields = new System.Collections.Generic.List <oField>();
int CmtCount = objExcel.excelWorksheet.Comments.Count;
for (int i = 0; i < CmtCount; i++)
{
Range objCell = (Range)objExcel.excelWorksheet.Comments[i + 1].Parent;
oField objField = new oField();
objField.Row = objCell.Row;
objField.Column = objCell.Column;
objField.FieldName = objExcel.excelWorksheet.Comments[i + 1].Shape.TextFrame.Characters(Type.Missing, Type.Missing).Text.Trim().Replace("\n", "");
objFields.Add(objField);
}
for (int i = 0; i < CmtCount; i++)
{
objExcel.excelWorksheet.Comments[1].Delete();
}
int rowstart = 1;
int colstart = 1;
int rowlast = objExcel.excelWorksheet.UsedRange.Rows.Count;
int collast = objExcel.excelWorksheet.UsedRange.Columns.Count;
for (int row = 0; row < dv.Count - 1; row++)
{
Range objCopy = (Range)objExcel.excelWorksheet.get_Range(getcellnm(rowstart, colstart), getcellnm(rowlast, collast));
objCopy.Copy(Type.Missing);
Range objPaste = (Range)objExcel.excelWorksheet.get_Range(getcellnm(rowstart, colstart), Type.Missing);
objPaste.Insert(XlInsertShiftDirection.xlShiftDown, Type.Missing);
}
for (int row = 1; row <= dv.Count; row++)
{
((Range)objExcel.excelWorksheet.Cells[((rowlast - rowstart) + 1) * row + rowstart, collast + 1]).PageBreak = (int)XlPageBreak.xlPageBreakManual;
}
for (int row = 0; row < dv.Count; row++)
{
foreach (oField objField in objFields)
{
Range objCell = (Range)objExcel.excelWorksheet.Cells[row * rowlast + objField.Row, objField.Column];
if (objCell.Value2 != null && objCell.Value2.ToString().IndexOf("@1") > 0)
{
string[] FieldNames = objField.FieldName.Split(',');
for (int i = 1; i <= FieldNames.Length; i++)
{
string FieldName = FieldNames[i - 1].Trim();
objCell.Value2 = objCell.Value2.ToString().Replace("@" + i.ToString(), dv[row][FieldName].ToString().Trim());
}
}
else
{
if (dv[row][objField.FieldName] is System.String)
{
objCell.Value2 = dv[row][objField.FieldName].ToString().Trim();
}
else
{
objCell.Value2 = dv[row][objField.FieldName];
}
}
}
}
}
}
catch (Exception e)
{
objExcel.CloseFile();
objExcel.stopExcel();
return(e.Message);
}
if (ExcelFileSaveAs != null)
{
if (pwd != null && pwd != "")
{
for (int j = 1; j <= objExcel.excelSheets.Count; j++)
{
objExcel.excelWorksheet = (Worksheet)objExcel.excelSheets[j];
objExcel.excelWorksheet.Protect(pwd,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
objExcel.excelWorkbook.Protect(pwd, true, false);
}
objExcel.CloseFile(ExcelFileSaveAs);
objExcel.stopExcel();
}
return("");
}
public static string generate_excel_report(ref oExcel objExcel, System.Data.DataSet ds, string ExcelFileTmpl, string ExcelFileSaveAs, object sheetnm, string pwd)
{
ExcelFileTmpl = FixupPath(ExcelFileTmpl);
ExcelFileSaveAs = FixupPath(ExcelFileSaveAs);
if (objExcel == null)
{
objExcel = new oExcel();
if (ExcelFileTmpl == ExcelFileSaveAs)
{
objExcel.OpenFile(ExcelFileTmpl, "", false);
}
else
{
objExcel.OpenFile(ExcelFileTmpl, "");
}
}
try
{
objExcel.excelWorksheet = (Worksheet)objExcel.excelSheets[sheetnm];
int TblCount = ds.Tables.Count / 2;
System.Collections.Generic.List <oField>[] objFields = new System.Collections.Generic.List <oField> [TblCount];
for (int i = 0; i < TblCount; i++)
{
objFields[i] = new System.Collections.Generic.List <oField>();
}
int CmtCount = objExcel.excelWorksheet.Comments.Count;
for (int i = 0; i < CmtCount; i++)
{
Range objComment = (Range)objExcel.excelWorksheet.Comments[i + 1].Parent;
oField objField = new oField();
objField.Row = objComment.Row;
objField.Column = objComment.Column;
objField.FieldName = objExcel.excelWorksheet.Comments[i + 1].Shape.TextFrame.Characters(Type.Missing, Type.Missing).Text.Trim().Replace("\n", "");
for (int j = TblCount - 1; j >= 0; j--)
{
Range startRange = (Range)objExcel.excelWorksheet.get_Range(ds.Tables[j * 2].Rows[0][0].ToString(), Type.Missing);
Range endRange = (Range)objExcel.excelWorksheet.get_Range(ds.Tables[j * 2].Rows[0][1].ToString(), Type.Missing);
if (objField.Row >= startRange.Row && objField.Row <= endRange.Row &&
objField.Column >= startRange.Column && objField.Column <= endRange.Column)
{
objFields[j].Add(objField);
break;
}
}
}
for (int i = 0; i < CmtCount; i++)
{
objExcel.excelWorksheet.Comments[1].Delete();
}
DataView dv = new DataView(ds.Tables[1], "", "", DataViewRowState.OriginalRows);
System.Collections.Generic.List <Range> objPrints = new System.Collections.Generic.List <Range>();
objExcel.excelWorksheet.Copy(objExcel.excelWorksheet, Type.Missing);
objExcel.excelWorksheet2 = (Worksheet)objExcel.excelSheets[objExcel.excelWorksheet.Name + " (2)"];
generate_excel_report(objExcel, ds, dv, objFields, objPrints, 0);
objExcel.excelWorksheet2.Delete();
foreach (Range objPrint in objPrints)
{
Range objPrintBreak = (Range)objExcel.excelWorksheet.Cells[objPrint.Row + 1, objPrint.Column + 1];
objPrintBreak.PageBreak = (int)XlPageBreak.xlPageBreakManual;
}
//((Range)objExcel.excelWorksheet.Cells[((rowlast - rowstart) + 1) * row + rowstart, collast + 1]).PageBreak = (int)XlPageBreak.xlPageBreakManual;
}
catch (Exception e)
{
objExcel.CloseFile();
objExcel.stopExcel();
return(e.Message);
}
if (ExcelFileSaveAs != null)
{
if (pwd != null && pwd != "")
{
for (int j = 1; j <= objExcel.excelSheets.Count; j++)
{
objExcel.excelWorksheet = (Worksheet)objExcel.excelSheets[j];
objExcel.excelWorksheet.Protect(pwd,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
}
objExcel.excelWorkbook.Protect(pwd, true, false);
}
objExcel.CloseFile(ExcelFileSaveAs);
objExcel.stopExcel();
}
return("");
}
