Esempio n. 1
0
        public static void CloseExcelApp()
        {
            CloseExcelWorkbook();

            if (ExcelApp == null)
            {
                return;
            }

            /*var LocalByNameApp = Process.GetProcessesByName("EXCEL");
             * if (LocalByNameApp.Length > 0)
             * {
             *  foreach (var App in LocalByNameApp)
             *  {
             *      if (!App.HasExited)
             *      {
             *          App.Kill();
             *      }
             *  }
             * }*/
            GetWindowThreadProcessId(new IntPtr(ExcelApp.Hwnd), out int ProcessId);
            Process.GetProcessById(ProcessId).Kill();

            ExcelApp.Workbooks.Close();
            Marshal.ReleaseComObject(ExcelApp);
            ExcelApp.Quit();
            ExcelApp = null;
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
Esempio n. 2
0
        /// <summary>
        ///     Close Excel file and release all Excel objects
        /// </summary>
        public void CloseFile()
        {
            try
            {
                if (Worksheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(Worksheet);
                }
                Sheet?.Close(false);
                Books?.Close();
                if (Sheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(Sheet);
                }
                if (Books != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(Books);
                    Books = null;
                }
            }
            finally
            {
                ExcelApp?.Quit();
                if (ExcelApp != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
                    ExcelApp = null;
                }
            }

            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
Esempio n. 3
0
        public override void Print(IOfficeFilePropties properties)
        {
            if (properties == null)
            {
                throw new ArgumentNullException(nameof(properties));
            }
            var p = properties as ExcelFileProperties;

            if (p == null)
            {
                throw new InvalidOperationException("You have to supply valid Excel File Properties");
            }
            try
            {
                CurrentExcelSheet = GetExcelSheet(p.Path);
                TableToExcelSheet(p.Source, CurrentExcelSheet, p.StartRowIndex, p.StartcolumnIndex);
                if (p.PrintDirectly)
                {
                    CurrentExcelSheet.PrintOut();
                    ExcelApp.DisplayAlerts = false;
                    ExcelApp.Quit();
                    ReleaseResources(ExcelApp, ExcelWorkbooks, ExcelWorkbook, CurrentExcelSheet);
                    return;
                }
                ExcelApp.Visible = true;
                //ReleaseResources(excelApp, excelWorkbooks, excelWorkbook, excelSheet);
            }
            catch
            {
                ForceExcleToQuit(ExcelApp);
                ReleaseResources(ExcelApp, ExcelWorkbooks, ExcelWorkbook, CurrentExcelSheet);
                throw;
            }
        }
Esempio n. 4
0
        public virtual void Print()
        {
            try
            {
                //Insert the DataGridView into the excel spreadsheet
                TableToExcelSheet(Data, ExcelSheet, ExcelProperties.StartRow, ExcelProperties.StartColumn);
                //if visible , then exit so user can see it, otherwise save and exit

                if (ExcelProperties.PrintDirectely)
                {
                    //bool okPrint = objExcel.Dialogs[XlBuiltInDialog.xlDialogPrint].Show();
                    // if (!okPrint)
                    //{
                    //    objWorkbook.Close(false);
                    //    return;
                    //}
                    ExcelSheet.PrintOut();
                    //excelWorkbook.Close(false);
                    ExcelApp.DisplayAlerts = false;
                    ExcelApp.Quit();
                    Dispose();
                    return;
                }
                ExcelApp.Visible = true;
                Dispose();
            }
            catch (Exception)
            {
                ForceExcleToQuit(ExcelApp);
                Dispose();
                throw;
            }
        }
Esempio n. 5
0
        public void Dispose()
        {
            ExcelDocument?.Close(false, "", false);
            ExcelApp?.Quit();

            if (ExcelApp != null)
            {
                while (Marshal.ReleaseComObject(ExcelApp) != 0)
                {
                }
            }
            if (ExcelDocument != null)
            {
                while (Marshal.ReleaseComObject(ExcelDocument) != 0)
                {
                }
            }

            ExcelApp      = null;
            ExcelDocument = null;


            GC.Collect();
            GC.WaitForPendingFinalizers();
        }
Esempio n. 6
0
        /// <summary>
        /// 释放资源
        /// </summary>
        public void Quit()
        {
            if (WorkBook != null)
            {
                WorkBook.Close(null, null, null);
            }
            if (ExcelApp != null)
            {
                ExcelApp.Workbooks.Close();
                ExcelApp.Quit();
            }
            if (Range != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(Range);
                Range = null;
            }

            if (WorkSheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkSheet);
                WorkSheet = null;
            }
            if (WorkBook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(WorkBook);
                WorkBook = null;
            }
            if (ExcelApp != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelApp);
                ExcelApp = null;
            }
            GC.Collect();
        }
Esempio n. 7
0
 public void Close()
 {
     if (ExcelWorkBook != null)
     {
         ExcelWorkBook.Close();
     }
     if (ExcelApp != null)
     {
         ExcelApp.Quit();
     }
 }
Esempio n. 8
0
 public void Open(string filePath)
 {
     Book            = ExcelApp.Workbooks.Open(filePath);
     mainSheet.Sheet = Book.Sheets[1];
     mainSheet.ReadData();
     FileVM.filePath = filePath;
     Book.Close(true);
     ExcelApp.Quit();
     Clear();
     GC.Collect();
     //добавить окно, которое спрашивало бы "имеются несохраненные данные, сохранить?"
 }
Esempio n. 9
0
 public void closeApp()
 {
     Workbook.Close();
     ExcelApp.Quit();
     templateName      = null;
     excelApp          = null;
     worksheetSettings = null;
     worksheetNames    = null;
     worksheetWishes   = null;
     workbook          = null;
     GC.Collect();
 }
Esempio n. 10
0
 public void Save()
 {
     if (FileVM.filePath != "")
     {
         File.Delete(FileVM.filePath);
         Book.SaveAs(FileVM.filePath, XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                     false, false, XlSaveAsAccessMode.xlExclusive,
                     Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
         Book.Close(true);
         ExcelApp.Quit();
         Clear();
         GC.Collect();
         App.SaveNotificationWindowVM.IsSaved = true;
     }
 }
Esempio n. 11
0
    /// <summary>
    /// 模板格式下载,用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key)
    /// </summary>
    /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param>
    /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param>
    /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param>
    /// <param name="items">标签的键、值对</param>
    /// <returns>返回下载文件的链接地址(使用download.aspx)</returns>
    public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items)
    {
        ExcelApp       excelApp = null;
        ExcelWorkbook  workbook = null;
        ExcelWorksheet sheet    = null;

        string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
        string filePath = DownloadFolder + fileName;

        try
        {
            excelApp = new ExcelApp();
            workbook = excelApp.Open(template, 0);
            workbook.SaveAs(filePath);
            sheet = workbook.Worksheets(1);
            ExcelRange range = sheet.Cells();
            foreach (KeyValuePair <string, string> kv in items)
            {
                range.Replace(kv.Key, kv.Value, false);
            }
            workbook.Save();
        }
        catch (Exception er)
        {
            throw er;
        }
        finally
        {
            if (sheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
            }
            if (workbook != null)
            {
                workbook.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
            }
            if (excelApp != null)
            {
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
            }
        }

        return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
    }
Esempio n. 12
0
 public void Close()
 {
     if (app != null)
     {
         if (app.Worksheets.Count > 0)
         {
             app.Workbooks.Close();
         }
         app.Quit();
         app = null;
     }
     Process[] procs = Process.GetProcessesByName("excel");
     foreach (Process pro in procs)
     {
         pro.Kill();//没有更好的方法,只有杀掉进程
     }
 }
Esempio n. 13
0
        public static void SaveAsLNSchedule()
        {
            Console.Clear();
            try
            {
                clsConsole.WriteLine("\r\n Complete saving as Schedule ...", ConsoleColor.Red);
                WorkbookExcel.SaveAs(LNSchedule, SaveAsExcelFormat, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
            }
            catch (Exception)
            {
                clsConsole.WriteLine("\r\n Complete saving Copy of Schedule ...", ConsoleColor.Red);
                WorkbookExcel.SaveAs(CopyofLNSchedule, SaveAsExcelFormat, missing, missing, missing, missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing);
                ScheduleCopyExisted = true;
            }

            WorkbookExcel.Close();

            ExcelApp.Quit();
        }
Esempio n. 14
0
        /// <summary>
        /// Closes the file and excel and returns true on success
        /// </summary>
        /// <returns>True if excel closed successfully</returns>
        public bool CloseFile()
        {
            //if(!CanUse) return false;

            try
            {
                SimpleLog.Info($"Closing Excel file \"{Workbook.FullName}\".");
                Workbook?.Close();
                ExcelApp?.Quit();
                Dispose();
                SimpleLog.Info("Excel closed.");
                return(true);
            }
            catch (Exception e)
            {
                SimpleLog.Log("Failed to close excel.");
                SimpleLog.Error(e.ToString());
            }
            return(false);
        }
Esempio n. 15
0
 public void SaveAs(string directoryPath)
 {
     try
     {
         if (!directoryPath.Contains(".xlsx"))
         {
             FileVM.filePath = directoryPath;
             Book.SaveAs(directoryPath);
             Book.Close(false, false, false);
             ExcelApp.Quit();
             Clear();
             GC.Collect();
             App.SaveNotificationWindowVM.IsSaved = true;
         }
         else
         {
             FileVM.filePath = directoryPath;
             Save();
         }
     }
     catch (Exception)
     { }
 }
Esempio n. 16
0
    /// <summary>
    /// 模板格式+简单格式下载,先用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key),再下载<paramref name="ds"/>的数据行
    /// </summary>
    /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param>
    /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param>
    /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param>
    /// <param name="items">标签的键、值对</param>
    /// <param name="rowIndex">数据行的开始位置(1开始的索引,即Excel中的行号)</param>
    /// <param name="ds"></param>
    /// <returns>返回下载文件的链接地址(使用download.aspx)</returns>
    public static string DownloadXls(string name, string prefix, string template, IDictionary<string, string> items, int rowIndex, IList<DownloadFormat> format, DataSet ds)
    {
        ExcelApp excelApp = null;
        ExcelWorkbook workbook = null;
        ExcelWorksheet sheet = null;
        string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
        string filePath = DownloadFolder + fileName;

        try
        {
            excelApp = new ExcelApp();
            workbook = excelApp.Open(template, 0);
            workbook.SaveAs(filePath);
            sheet = workbook.Worksheets(1);

            //标签替换
            ExcelRange range = sheet.Cells();
            if (items != null)
                foreach (KeyValuePair<string, string> kv in items)
                    range.Replace(kv.Key, kv.Value, false);

            //数据行
            int index = rowIndex;
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                for (int i = 0; i < format.Count; i++)
                {
                    DownloadFormat ft = format[i];
                    if (ft.ColumnIndex == null)
                        continue;
                    for (int j = 0; j < ft.ColumnIndex.Length; j++)
                        SetCellValue(sheet.Cells(index, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
                }
                index++;
            }

            workbook.Save();
        }
        catch (Exception er)
        {
            throw er;
        }
        finally
        {
            if (sheet != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
            if (workbook != null)
            {
                workbook.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
            }
            if (excelApp != null)
            {
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
            }
        }

        return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath);
    }
Esempio n. 17
0
    /// <summary>
    /// 简单格式下载,只下载<paramref name="ds"/>的数据行
    /// </summary>
    /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param>
    /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param>
    /// <param name="format">数据列的格式描述信息</param>
    /// <param name="ds"></param>
    /// <returns>返回下载文件的链接地址(使用download.aspx)</returns>
    public static string DownloadXls(string name, string prefix, IList<DownloadFormat> format, DataSet ds)
    {
        string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
        string filePath = DownloadFolder + fileName;

        ExcelApp excelapp = null;
        ExcelWorkbook excelBook = null;
        ExcelWorksheet excelSheet = null;
        try
        {
            excelapp = new ExcelApp();
            excelapp.DisplayAlerts = false;
            excelBook = excelapp.NewWorkBook();
            excelSheet = excelBook.Worksheets(1);
            int rowIndex = 1;

            for (int i = 0; i < format.Count; i++)
                excelSheet.Cells(rowIndex, i + 1).Value = format[i].Title;
            ExcelRange rg = excelSheet.Range(rowIndex, rowIndex, 1, format.Count);
            rg.SelectRange();
            rg.Font.Bold = true;
            rg.HorizontalAlignment = 3;
            rg.Interior.SetColor(221, 221, 221);
            rowIndex++;

            #region 写文件
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                for (int i = 0; i < format.Count; i++)
                {
                    DownloadFormat ft = format[i];
                    if (ft.ColumnIndex == null)
                        continue;
                    for (int j = 0; j < ft.ColumnIndex.Length; j++)
                        SetCellValue(excelSheet.Cells(rowIndex, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
                }
                rowIndex++;
            }
            #endregion

            ExcelRange excelRange = excelSheet.Cells();
            excelRange.SelectRange();
            excelRange.AutoFit();
            excelRange.Font.Size = 10;
            excelBook.SaveAs(filePath);
        }
        catch (Exception er)
        {
            throw er;
        }
        finally
        {
            if (excelSheet != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
            if (excelBook != null)
            {
                excelBook.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
            }
            if (excelapp != null)
            {
                excelapp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
            }
        }

        return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath);
    }
Esempio n. 18
0
        public static void Main(string[] args)
        {
            Application
                ExcelApp = null;

            Workbook
                Workbook = null;

            Sheets
                Sheets = null,
                Charts = null;

            Worksheet
                Sheet = null;

            Chart
                Chart = null;

            Series
                Series = null;

            Axes
                Axes = null;

            Axis
                Axis = null;

            Range
                Range  = null,
                Range2 = null;

            object
                tmpObject;

            try
            {
                try
                {
                    string
                        CurrentDirectory = System.IO.Directory.GetCurrentDirectory();

                    CurrentDirectory = CurrentDirectory.Substring(0, CurrentDirectory.LastIndexOf("bin", CurrentDirectory.Length - 1));

                    string
                    //InputFileName = CurrentDirectory + "test.xls",
                        InputFileName = "d:\\result.xlsx",
                        OutputDbf     = CurrentDirectory + "xls2dbf.dbf";

                    try
                    {
                        ExcelApp = (Application)Marshal.GetActiveObject("Excel.Application");
                    }
                    catch (COMException eException)
                    {
                        if (eException.ErrorCode == -2147221021)
                        {
                            ExcelApp = new Application();
                        }
                    }

                    ExcelApp.Visible       = true;
                    ExcelApp.DisplayAlerts = false;
                    ExcelApp.UserControl   = false;

                                        #if TEST_DATE
                    Workbook = ExcelApp.Workbooks.Add(Type.Missing);
                    Sheet    = (Worksheet)Workbook.ActiveSheet;
                    Sheet.Columns.get_Range("A1", "A1").ColumnWidth = 3;
                    Sheet.get_Range("A1", Type.Missing).Value       = "01.01.2001";
                    tmpObject = Sheet.get_Range("A1", Type.Missing).Value;
                    Workbook.Close(Type.Missing, Type.Missing, Type.Missing);
                                        #endif

                    Workbook = ExcelApp.Workbooks.Open(InputFileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                        #if TEST_SAVE_AS
                    if (File.Exists(OutputDbf))
                    {
                        File.Delete(OutputDbf);
                    }
                    Workbook.SaveAs(OutputDbf, XlFileFormat.xlDBF4, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                                        #endif
                    Sheets = Workbook.Worksheets;
                    Sheet  = (Worksheet)Workbook.ActiveSheet;

                    double
                        points = ExcelApp.CentimetersToPoints(1.93d);

                    points = ExcelApp.CentimetersToPoints(2.70d);
                    points = ExcelApp.CentimetersToPoints(3.55d);

                    int
                        MaxRow = Sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Row,
                        MaxCol = Sheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell, Type.Missing).Column;

                    Range = Sheet.get_Range("A1", Type.Missing);
                    Range = Range.get_End(XlDirection.xlToRight);
                    Range = Range.get_End(XlDirection.xlDown);

                    string
                        CurrAddress = Range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

                    Range = Sheet.get_Range("A1", CurrAddress);

                    CurrAddress = ColNoToName(MaxCol) + Convert.ToString(MaxRow);
                    Range       = Sheet.get_Range("A1", CurrAddress);

                    object[,]
                    values = (object[, ])Range.Value2;

                    int
                        MaxI = values.GetLength(0),                       // Row
                        MaxJ = values.GetLength(1);                       // Col

                    string
                        tmpString = string.Empty;

                    for (int i = 1; i <= MaxI; ++i)                // Row
                    {
                        for (int j = 1; j <= MaxJ; ++j)            // Col
                        {
                            tmpString += "[" + i + "," + j + "]=" + (values[i, j] != null ? values[i, j] : "null");
                            if (j <= MaxJ - 1)
                            {
                                tmpString += "\t";
                            }
                        }
                        tmpString += Environment.NewLine;
                    }
                    Console.WriteLine(tmpString);

                    Range = Sheet.get_Range("A10", "E18");
                    //Range.Formula=values;
                    Range.Value = values;

                    Range     = Sheet.get_Range(Sheet.Cells[1, 1], Sheet.Cells[10, 10]);
                    tmpObject = ((Range)Range.get_Item(1, 1)).Value;

                    Sheet.get_Range("A23", Type.Missing).Value = "A23";
                    tmpString = Convert.ToString(Sheet.get_Range("A23", Type.Missing).Value);
                    Sheet.get_Range("A24", "C24").Merge(true);

                    for (int _i = 1; _i <= 10; ++_i)
                    {
                        Sheet.get_Range("F" + _i, Type.Missing).Value = _i;
                    }
                    Sheet.get_Range("F11", Type.Missing).Formula     = "=яслл(F1:F10)";
                    Sheet.get_Range("F11", Type.Missing).Orientation = 30;
                    Sheet.get_Range("F11", Type.Missing).Font.Bold   = true;
                    Sheet.get_Range("F11", Type.Missing).Font.Size   = 25;

                    Range = Sheet.get_Range("F2", Type.Missing);

                    tmpObject = -4121;                   // 0xFFFFEFE7; /* xlShiftDown */
                    Range.Rows.Insert(tmpObject);

                    Range.Rows.Insert(Type.Missing);
                    Sheet.get_Range("F2", Type.Missing).Value = 999;

                    Sheet = (Worksheet)Sheets.get_Item(2);
                    Sheet.Activate();

                    string
                        CellDirect,
                        CellObject;

                    #region bool
                    CellDirect = "E1";
                    CellObject = "E2";
                    tmpString  = "bool";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F1";
                    CellObject = "F2";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = true;

                    bool
                        tmpBool = true;

                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpBool;
                    #endregion

                    #region byte
                    CellDirect = "E3";
                    CellObject = "E4";
                    tmpString  = "byte";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F3";
                    CellObject = "F4";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = 255;

                    byte
                        tmpByte = 255;

                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpByte;
                    #endregion

                    #region sbyte
                    CellDirect = "E5";
                    CellObject = "E6";
                    tmpString  = "sbyte";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F5";
                    CellObject = "F6";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = 127;

                    sbyte
                        tmpSByte = 127;

                    // !!!
                    Sheet.get_Range(CellObject, Type.Missing).Value = (int)tmpSByte;
                    #endregion

                    #region short
                    CellDirect = "E7";
                    CellObject = "E8";
                    tmpString  = "short";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F7";
                    CellObject = "F8";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = 32767;

                    short
                        tmpShort = 32767;

                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpShort;
                    #endregion

                    #region ushort
                    CellDirect = "E9";
                    CellObject = "E10";
                    tmpString  = "ushort";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F9";
                    CellObject = "F10";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = 65535;

                    ushort
                        tmpUShort = 65535;

                    // !!!
                    Sheet.get_Range(CellObject, Type.Missing).Value = (int)tmpUShort;
                    #endregion

                    #region int
                    CellDirect = "E11";
                    CellObject = "E12";
                    tmpString  = "int";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F11";
                    CellObject = "F12";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = 2147483647;

                    int
                        tmpInt = 2147483647;

                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpInt;
                    #endregion

                    #region uint
                    CellDirect = "E13";
                    CellObject = "E14";
                    tmpString  = "uint";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F13";
                    CellObject = "F14";
                    // !!!
                    Sheet.get_Range(CellDirect, Type.Missing).Value = (double)4294967295U;

                    uint
                        tmpUInt = 4294967295U;

                    // !!!
                    Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpUInt;
                    #endregion

                    #region long
                    CellDirect = "E15";
                    CellObject = "E16";
                    tmpString  = "long";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F15";
                    CellObject = "F16";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = (double)999999999999999L;
                    //Sheet.get_Range(CellDirect,Type.Missing).Value=9223372036854775807L;

                    long
                        tmpLong = 999999999999999L;                       //9223372036854775807L;

                    Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpLong;
                    #endregion

                    #region ulong
                    CellDirect = "E17";
                    CellObject = "E18";
                    tmpString  = "ulong";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F17";
                    CellObject = "F18";
                    //Sheet.get_Range(CellDirect,Type.Missing).Value=18446744073709551615UL;

                    ulong
                        tmpULong = 18446744073709551615UL;

                    //Sheet.get_Range(CellObject,Type.Missing).Value=tmpULong;
                    #endregion

                    #region DateTime
                    CellDirect = "E19";
                    CellObject = "E20";
                    tmpString  = "DateTime";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F19";
                    CellObject = "F20";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = DateTime.Now;

                    DateTime
                        tmpDateTime = DateTime.Now;

                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpDateTime;
                    #endregion

                    #region TimeSpan
                    CellDirect = "E21";
                    CellObject = "E22";
                    tmpString  = "TimeSpan";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect  = "F21";
                    CellObject  = "F22";
                    tmpDateTime = new DateTime(0L);
                    // !!!
                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpDateTime + (new TimeSpan(1, 2, 3));

                    TimeSpan
                        tmpTimeSpan = new TimeSpan(1, 2, 3);

                    // !!!
                    tmpDateTime  = new DateTime(0L);
                    tmpDateTime += tmpTimeSpan;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpDateTime;
                    #endregion

                    #region float
                    CellDirect = "E23";
                    CellObject = "E24";
                    tmpString  = "float";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F23";
                    CellObject = "F24";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = 9999.9999F;

                    float
                        tmpFloat = 9999.9999F;

                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpFloat;
                    #endregion

                    #region double
                    CellDirect = "E25";
                    CellObject = "E26";
                    tmpString  = "double";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F25";
                    CellObject = "F26";
                    Sheet.get_Range(CellDirect, Type.Missing).Value = 1.7E+3;

                    double
                        tmpDouble = 1.7E+3;

                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpDouble;
                    #endregion

                    #region decimal
                    CellDirect = "E27";
                    CellObject = "E28";
                    tmpString  = "decimal";

                    Sheet.get_Range(CellDirect, Type.Missing).Value = tmpString;
                    Sheet.get_Range(CellObject, Type.Missing).Value = tmpString;

                    CellDirect = "F27";
                    CellObject = "F28";
                    // !!!
                    Sheet.get_Range(CellDirect, Type.Missing).Value = (double)999999999.99m;

                    decimal
                        tmpDecimal = 999999999.99m;

                    // !!!
                    Sheet.get_Range(CellObject, Type.Missing).Value = (double)tmpDecimal;
                    #endregion

                    Sheet = (Worksheet)Sheets.get_Item(3);

                    Range       = Sheet.get_Range("A1", "A5");
                    Range.Value = "Some Value";
                    Range2      = Sheet.get_Range("A11", "A15");
                    Range.Copy(Range2);

                    Range       = Sheet.get_Range("B1", "B5");
                    Range.Value = "Some Value";
                    Range.Copy(Type.Missing);
                    Range2 = Sheet.get_Range("B11", "B15");
                    Sheet.Paste(Range2, Type.Missing);

                    Range2 = Sheet.get_Range("B20", Type.Missing);
                    Sheet.Activate();
                    Range2.Select();
                    Sheet.Paste(Type.Missing, Type.Missing);


                                        #if TEST_CHART
                    Sheet = (Excel.Worksheet)Sheets.get_Item(3);
                    Sheet.Activate();

                    for (int Col = 1; Col <= 20; ++Col)
                    {
                        Sheet.get_Range("A" + Col, Type.Missing).Value   = Col;
                        Sheet.get_Range("B" + Col, Type.Missing).Formula = "=sin(A" + Col + ")";
                        Sheet.get_Range("C" + Col, Type.Missing).Formula = "=cos(A" + Col + ")";
                        Sheet.get_Range("D" + Col, Type.Missing).Formula = "=B" + Col + "+C" + Col;
                    }

                    Sheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    Charts = ExcelApp.Charts;
                    Chart  = (Excel.Chart)Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    //Chart.Type=65; // xlLineMarkers
                    Chart.Type = 4;                   // xlLine
                    Chart.SetSourceData(Sheet.get_Range("B1", "C20"), 2 /*xlColumns*/);
                    Chart  = Chart.Location(XlChartLocation.xlLocationAsObject, "кХЯР4");
                    Series = ((Excel.SeriesCollection)Chart.SeriesCollection(Type.Missing)).NewSeries();
                    MaxI   = ((Excel.SeriesCollection)Chart.SeriesCollection(Type.Missing)).Count;
                    Range  = Sheet.get_Range("A1", "A20");
                    for (int i = MaxI; i > 0; --i)
                    {
                        ((Excel.Series)Chart.SeriesCollection(i)).XValues = Range;
                        switch (i)
                        {
                        case 1:
                        {
                            ((Excel.Series)Chart.SeriesCollection(i)).Name = "sin";
                            break;
                        }

                        case 2:
                        {
                            ((Excel.Series)Chart.SeriesCollection(i)).Name = "cos";
                            break;
                        }
                        }
                    }

                    Range         = Sheet.get_Range("D1", "D20");
                    Series.Values = Range;
                    Series.Name   = "sin+cos";

                    Chart.HasTitle           = true;
                    Chart.ChartTitle.Caption = "Name of Chart";
                    Axis               = (Excel.Axis)Chart.Axes(1 /*xlCategory*/, XlAxisGroup.xlPrimary);
                    Axis.HasTitle      = true;
                    Axis.CategoryNames = "Name of Category";
                    Axis               = (Excel.Axis)Chart.Axes(2 /*xlValue*/, XlAxisGroup.xlPrimary);
                    Axis.HasTitle      = true;

                    Chart.HasLegend       = true;
                    Chart.Legend.Position = XlLegendPosition.xlLegendPositionBottom;
                                        #endif

                    ExcelApp.Quit();
                }
                catch (COMException eException)
                {
                    string
                        tmp = eException.GetType().FullName + Environment.NewLine + "ErrorCode: " + eException.ErrorCode + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;

                    Console.WriteLine(tmp);
                }
                catch (ArgumentException eException)
                {
                    string
                        tmp = eException.GetType().FullName + Environment.NewLine + "ParamName: " + eException.ParamName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;

                    Console.WriteLine(tmp);
                }
                catch (Exception eException)
                {
                    string
                        tmp = eException.GetType().FullName + Environment.NewLine + "Message: " + eException.Message + Environment.NewLine + "StackTrace:" + Environment.NewLine + eException.StackTrace;

                    Console.WriteLine(tmp);
                }
            }
            finally
            {
                if (Axes != null)
                {
                    Marshal.ReleaseComObject(Axes);
                    Axes = null;
                }
                if (Axis != null)
                {
                    Marshal.ReleaseComObject(Axis);
                    Axis = null;
                }
                if (Series != null)
                {
                    Marshal.ReleaseComObject(Series);
                    Series = null;
                }
                if (Chart != null)
                {
                    Marshal.ReleaseComObject(Chart);
                    Chart = null;
                }
                if (Charts != null)
                {
                    Marshal.ReleaseComObject(Charts);
                    Charts = null;
                }
                if (Range2 != null)
                {
                    Marshal.ReleaseComObject(Range2);
                    Range2 = null;
                }
                if (Range != null)
                {
                    Marshal.ReleaseComObject(Range);
                    Range = null;
                }
                if (Sheets != null)
                {
                    Marshal.ReleaseComObject(Sheets);
                    Sheets = null;
                }
                if (Sheet != null)
                {
                    Marshal.ReleaseComObject(Sheet);
                    Sheet = null;
                }
                if (Workbook != null)
                {
                    Marshal.ReleaseComObject(Workbook);
                    Workbook = null;
                }
                if (ExcelApp != null)
                {
                    ExcelApp.Quit();
                    Marshal.ReleaseComObject(ExcelApp);
                    ExcelApp = null;
                }
                //GC.Collect();
                GC.GetTotalMemory(true);
            }
        }
Esempio n. 19
0
    /// <summary>
    /// 模板格式下载,用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key)
    /// </summary>
    /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param>
    /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param>
    /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param>
    /// <param name="items">标签的键、值对</param>
    /// <returns>返回下载文件的链接地址(使用download.aspx)</returns>
    public static string DownloadXls(string name, string prefix, string template, IDictionary<string, string> items)
    {
        ExcelApp excelApp = null;
        ExcelWorkbook workbook = null;
        ExcelWorksheet sheet = null;

        string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
        string filePath = DownloadFolder + fileName;
        try
        {
            excelApp = new ExcelApp();
            workbook = excelApp.Open(template, 0);
            workbook.SaveAs(filePath);
            sheet = workbook.Worksheets(1);
            ExcelRange range = sheet.Cells();
            foreach (KeyValuePair<string, string> kv in items)
                range.Replace(kv.Key, kv.Value, false);
            workbook.Save();
        }
        catch (Exception er)
        {
            throw er;
        }
        finally
        {
            if (sheet != null)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
            if (workbook != null)
            {
                workbook.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
            }
            if (excelApp != null)
            {
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
            }
        }

        return "/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath);
    }
Esempio n. 20
0
 private void LandingForm_FormClosing(object sender, FormClosingEventArgs e)
 {
     ExcelApp.Quit();
 }
Esempio n. 21
0
        public static string GetExcelDocumentSet(SetViewModel obj, string filePath, int setType)
        {
            Excel.Application ExcelApp;
            Excel.Worksheet   ExcelSheet;
            Excel.Workbook    ExcelWorkbook;
            Excel.Workbooks   ExcelWorkbooks;
            Excel.Range       ExcelRange;
            int     rowsCount;
            int     columnsCount;
            dynamic data;

            ExcelApp                = CreateExcelObj();
            ExcelWorkbooks          = ExcelApp.Workbooks;
            ExcelApp.ScreenUpdating = false;
            ExcelApp.DisplayAlerts  = false;
            ExcelWorkbook           = ExcelWorkbooks.Add();

            try
            {
                if (String.IsNullOrEmpty(filePath))
                {
                    filePath = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)).FullName;
                    //if (Environment.OSVersion.Version.Major >= 6)
                    //{
                    //    filePath = Directory.GetParent(filePath).FullName;
                    //}
                }
                filename = obj.Set.First().Project + " - Сет " + obj.Set.First().Set + " - " + obj.Set.First().TestMethod + ".xlsx";



                switch (setType)
                {
                case 1:

                    foreach (var itemSet in obj.Set)
                    {
                        ExcelSheet = ExcelWorkbook.Sheets.Add();

                        rowsCount    = itemSet.MOList.Count + 8 + itemSet.ControlMOList.Count + 1;
                        columnsCount = itemSet.MICList.Count + 5;

                        ExcelRange =
                            ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowsCount, columnsCount]];
                        if (
                            itemSet.AB.Length > 30)
                        {
                            ExcelSheet.Name = itemSet.AB.Substring(0, 30).Replace("/", "|").Replace("\\", "|");
                        }
                        else
                        {
                            ExcelSheet.Name = itemSet.AB.Replace("/", "|").Replace("\\", "|");
                        }

                        data = PrepareListForSet1(itemSet);

                        ExcelRange.Value = data;
                        FormatSheetForSet1(ExcelSheet, itemSet);

                        Marshal.ReleaseComObject(ExcelRange);
                        Marshal.ReleaseComObject(ExcelSheet);
                    }
                    break;

                case 2:
                    ExcelSheet   = ExcelWorkbook.Sheets.Add();
                    rowsCount    = obj.Set.First().MOList.Count + obj.Set.First().ControlMOList.Count + 3;
                    columnsCount = obj.Set.Count + 3;

                    ExcelRange = ExcelSheet.Range[ExcelSheet.Cells[1, 1], ExcelSheet.Cells[rowsCount, columnsCount]];

                    ExcelSheet.Name = obj.Set.First().Project + " - Сет № " + obj.Set.First().Set;

                    data = PrepareListForSet2(obj);

                    ExcelRange.Value = data;
                    FormatSheetForSet2(ExcelSheet, obj);
                    Marshal.ReleaseComObject(ExcelRange);
                    Marshal.ReleaseComObject(ExcelSheet);
                    break;

                default:
                    break;
                }
                ExcelWorkbook.SaveAs();
                ExcelWorkbook.SaveAs(filePath + "\\" + filename, Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing);


                //while (Marshal.ReleaseComObject(ExcelWorkbook) > 0)
                //{ }
                //while (Marshal.ReleaseComObject(ExcelWorkbooks) > 0)
                //{ }


                //ExcelApp.Quit();

                //while (Marshal.ReleaseComObject(ExcelApp) > 0)
                //{ }

                return(filePath + "\\" + filename);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                while (Marshal.ReleaseComObject(ExcelWorkbook) > 0)
                {
                }
                while (Marshal.ReleaseComObject(ExcelWorkbooks) > 0)
                {
                }


                ExcelApp.Quit();

                while (Marshal.ReleaseComObject(ExcelApp) > 0)
                {
                }
                GC.Collect();
            }
        }
    protected void MagicItemCommand(object sender, MagicItemEventArgs e)
    {
        if (e.CommandName == "Save")
        {
            if (this.FileUpload1.FileName.Trim().Length <= 0)
            {
                WebUtil.ShowMsg(this, "请选择盘点结果文件");
                return;
            }
            string fileName = this.FileUpload1.FileName;
            if (!fileName.EndsWith(".xls"))
            {
                WebUtil.ShowMsg(this, "请选择有效的Excel文件");
                return;
            }
            string filePath = System.IO.Path.Combine(DownloadUtil.DownloadFolder, "CK_IMP_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xls");
            this.FileUpload1.SaveAs(filePath);
            IList<INVCheckLine> lines = new List<INVCheckLine>();

            #region 读取文件
            ExcelApp excelapp = null;
            ExcelWorkbook excelBook = null;
            ExcelWorksheet excelSheet = null;
            try
            {
                excelapp = new ExcelApp();
                excelapp.DisplayAlerts = false;
                excelBook = excelapp.Open(filePath, 0);
                excelSheet = excelBook.Worksheets(1);
                int rowIndex = 2;
                string lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
                decimal qty;
                while (lineNum.Length==4)
                {
                    qty = Cast.Decimal(excelSheet.Range(rowIndex, rowIndex, 9, 9).Value, 0M);
                    INVCheckLine line = new INVCheckLine();
                    line.LineNumber = lineNum;
                    line.CurrentQty = qty;
                    lines.Add(line);
                    rowIndex++;
                    lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
                }
            }
            catch (Exception er)
            {
                WebUtil.ShowError(this, er.Message);
                return;
            }
            finally
            {
                if (excelSheet != null)
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
                if (excelBook != null)
                {
                    excelBook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
                }
                if (excelapp != null)
                {
                    excelapp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
                }
            }
            #endregion

            using (ISession session = new Session())
            {
                try
                {
                    INVCheckHead head = INVCheckHead.Retrieve(session, WebUtil.Param("ordNum"));
                    if (head == null)
                    {
                        WebUtil.ShowError(this, "盘点单"+WebUtil.Param("ordNum")+"不存在");
                        return;
                    }
                    session.BeginTransaction();
                    head.ClearCheckQty(session);
                    head.UpdateLines(session, lines);
                    session.Commit();
                }
                catch (Exception er)
                {
                    session.Rollback();
                    WebUtil.ShowError(this, er.Message);
                    return;
                }
            }

            this.Response.Redirect(WebUtil.Param("return"));
        }
    }
Esempio n. 23
0
    /// <summary>
    /// 模板格式+简单格式下载,先用<paramref name="items"/>中每个key, value替换模板中的标签(标签名称为key),再下载<paramref name="ds"/>的数据行
    /// </summary>
    /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param>
    /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param>
    /// <param name="template">模板文件的物理路径(不是IIS虚拟路径)</param>
    /// <param name="items">标签的键、值对</param>
    /// <param name="rowIndex">数据行的开始位置(1开始的索引,即Excel中的行号)</param>
    /// <param name="ds"></param>
    /// <returns>返回下载文件的链接地址(使用download.aspx)</returns>
    public static string DownloadXls(string name, string prefix, string template, IDictionary <string, string> items, int rowIndex, IList <DownloadFormat> format, DataSet ds)
    {
        ExcelApp       excelApp = null;
        ExcelWorkbook  workbook = null;
        ExcelWorksheet sheet    = null;
        string         fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
        string         filePath = DownloadFolder + fileName;

        try
        {
            excelApp = new ExcelApp();
            workbook = excelApp.Open(template, 0);
            workbook.SaveAs(filePath);
            sheet = workbook.Worksheets(1);

            //标签替换
            ExcelRange range = sheet.Cells();
            if (items != null)
            {
                foreach (KeyValuePair <string, string> kv in items)
                {
                    range.Replace(kv.Key, kv.Value, false);
                }
            }

            //数据行
            int index = rowIndex;
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                for (int i = 0; i < format.Count; i++)
                {
                    DownloadFormat ft = format[i];
                    if (ft.ColumnIndex == null)
                    {
                        continue;
                    }
                    for (int j = 0; j < ft.ColumnIndex.Length; j++)
                    {
                        SetCellValue(sheet.Cells(index, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
                    }
                }
                index++;
            }

            workbook.Save();
        }
        catch (Exception er)
        {
            throw er;
        }
        finally
        {
            if (sheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet.COMObject);
            }
            if (workbook != null)
            {
                workbook.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook.COMObject);
            }
            if (excelApp != null)
            {
                excelApp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp.COMObject);
            }
        }

        return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
    }
 /// <summary>
 /// Quit file
 /// </summary>
 public void Quit()
 {
     ExcelApp.Quit();
 }
Esempio n. 25
0
    /// <summary>
    /// 简单格式下载,只下载<paramref name="ds"/>的数据行
    /// </summary>
    /// <param name="name">用户下载框中显示的保存文件名,例如:SN_08082600012.xls</param>
    /// <param name="prefix">内部生成的下载文件前缀,例如:SN</param>
    /// <param name="format">数据列的格式描述信息</param>
    /// <param name="ds"></param>
    /// <returns>返回下载文件的链接地址(使用download.aspx)</returns>
    public static string DownloadXls(string name, string prefix, IList <DownloadFormat> format, DataSet ds)
    {
        string fileName = prefix + DateTime.Now.ToString("_yyMMdd_HHmmss") + ".xls";
        string filePath = DownloadFolder + fileName;

        ExcelApp       excelapp   = null;
        ExcelWorkbook  excelBook  = null;
        ExcelWorksheet excelSheet = null;

        try
        {
            excelapp = new ExcelApp();
            excelapp.DisplayAlerts = false;
            excelBook  = excelapp.NewWorkBook();
            excelSheet = excelBook.Worksheets(1);
            int rowIndex = 1;

            for (int i = 0; i < format.Count; i++)
            {
                excelSheet.Cells(rowIndex, i + 1).Value = format[i].Title;
            }
            ExcelRange rg = excelSheet.Range(rowIndex, rowIndex, 1, format.Count);
            rg.SelectRange();
            rg.Font.Bold           = true;
            rg.HorizontalAlignment = 3;
            rg.Interior.SetColor(221, 221, 221);
            rowIndex++;

            #region 写文件
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                for (int i = 0; i < format.Count; i++)
                {
                    DownloadFormat ft = format[i];
                    if (ft.ColumnIndex == null)
                    {
                        continue;
                    }
                    for (int j = 0; j < ft.ColumnIndex.Length; j++)
                    {
                        SetCellValue(excelSheet.Cells(rowIndex, i + 1), j, ft.Type, row, ft.ColumnIndex[j]);
                    }
                }
                rowIndex++;
            }
            #endregion

            ExcelRange excelRange = excelSheet.Cells();
            excelRange.SelectRange();
            excelRange.AutoFit();
            excelRange.Font.Size = 10;
            excelBook.SaveAs(filePath);
        }
        catch (Exception er)
        {
            throw er;
        }
        finally
        {
            if (excelSheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
            }
            if (excelBook != null)
            {
                excelBook.Close();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
            }
            if (excelapp != null)
            {
                excelapp.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
            }
        }

        return("/download.aspx?type=p&name=" + Microsoft.JScript.GlobalObject.escape(name) + "&path=" + Microsoft.JScript.GlobalObject.escape(filePath));
    }
Esempio n. 26
0
    protected void MagicItemCommand(object sender, MagicItemEventArgs e)
    {
        if (e.CommandName == "Save")
        {
            if (this.FileUpload1.FileName.Trim().Length <= 0)
            {
                WebUtil.ShowMsg(this, "请选择盘点结果文件");
                return;
            }
            string fileName = this.FileUpload1.FileName;
            if (!fileName.EndsWith(".xls"))
            {
                WebUtil.ShowMsg(this, "请选择有效的Excel文件");
                return;
            }
            string filePath = System.IO.Path.Combine(DownloadUtil.DownloadFolder, "CK_IMP_" + DateTime.Now.ToString("yyMMdd_HHmmss") + ".xls");
            this.FileUpload1.SaveAs(filePath);
            IList <INVCheckLine> lines = new List <INVCheckLine>();

            #region 读取文件
            ExcelApp       excelapp   = null;
            ExcelWorkbook  excelBook  = null;
            ExcelWorksheet excelSheet = null;
            try
            {
                excelapp = new ExcelApp();
                excelapp.DisplayAlerts = false;
                excelBook  = excelapp.Open(filePath, 0);
                excelSheet = excelBook.Worksheets(1);
                int     rowIndex = 2;
                string  lineNum  = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
                decimal qty;
                while (lineNum.Length == 4)
                {
                    qty = Cast.Decimal(excelSheet.Range(rowIndex, rowIndex, 9, 9).Value, 0M);
                    INVCheckLine line = new INVCheckLine();
                    line.LineNumber = lineNum;
                    line.CurrentQty = qty;
                    lines.Add(line);
                    rowIndex++;
                    lineNum = Cast.String(excelSheet.Range(rowIndex, rowIndex, 1, 1).Value).Trim();
                }
            }
            catch (Exception er)
            {
                WebUtil.ShowError(this, er.Message);
                return;
            }
            finally
            {
                if (excelSheet != null)
                {
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelSheet.COMObject);
                }
                if (excelBook != null)
                {
                    excelBook.Close();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelBook.COMObject);
                }
                if (excelapp != null)
                {
                    excelapp.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelapp.COMObject);
                }
            }
            #endregion

            using (ISession session = new Session())
            {
                try
                {
                    INVCheckHead head = INVCheckHead.Retrieve(session, WebUtil.Param("ordNum"));
                    if (head == null)
                    {
                        WebUtil.ShowError(this, "盘点单" + WebUtil.Param("ordNum") + "不存在");
                        return;
                    }
                    session.BeginTransaction();
                    head.ClearCheckQty(session);
                    head.UpdateLines(session, lines);
                    session.Commit();
                }
                catch (Exception er)
                {
                    session.Rollback();
                    WebUtil.ShowError(this, er.Message);
                    return;
                }
            }

            this.Response.Redirect(WebUtil.Param("return"));
        }
    }