Example #1
0
        public static string upload_excel(string ExcelFile, string savequery, DbConnection conn, int rowstart, int colstart, int colend, string pwd)
        {
            ExcelFile = FixupPath(ExcelFile);

            oExcel objExcel = new oExcel();

            objExcel.OpenFile(ExcelFile, "");


            try
            {
                if (pwd != null || pwd != "")
                {
                    objExcel.excelWorkbook.Unprotect(pwd);
                }

                int Field = colend - colstart + 1;
                objExcel.excelWorksheet = (Worksheet)objExcel.excelSheets[1];
                object[] objparam = new object[Field];
                for (int row = rowstart; ((Range)objExcel.excelWorksheet.Cells[row, colstart]).Value2 != null; row++)
                {
                    for (int Column = 0; Column < Field; Column++)
                    {
                        Range objCell = (Range)objExcel.excelWorksheet.Cells[row, Column + colstart];
                        objparam[Column] = objCell.Value2;
                        try
                        {
                            switch (objCell.Validation.Type)
                            {
                            case 3:
                                objparam[Column] = objparam[Column].ToString().Substring(0, objparam[Column].ToString().IndexOf(" - ")).Trim();
                                break;

                            case 4:
                                objparam[Column] = DateTime.FromOADate((double)objparam[Column]);
                                break;
                            }
                        }
                        catch
                        {
                        };
                    }
                    conn.ExecuteNonQuery(savequery, objparam, dbtimeout);
                }
            }
            catch (Exception e)
            {
                objExcel.CloseFile();
                objExcel.stopExcel();
                return(e.Message);
            }
            objExcel.CloseFile();
            objExcel.stopExcel();

            return("");
        }
Example #2
0
        public static string generate_excel(ref oExcel objExcel, System.Data.DataSet dtset, string ExcelFileTmpl, string ExcelFileSaveAs, string pwd)
        {
            System.Data.DataTable dtdata = dtset.Tables[0];

            int rowstart = 5, colreffstart = 210;
            int colstart = 1, colend = dtdata.Columns.Count;

            ExcelFileTmpl   = FixupPath(ExcelFileTmpl);
            ExcelFileSaveAs = FixupPath(ExcelFileSaveAs);

            if (objExcel == null)
            {
                objExcel = new oExcel();
                if (ExcelFileTmpl == ExcelFileSaveAs)
                {
                    objExcel.OpenFile(ExcelFileTmpl, "", false);
                }
                else
                {
                    objExcel.OpenFile(ExcelFileTmpl, "");
                }
            }
            try
            {
                if (dtdata.Rows.Count == 0)
                {
                    throw new Exception("no data");
                }

                objExcel.excelWorksheet = (Worksheet)objExcel.excelSheets[1];
                if (dtset.Tables.Count > 1)
                {
                    System.Data.DataTable dtreffcol = dtset.Tables[1];
                    for (int i = 0; i < dtset.Tables.Count - 2; i++)
                    {
                        System.Data.DataTable dtreff = dtset.Tables[i + 2];
                        for (int j = 0; j < dtreff.Rows.Count; j++)
                        {
                            Range objCell = (Range)objExcel.excelWorksheet.Cells[j + 2, colreffstart + i];
                            objCell.Value2 = dtreff.Rows[j][0].ToString() + " - " + dtreff.Rows[j][1].ToString();
                        }
                        Range objList = (Range)objExcel.excelWorksheet.get_Range(dtreffcol.Rows[0][i].ToString(), Type.Missing);
                        if (objList.Validation != null)
                        {
                            objList.Validation.Delete();
                        }
                        objList.Validation.Add(
                            XlDVType.xlValidateList,
                            XlDVAlertStyle.xlValidAlertStop,
                            XlFormatConditionOperator.xlBetween,
                            "=" + getcellnm(1, colreffstart + i, true, true) + ":" + getcellnm(dtreff.Rows.Count + 1, colreffstart + i, true, true),
                            Type.Missing);
                    }
                }

                Range objCopy  = (Range)objExcel.excelWorksheet.get_Range(getcellnm(rowstart, colstart), getcellnm(rowstart, colend));
                Range objPaste = (Range)objExcel.excelWorksheet.get_Range(getcellnm(rowstart, colstart), getcellnm(rowstart + dtdata.Rows.Count - 1, colend));
                objCopy.Copy(objPaste);

                for (int row = 0; row < dtdata.Rows.Count; row++)
                {
                    for (int Column = 0; Column < dtdata.Columns.Count; Column++)
                    {
                        if (dtdata.Rows[row][Column] is System.DBNull)
                        {
                            continue;
                        }
                        Range objCell = (Range)objExcel.excelWorksheet.Cells[row + rowstart, Column + colstart];
                        objCell.Value2 = dtdata.Rows[row][Column];
                    }
                }
            }
            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("");
        }
Example #3
0
        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("");
        }
Example #4
0
        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("");
        }
Example #5
0
        private static void generate_excel_report(oExcel objExcel, DataSet ds, DataView dv, System.Collections.Generic.List <oField>[] objFields, System.Collections.Generic.List <Range> objPrints, int tbl)
        {
            for (int row = dv.Count - 1; row >= 0; row--)
            {
                if (row != dv.Count - 1)
                {
                    Range objCopy = (Range)objExcel.excelWorksheet2.get_Range(ds.Tables[tbl * 2].Rows[0][0].ToString(), ds.Tables[tbl * 2].Rows[0][1].ToString());
                    objCopy.Copy(Type.Missing);
                    Range objPaste = (Range)objExcel.excelWorksheet.get_Range(ds.Tables[tbl * 2].Rows[0][0].ToString(), Type.Missing);
                    objPaste.Insert(XlInsertShiftDirection.xlShiftDown, Type.Missing);
                }

                if (tbl == 0)
                {
                    Range objPrint = (Range)objExcel.excelWorksheet.get_Range(ds.Tables[tbl * 2].Rows[0][1].ToString(), Type.Missing);
                    objPrints.Add(objPrint);
                }

                foreach (oField objField in objFields[tbl])
                {
                    Range objCell = (Range)objExcel.excelWorksheet.Cells[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];
                        }
                    }
                }
                if (tbl + 1 < ds.Tables.Count / 2)
                {
                    System.Data.DataTable dtrel = ds.Tables[(tbl + 1) * 2];
                    string filter = "";
                    string sort   = "";
                    for (int i = 2; i < dtrel.Columns.Count; i++)
                    {
                        if (dtrel.Rows[0][i].ToString().ToUpper().StartsWith("ORDER BY"))
                        {
                            sort = dtrel.Rows[0][i].ToString();
                        }
                        else
                        {
                            if (filter != "")
                            {
                                filter += " AND ";
                            }
                            filter += dtrel.Rows[0][i].ToString() + " = " + staticFramework.toSql(dv[row][dtrel.Rows[0][i].ToString()]);
                        }
                    }

                    DataView dvchild = new DataView(ds.Tables[(tbl + 1) * 2 + 1], filter, sort, DataViewRowState.OriginalRows);
                    generate_excel_report(objExcel, ds, dvchild, objFields, objPrints, tbl + 1);
                }
            }
        }
Example #6
0
        public static string generate_excel_report(ref oExcel objExcel, System.Data.DataTable dtdata, string ExcelFileTmpl, string ExcelFileSaveAs, object sheetnm, string pwd)
        {
            DataView dv = new DataView(dtdata);

            return(generate_excel_report(ref objExcel, dv, ExcelFileTmpl, ExcelFileSaveAs, sheetnm, pwd));
        }