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(""); }