상속: ISerializable
예제 #1
0
        public static bool GenExcel <T>(string filepath, string sheetName, string[] headers, string[] pathes, List <T> items)
        {
            Application excel = new Application();

            excel.Application.Workbooks.Add(true);
            excel.Visible       = false;
            excel.DisplayAlerts = false;

            Workbooks  books = excel.Workbooks;
            _Workbook  book  = books.Add(XlWBATemplate.xlWBATWorksheet);
            _Worksheet sheet = book.ActiveSheet;

            try
            {
                System.Reflection.Missing miss = System.Reflection.Missing.Value;

                sheet.Name = sheetName;

                for (int i = 0; i < headers.Length; i++)
                {
                    excel.Cells[1, i + 1] = headers[i];
                }
                Type t = typeof(T);
                for (int i = 0; i < items.Count; i++)
                {
                    for (int j = 0; j < pathes.Length; j++)
                    {
                        object v = t.GetProperty(pathes[j]).GetValue(items[i], null);
                        excel.Cells[2 + i, j + 1] = v == null ? "" : v.ToString();
                    }
                }
                Range range = sheet.Range[sheet.Cells[1, 1], sheet.Cells[items.Count + 1, headers.Length + 1]];
                range.HorizontalAlignment = XlHAlign.xlHAlignLeft;
                sheet.SaveAs(filepath, miss, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                book.Close(false, miss, miss);
                books.Close();
                excel.Quit();
            }
            catch (Exception e)
            {
                return(false);
            }
            finally
            {
                Marshal.ReleaseComObject(sheet);
                Marshal.ReleaseComObject(book);
                Marshal.ReleaseComObject(books);
                Marshal.ReleaseComObject(excel);
                GC.Collect();
            }
            return(true);
        }
        public void SetRuntimeValue(string name, object value)
        {
            PropertyValueLinker pvl;

            if (_properties.TryGetValue(name, out pvl))
            {
                System.Reflection.Missing mv = value as System.Reflection.Missing;
                if (mv != null)
                {
                    pvl.UserRuntimeValue = false;
                }
                else
                {
                    pvl.UserRuntimeValue = true;
                    pvl.RuntimeValue     = value;
                }
            }
        }
예제 #3
0
        public void printAll(DataTable dt)
        {
            if (dt != null)
            {
                try
                {
                    //保存文件
                    SaveFileDialog saveFileDialog = new SaveFileDialog();
                    saveFileDialog.Filter           = "导出Excel (*.xls)|*.xls";
                    saveFileDialog.FilterIndex      = 0;
                    saveFileDialog.RestoreDirectory = true;
                    saveFileDialog.CreatePrompt     = false;
                    saveFileDialog.Title            = "导出文件保存路径";
                    saveFileDialog.ShowDialog();
                    string strName = saveFileDialog.FileName;
                    if (strName.Length != 0)
                    {
                        System.Reflection.Missing miss = System.Reflection.Missing.Value;
                        //实例化一个excel对象
                        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                        excel.Application.Workbooks.Add(true);
                        excel.Visible = false;
                        if (excel == null)
                        {
                            MessageBox.Show("Excel无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }

                        Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                        Microsoft.Office.Interop.Excel.Workbook  book  = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                        Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                        sheet.Name = "sheet1";
                        //int m = 0, n = 0;

                        //生成表头
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                        }
                        //写入数据
                        if (dt.Rows.Count > 0)
                        {
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                for (int j = 0; j < dt.Columns.Count; j++)
                                {
                                    string str = dt.Rows[i][j].ToString();
                                    excel.Cells[i + 2, j + 1] = str;
                                }
                            }
                        }
                        sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                        book.Close(false, miss, miss);
                        books.Close();
                        excel.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);

                        GC.Collect();
                        MessageBox.Show("数据已成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        System.Diagnostics.Process.Start(strName);
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "错误提示");
                }
            }
        }
 internal static void GetUnitySerializationInfo(SerializationInfo info, Missing missing)
 {
     info.SetType(typeof(UnitySerializationHolder));            
     info.AddValue("UnityType", MissingUnity);
 }
예제 #5
0
    public void Download(int ImageColNum, string filePathName, string tempFileName)
    {
        //设置response
        System.Web.HttpContext.Current.Response.Clear();
        System.Web.HttpContext.Current.Response.BufferOutput = true;
        System.Web.HttpContext.Current.Response.Charset      = System.Text.Encoding.UTF8.HeaderName;
        System.Web.HttpContext.Current.Response.BufferOutput = true;
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + DownloadFileName + ".xls");
        System.Web.HttpContext.Current.Response.ContentType     = "application/ms-excel";
        System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
        System.Web.HttpContext.Current.Response.HeaderEncoding  = System.Text.Encoding.UTF8;

        //设置HTML头
        System.Reflection.Missing MissingValue = System.Reflection.Missing.Value;
        Application app = new Application();
        Workbooks   wks = app.Workbooks;
        Workbook    wk  = wks.Add(MissingValue);
        Sheets      wss = wk.Worksheets;
        Worksheet   ws  = (Worksheet)wss.get_Item(1);

        app.Visible       = false;
        app.DisplayAlerts = false;
        int i = 1, j = 2;

        //循环Columns 设置列名
        if (exportDataSource != null)
        {
            ((Range)ws.Cells[1, 1]).Value2 = "Picture";
            foreach (DataColumn dc in exportDataSource.Columns)
            {
                if (dc.Caption != "Picture")
                {
                    //((Range)ws.Cells[i, j]).Value2 = dc.Caption;
                    if (dc.Caption == "Name")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 25;
                        ((Range)ws.Cells[i, j]).Value2      = "Name";
                    }
                    else if (dc.Caption == "Style")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 20;
                        ((Range)ws.Cells[i, j]).Value2      = "Model Number";
                    }
                    else if (dc.Caption == "Barcode")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 14;
                        ((Range)ws.Cells[i, j]).Value2      = "Barcode";
                    }
                    else if (dc.Caption == "Type_Name")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 14;
                        ((Range)ws.Cells[i, j]).Value2      = "Category";
                    }
                    else if (dc.Caption == "SType_Name")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 14;
                        ((Range)ws.Cells[i, j]).Value2      = "Sub Category";
                    }
                    else if (dc.Caption == "Color")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 12;
                        ((Range)ws.Cells[i, j]).Value2      = "Color";
                    }
                    else if (dc.Caption == "Size")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 6;
                        ((Range)ws.Cells[i, j]).Value2      = "Size";
                    }
                    else if (dc.Caption == "Order_Number")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 15;
                        ((Range)ws.Cells[i, j]).Value2      = "Order quantity";
                    }
                    else if (dc.Caption == "Real_Number")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 15;
                        ((Range)ws.Cells[i, j]).Value2      = "Real quantity";
                    }
                    else if (dc.Caption == "Descriptions")
                    {
                        ((Range)ws.Cells[i, j]).ColumnWidth = 30;
                        ((Range)ws.Cells[i, j]).Value2      = "Descriptions";
                    }
                    j++;
                }
                //sb.AppendFormat("<td>{0}</td>", dc.Caption);
            }
            i++;
        }
        ((Range)ws.Cells[1, 1]).RowHeight = 22;
        string imgPath;      // 图片地址
        int    tempRowIndex; // 当前图片所在行

        //循环TABLE设置数据行
        if (exportDataSource != null && exportDataSource.Rows.Count > 0)
        {
            // 初始化图片地址和当前图片行
            imgPath      = filePathName + exportDataSource.Rows[0]["Picture"].ToString();
            tempRowIndex = 2;
            Pictures pics = (Pictures)ws.Pictures(MissingValue);
            float    PictureWidth, PictureHeight;
            PictureWidth  = 135;
            PictureHeight = 80;
            ((Range)ws.Cells[1, 1]).ColumnWidth = 30;

            // 第一行插入图片
            if (imgPath != string.Empty)
            {
                if (File.Exists(imgPath))
                {
                    Bitmap bp = new Bitmap(imgPath); //todo:
                    PictureHeight = bp.Height * PictureWidth / bp.Width;
                    Range r = (Range)ws.Cells[2, ImageColNum];
                    r.Select();

                    //ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToInt32(r.Left), Convert.ToInt32(r.Top) + 1, PictureWidth, PictureHeight);
                }
                //ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 10, 10, PictureWidth, PictureHeight);
            }

            foreach (DataRow dr in exportDataSource.Rows)
            {
                // 填充单元格
                j = 2;
                foreach (DataColumn dc in exportDataSource.Columns)
                {
                    if (j != ImageColNum && dc.Caption != "Picture")
                    {
                        ((Range)ws.Cells[i, j]).Value2 = "'" + dr[dc.Caption];
                    }
                    //sb.AppendFormat("<td>{0}</td>", dr[dc.Caption]);
                    j++;
                }
                //填充图片
                if (filePathName + dr["Picture"].ToString() != imgPath)
                {
                    if (imgPath != filePathName)
                    {
                        //设置高度和宽度
                        int rowCount = i - tempRowIndex;
                        if (rowCount * 20 < PictureHeight)
                        {
                            for (int tempi = tempRowIndex; tempi < i; tempi++)
                            {
                                ((Range)ws.Cells[tempi, ImageColNum]).RowHeight = PictureHeight / rowCount + 3;
                            }
                        }
                        //合并单元格

                        if (i > 2)
                        {
                            Range tempr = ws.get_Range(ws.Cells[tempRowIndex, ImageColNum], ws.Cells[i - 1, ImageColNum]);
                            tempr.MergeCells = true;
                        }
                        else
                        {
                            Range tempr = ws.get_Range(ws.Cells[tempRowIndex, ImageColNum], ws.Cells[i, ImageColNum]);
                            tempr.MergeCells = true;
                        }
                    }

                    //重新设置临时变量
                    imgPath      = filePathName + dr["Picture"].ToString();
                    tempRowIndex = i;
                    if (File.Exists(imgPath))
                    {
                        Bitmap bp = new Bitmap(imgPath); //todo:
                        PictureHeight = bp.Height * PictureWidth / bp.Width;
                        Range r = (Range)ws.Cells[i, ImageColNum];
                        r.Select();
                        //ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, (float)r.Left, (float)r.Top, PictureWidth, PictureHeight);
                        //ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, Convert.ToInt32(r.Left), Convert.ToInt32(r.Top) + 1, PictureWidth, PictureHeight);
                        //ws.Shapes.AddPicture(imgPath, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 10, 10, 150, 150);
                    }
                }
                i++;
            }
            //设置最后一个图片的高度和宽度
            int rowCount2 = i - tempRowIndex;
            if (rowCount2 * 20 < PictureHeight)
            {
                for (int tempi = tempRowIndex; tempi < i; tempi++)
                {
                    ((Range)ws.Cells[tempi, ImageColNum]).RowHeight = PictureHeight / rowCount2 + 3;
                }
            }
            //合并单元格
            Range tempr2 = ws.get_Range(ws.Cells[tempRowIndex, ImageColNum], ws.Cells[i - 1, ImageColNum]);
            tempr2.MergeCells = true;
        }
        //导出数据
        wk.SaveAs(tempFileName, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue, XlSaveAsAccessMode.xlNoChange, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue);

        //关闭文件,退出EXCEL

        //System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
        //System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
        //System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
        //GC.Collect();               //显式调用GC


        wk.Close(false, MissingValue, MissingValue);
        app.Quit();
        //释放Excel资源
        if (wks != null && app != null)
        {
            foreach (_Workbook book in wks)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
            }
            System.Runtime.InteropServices.Marshal.ReleaseComObject(wks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
        }

        app = null;
        wks = null;
        wk  = null;
        ws  = null;
        GC.Collect();

        //app.Visible = true;
        //Kill(app);
        //下载文件
        System.Web.HttpContext.Current.Response.WriteFile(tempFileName);

        System.Web.HttpContext.Current.Response.End();
    }
예제 #6
0
        public void ToExcel1(DataGridView gridView)
        {
            //try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();

                saveFileDialog.InitialDirectory = System.Windows.Forms.Application.StartupPath;
                if (gridView.Rows.Count == 0)
                {
                    MessageBox.Show("没有数据可供导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                else
                {
                    /*
                     * 获取保存EXCEL的路径
                     */
                    saveFileDialog.Filter           = "Execl files (*.xls)|*.xls";
                    saveFileDialog.FilterIndex      = 0;
                    saveFileDialog.RestoreDirectory = true;
                    saveFileDialog.CreatePrompt     = true;
                    saveFileDialog.Title            = "导出文件保存路径";
                    if (saveFileDialog.ShowDialog() == DialogResult.OK)
                    {
                        //strName储存保存EXCEL路径
                        string strName = saveFileDialog.FileName;
                        if (strName.Length != 0)
                        {
                            System.Reflection.Missing miss = System.Reflection.Missing.Value;
                            Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                            excel.Application.Workbooks.Add(true);;
                            //若是true,则在导出的时候会显示EXcel界面。
                            excel.Visible = false;
                            if (excel == null)
                            {
                                MessageBox.Show("EXCEL无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            }
                            Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                            Microsoft.Office.Interop.Excel.Workbook  book  = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                            Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                            sheet.Name = "test";
                            //给进度条赋最大值,为gridView的行数
                            //progressBar1.Maximum = gridView.RowCount;
                            //清零计数并开始计数
                            //TimeP = new System.DateTime(0);
                            //timer1.Start();
                            //label1.Text = TimeP.ToString("HH:mm:ss");
                            //生成字段名称,逐条写,无效率
                            for (int i = 0; i < gridView.ColumnCount; i++)
                            {
                                excel.Cells[1, i + 1] = gridView.Columns[i].HeaderText.ToString();
                            }
                            //以下为填充数据关键代码,逐条写,无效率
                            for (int i = 0; i < gridView.RowCount; i++)
                            {
                                for (int j = 0; j < gridView.ColumnCount; j++)
                                {
                                    if (gridView[j, i] != null && gridView[j, i].Value != null)
                                    {
                                        if (gridView[j, i].Value == typeof(string))
                                        {
                                            excel.Cells[i + 2, j + 1] = "" + gridView[j, i].Value.ToString();
                                        }
                                        else
                                        {
                                            excel.Cells[i + 2, j + 1] = gridView[j, i].Value.ToString();
                                        }
                                    }
                                    else
                                    {
                                        excel.Cells[i + 2, j + 1] = "";
                                    }
                                }
                                //进度条加1
                                //progressBar1.Value++;

                                /*
                                 * 注意此Application.DoEvents(),如果无此句,当切换窗口后回到本程序无法重绘窗体会出现假死状态
                                 * 此处我试过用委托和线程异步调用的方法,但效果没有这句效果好
                                 */
                                System.Windows.Forms.Application.DoEvents();
                            }

                            //保存EXCEL并释放资源
                            sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                            book.Close(false, miss, miss);
                            books.Close();
                            excel.Quit();
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                            System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                            GC.Collect();
                            //timer1.Stop();
                            MessageBox.Show("数据已经成功导出到:" + saveFileDialog.FileName.ToString(), "导出完成", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        }
                    }
                }
            }
            //catch (Exception er)
            {
                // MessageBox.Show(er.Message);
                //timer1.Stop();
                return;
            }
        }
예제 #7
0
    public static void CreateExcelFileForDataTable(System.Data.DataTable table, string strFullFilePath, string title)
    {
        //文件存在时先删除文件后再进行下一步操作
        FileInfo file = new FileInfo(strFullFilePath);

        if (file.Exists)
        {
            file.Delete();
        }
        int rowIndex = 3; //开始写入数据的单元格行
        int colIndex = 0; //开始写入数据的单元格列

        System.Reflection.Missing miss   = System.Reflection.Missing.Value;
        Excel.ApplicationClass    mExcel = new Excel.ApplicationClass();
        mExcel.Visible = false;
        Excel.Workbooks mBooks = (Excel.Workbooks)mExcel.Workbooks;
        Excel.Workbook  mBook  = (Excel.Workbook)(mBooks.Add(miss));
        Excel.Worksheet mSheet = (Excel.Worksheet)mBook.ActiveSheet;
        Excel.Range     er     = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本
        er.Value2 = title;
        try
        {
            foreach (DataColumn col in table.Columns) //将所得到的表的列名,赋值给单元格
            {
                colIndex++;
                mSheet.Cells[3, colIndex] = col.ColumnName;
            }
            foreach (DataRow row in table.Rows) //同样方法处理数据
            {
                rowIndex++;
                colIndex = 0;
                foreach (DataColumn col in table.Columns)
                {
                    colIndex++;
                    if (colIndex == 2)
                    {
                        mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失
                    }
                    else
                    {
                        mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                    }
                }
            }
            //保存工作已写入数据的工作表
            mBook.SaveAs(strFullFilePath, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
            // return true;
        }
        catch (Exception ee)
        {
            throw new Exception(ee.Message);
        }
        finally //finally中的代码主要用来释放内存和中止进程()
        {
            mBook.Close(false, miss, miss);
            mBooks.Close();
            mExcel.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
            GC.Collect();
        }
        //return false;
    }
예제 #8
0
        /// <summary>
        /// 导出Excel文件
        /// </summary>
        public static int DataTableToExcel(DataTable dt, string title, string columns, string sheetName, string fileName)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.Filter      = "导出Excel (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.Title       = "导出文件保存路径";
            saveFileDialog.FileName    = "LZ_" + fileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                string strName = saveFileDialog.FileName;
                if (strName.Length != 0)
                {
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass();
                    excel.Application.Workbooks.Add(true);;
                    excel.Visible = false;//若是true,则在导出的时候会显示EXcel界面。
                    if (excel == null)
                    {
                        Logger.Error("Excel文件保存失败。", null);
                        return(CConstant.EXPORT_FAILURE);
                    }
                    Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook  book  = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                    Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                    sheet.Name = sheetName;
                    int m = 0, n = 0;
                    //生成列名称 这里i是从1开始的 因为我第0列是个隐藏列ID 没必要写进去
                    string[] strHeader = title.Split(',');
                    string[] strColumns = columns.Split(',');
                    for (int i = 0; i < strHeader.Length; i++)
                    {
                        excel.Cells[1, i + 1] = strHeader[i].ToString();
                    }
                    //填充数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        DataRow dr = dt.Rows[i];
                        //j也是从1开始 原因如上 每个人需求不一样
                        int j = 1;
                        foreach (string str in strColumns)
                        {
                            try
                            {
                                if (dr[str.Trim()].GetType() == typeof(string))
                                {
                                    excel.Cells[i + 2, j] = "'" + dr[str.Trim()].ToString();
                                    j++;
                                }
                                else
                                {
                                    excel.Cells[i + 2, j] = dr[str.Trim()].ToString();
                                    j++;
                                }
                            }
                            catch
                            {
                                j++;
                                continue;
                            }
                        }
                    }
                    try
                    {
                        sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                        book.Close(false, miss, miss);
                        books.Close();
                        excel.Quit();
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                        GC.Collect();
                        //MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //System.Diagnostics.Process.Start(strName);
                    }
                    catch (Exception ex)
                    {
                        Logger.Error("Excel文件保存失败。", ex);
                        return(CConstant.EXPORT_FAILURE);
                    }
                }
                return(CConstant.EXPORT_SUCCESS);
            }
            else
            {
                return(CConstant.EXPORT_CANCEL);
            }
            return(CConstant.EXPORT_SUCCESS);
        }
예제 #9
0
        /// <summary>
        /// DataSet转出为Excel
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="sheetName"></param>
        /// <param name="strFileName"></param>
        public static void DataSetToExcel(DataSet ds, string sheetName, string strFileName)
        {
            if (ds == null || ds.Tables.Count == 0)
            {
                return;
            }
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            //创建excel对象appExcel,Workbook对象,WorkSheet对象,Range对象
            Microsoft.Office.Interop.Excel.Application appExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook    workbookData;
            Microsoft.Office.Interop.Excel.Worksheet   worksheetData;
            Microsoft.Office.Interop.Excel.Range       rangeData;
            appExcel.Visible = false;
            System.Globalization.CultureInfo currentCi = System.Threading.Thread.CurrentThread.CurrentCulture;
            System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            workbookData = appExcel.Workbooks.Add(miss);
            try
            {
                for (int k = 0; k < ds.Tables.Count; k++)
                {
                    worksheetData      = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                    worksheetData.Name = ds.Tables[k].TableName;
                    worksheetData.Columns.EntireColumn.AutoFit();
                    if (ds.Tables[k] != null)
                    {
                        for (int i = 0; i < ds.Tables[k].Columns.Count; i++)
                        {
                            worksheetData.Cells[i + 1] = ds.Tables[k].Columns[i].ColumnName.ToString();
                        }
                        //先给range对象一个范围从A2开始,range对象可以给一个cell范围,也可以给例如A1-H10这样的范围
                        //因为第一行已经写了表头,所以所有数据都从A2开始
                        rangeData = worksheetData.get_Range("A2", miss);
                        int iRowCount      = ds.Tables[k].Rows.Count;
                        int iColumnAccount = ds.Tables[k].Columns.Count;
                        //在内存中声明一个iEachSize*iColumnAccount的数组,iEachSize是每次最大存储的行数,iColumnAccount就是最大存储的实际列数
                        object[,] objVal = new object[iRowCount, iColumnAccount];
                        //方法一
                        //for(int i=0;i<ds.Tables[k].Rows.Count;i++)
                        //{
                        //     for (int j = 0; j < iColumnAccount; j++)
                        //    {
                        //        objVal[i, j] = ds.Tables[k].Rows[i][j].ToString();
                        //         worksheetData.Cells[i+2,j+1]=objVal[i,j];
                        //    }
                        //    System.Windows.Forms.Application.DoEvents();
                        //}

                        //方法2
                        Microsoft.Office.Interop.Excel.Range xlRange = null;
                        for (int i = 0; i < iRowCount; i++)
                        {
                            for (int j = 0; j < iColumnAccount; j++)
                            {
                                objVal[i, j] = ds.Tables[k].Rows[i][j].ToString();
                            }
                            if (i % 1000 == 0)
                            {
                                System.Windows.Forms.Application.DoEvents();
                            }
                        }
                        xlRange = worksheetData.get_Range(appExcel.Cells[2, 1], appExcel.Cells[iRowCount + 1, iColumnAccount]);
                        //调用Range的Value2属性,把内存中的值赋给Excel
                        xlRange.Value2 = objVal;
                        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange);
                        xlRange = null;
                    }
                }
                workbookData.Saved     = true;
                appExcel.DisplayAlerts = false;
                workbookData.SaveAs(strFileName + "", miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                appExcel.DisplayAlerts = true;
                appExcel.Quit();
                //结束之前恢复环境
                System.Threading.Thread.CurrentThread.CurrentCulture = currentCi;
                GC.Collect();
            }
            catch (Exception ex)
            {
                appExcel.Quit();
                Kill(appExcel);
                System.Threading.Thread.CurrentThread.CurrentCulture = currentCi;
                GC.Collect();
            }
        }
예제 #10
0
        public static XLSWorker Create(string filepath, bool overwrite = true, /* XlFileFormat format = XlFileFormat.xlWorkbookNormal ,*/ Missing passwd = null)
        {
            if (string.IsNullOrEmpty(filepath))
                throw new ArgumentException("Bad argumenst");

            passwd = passwd ?? Missing.Value;
            object missing = Missing.Value;

            Application xlapp = new Application();
            xlapp.DisplayAlerts = false;
            xlapp.ScreenUpdating = false;

            Workbook workBook;
            if (xlapp.Workbooks.Count == 0)
            {
                workBook = xlapp.Workbooks.Add(missing);
            }
            else
            {
                workBook = xlapp.ActiveWorkbook;
            }

            if (File.Exists(filepath))
            {
                if (overwrite)
                {
                    File.Delete(filepath);
                }
                else
                {
                    throw new ArgumentException("File already exists and 'overwrite' flag is FALSE");
                }
            }

            workBook.SaveAs(filepath, XlFileFormat.xlWorkbookNormal /* format */ , passwd, missing, false, false, XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing, missing);

            return new XLSWorker(xlapp);
        }
예제 #11
0
        public void ExcelOut(List <MatchedData> lst, string sFileName)
        {
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            MatchedData matchecdData       = new MatchedData();

            Excel.Application excelApplication = new Excel.Application();
            Excel.Workbook    excelWorkbook    = excelApplication.Workbooks.Add();
            excelApplication.UserControl = true;
            excelApplication.Application.DisplayAlerts = false;

            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelWorkbook.Sheets.Add(miss, miss, miss, miss);
            excelWorksheet.Name = "Protein";

            int iRow = 0;

            try
            {
                for (int i = excelWorkbook.Sheets.Count; i >= 1; i--)
                {
                    Excel.Worksheet excelWorksheetTemp = (Excel.Worksheet)excelWorkbook.Sheets[i];
                    if (excelWorksheetTemp.Name != "Protein")
                    {
                        excelWorksheetTemp.Delete();
                    }
                }

                //Header
                iRow = 1;
                excelWorksheet.Cells[iRow, 1]  = "Index";
                excelWorksheet.Cells[iRow, 2]  = "ID";
                excelWorksheet.Cells[iRow, 3]  = "MOD_RES";
                excelWorksheet.Cells[iRow, 4]  = "PTM_Score";
                excelWorksheet.Cells[iRow, 5]  = "TotalPTM_Score";
                excelWorksheet.Cells[iRow, 6]  = "Z";
                excelWorksheet.Cells[iRow, 7]  = "DatasetFile";
                excelWorksheet.Cells[iRow, 8]  = "Database";
                excelWorksheet.Cells[iRow, 9]  = "Repository";
                excelWorksheet.Cells[iRow, 10] = "ScreeningApproach";
                excelWorksheet.Cells[iRow, 11] = "ExpMZ";
                excelWorksheet.Cells[iRow, 12] = "TheoMZ";
                excelWorksheet.Cells[iRow, 13] = "Error_PPM";
                excelWorksheet.Cells[iRow, 14] = "AccessionNumber";
                excelWorksheet.Cells[iRow, 15] = "ProteinName";
                excelWorksheet.Cells[iRow, 16] = "Length";
                excelWorksheet.Cells[iRow, 17] = "Function";
                excelWorksheet.Cells[iRow, 18] = "Sequence";
                excelWorksheet.Cells[iRow, 19] = "NMFs";
                excelWorksheet.Cells[iRow, 20] = "Actual_PMFs";

                //Value
                iRow = 2;
                for (int i = 0; i < lst.Count; i++)
                {
                    matchecdData = lst[i];

                    excelWorksheet.Cells[iRow, 1]  = matchecdData.Index;
                    excelWorksheet.Cells[iRow, 2]  = matchecdData.ID;
                    excelWorksheet.Cells[iRow, 3]  = matchecdData.MOD_RES.Replace(";", "").Replace(",", "");
                    excelWorksheet.Cells[iRow, 4]  = matchecdData.DataBase_PTM_SCORE;
                    excelWorksheet.Cells[iRow, 5]  = matchecdData.Total_PTM_Score;
                    excelWorksheet.Cells[iRow, 6]  = matchecdData.Z;
                    excelWorksheet.Cells[iRow, 7]  = matchecdData.DatasetFile;
                    excelWorksheet.Cells[iRow, 8]  = matchecdData.Database;
                    excelWorksheet.Cells[iRow, 9]  = matchecdData.Repository;
                    excelWorksheet.Cells[iRow, 10] = matchecdData.ScreeningApproach;
                    excelWorksheet.Cells[iRow, 11] = matchecdData.ExpMZ;
                    excelWorksheet.Cells[iRow, 12] = matchecdData.TheoMZ;
                    excelWorksheet.Cells[iRow, 13] = matchecdData.Error_PPM;
                    excelWorksheet.Cells[iRow, 14] = matchecdData.AccessionNumber;
                    excelWorksheet.Cells[iRow, 15] = matchecdData.ProteinName;
                    excelWorksheet.Cells[iRow, 16] = matchecdData.Length;
                    excelWorksheet.Cells[iRow, 17] = matchecdData.Function;
                    excelWorksheet.Cells[iRow, 18] = matchecdData.Sequence;
                    excelWorksheet.Cells[iRow, 19] = matchecdData.NMFS;
                    excelWorksheet.Cells[iRow, 20] = matchecdData.Actualpmfs;

                    iRow++;
                }

                excelWorkbook.SaveAs(sFileName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);
                excelWorkbook.Close(false, miss, miss);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                matchecdData = null;

                if (excelApplication != null)
                {
                    excelApplication.Workbooks.Close();
                    excelApplication.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication.Workbooks);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication);
                    System.GC.Collect();
                }

                if (excelApplication != null)
                {
                    IntPtr t = new IntPtr(excelApplication.Hwnd);
                    int    k = 0;
                    GetWindowThreadProcessId(t, out k);
                    System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
                    p.Kill();
                }

                excelApplication = null;
                GC.Collect();
            }
        }
예제 #12
0
        /// <summary>
        /// DataSet導出到Excel,可以指定SheetName、文件名和是否要保存,也可以指定保存后是否自動退出
        /// </summary>
        /// <param name="ds">要導出的DataSet</param>
        /// <param name="sheetNames">字符串數組,用來存放SheetName,數量需要和DataSet中的Table數一致,輸入null則自動給sheet編號</param>
        /// <param name="fileName">指定的文件名,不帶路徑</param>
        /// <param name="save">是否要保存,默認存放在桌面上</param>
        /// <param name="savePath">文件存放的路徑,最後不要帶\\</param>
        /// <param name="quitAfterSave">導出完成后是否要自動退出</param>
        public static void ExportToExcel(DataSet ds, string[] sheetNames, string fileName, bool save, string savePath, bool quitAfterSave)
        {
            if (ds == null)
            {
                return;
            }
            if (sheetNames == null)
            {
                sheetNames = new string[256];
            }
            System.Reflection.Missing Miss = System.Reflection.Missing.Value;
            string SheetName = "";

            if (string.IsNullOrEmpty(fileName))
            {
                fileName = "Result.xlsx";
            }

            Excel.Application xlApp   = new Excel.Application();
            Excel.Workbook    xlBook  = xlApp.Application.Workbooks.Add(true);
            Excel.Worksheet   xlSheet = (Excel.Worksheet)xlBook.ActiveSheet;
            Excel.Range       ra;
            xlApp.Visible       = true;
            xlApp.DisplayAlerts = true;
            xlApp.Caption       = fileName;
            SheetName           = (sheetNames[0] != null) ? sheetNames[0].ToString() : "Sheet1";
            xlSheet.Name        = SheetName;

            //寫第一個DataTable
            WriteExcelModule(xlSheet, ds.Tables[0]);

            //如果DataTable數量大於1,循環寫
            if (ds.Tables.Count > 1)
            {
                for (int z = 1; z < ds.Tables.Count; z++)
                {
                    xlBook.Worksheets.Add(Miss, xlBook.ActiveSheet, Miss, Miss);
                    xlSheet      = (Excel.Worksheet)xlBook.Worksheets[z + 1];
                    SheetName    = (sheetNames[z] != null) ? sheetNames[z].ToString() : "Sheet" + Convert.ToString(z + 1);
                    xlSheet.Name = SheetName;
                    xlSheet.Activate();

                    WriteExcelModule(xlSheet, ds.Tables[z]);
                }
            }

            xlSheet = (Excel.Worksheet)xlBook.Worksheets[1];
            xlSheet.Activate();

            if (save)
            {
                if (savePath != "")
                {
                    if (System.IO.Directory.Exists(savePath) == false)
                    {
                        System.IO.Directory.CreateDirectory(savePath);
                    }
                }
                else
                {
                    savePath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Desktop);
                }
                xlBook.SaveAs(savePath + "\\" + fileName, Miss, null, null, null, null, Excel.XlSaveAsAccessMode.xlNoChange,
                              null, null, null, null, null);

                if (quitAfterSave)
                {
                    xlApp.Quit();
                }
            }

            xlSheet = null;
            xlBook  = null;
            xlApp   = null;
            GC.Collect();
        }
예제 #13
0
파일: OutputExcel.cs 프로젝트: qq5013/TM
        public void coutExcel(DataGridView dataGridView1)
        {
            try
            {
                SaveFileDialog saveFileDialog = new SaveFileDialog();
                saveFileDialog.Filter           = "导出Excel (*.xls)|*.xls";
                saveFileDialog.FilterIndex      = 0;
                saveFileDialog.RestoreDirectory = true;
                saveFileDialog.CreatePrompt     = true;
                saveFileDialog.Title            = "导出文件保存路径";
                saveFileDialog.ShowDialog();
                string strName = saveFileDialog.FileName;
                if (strName.Length != 0)
                {
                    //无数据则不再执行
                    if (dataGridView1.Rows.Count == 0)
                    {
                        return;
                    }
                    //实例化Excel.Application对象
                    System.Reflection.Missing miss = System.Reflection.Missing.Value;
                    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                    excel.Application.Workbooks.Add(true);
                    excel.Visible = false;//为TRUE在导出时显示EXCEL界面
                    if (excel == null)
                    {
                        MessageBox.Show("EXCEL无法启动!", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }
                    Microsoft.Office.Interop.Excel.Workbooks books = excel.Workbooks;
                    Microsoft.Office.Interop.Excel.Workbook  book  = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
                    Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
                    sheet.Name = "test";
                    //int m = 0, n = 0;
                    //生成EXCEL列名
                    for (int i = 0; i < dataGridView1.Columns.Count; i++)
                    {
                        excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;//输出DataGridView列头名
                    }
                    //存储数据
                    for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                    {
                        for (int j = 0; j < dataGridView1.Columns.Count; j++)
                        {
                            if (dataGridView1[j, i].ValueType == typeof(string))
                            {
                                excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
                            }

                            else
                            {
                                excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
                            }
                        }
                    }
                    sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
                    book.Close(false, miss, miss);
                    books.Close();
                    excel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    GC.Collect();
                    MessageBox.Show("数据已经成功导出!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    System.Diagnostics.Process.Start(strName);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误提示");
            }
        }
예제 #14
0
        private bool XLSConvertToPDF(string sourcePath, string targetPath)
        {
            bool result = false;

            Excel.XlFixedFormatType targetType = Excel.XlFixedFormatType.xlTypePDF;
            object missing = Type.Missing;

            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp  = null;
            Microsoft.Office.Interop.Excel._Workbook        ExcelBook = null;
            try
            {
                object target = targetPath;
                object type   = targetType;
                //workBook = application.Workbooks.Open(sourcePath, missing, missing, missing, missing, missing,
                //        missing, missing, missing, missing, missing, missing, missing, missing, missing);
                // sourcePath = "C:\\Users\\IBM_ADMIN\\Desktop\\newadd.xlsx";
                System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                System.Reflection.Missing missingValue = System.Reflection.Missing.Value;
                ExcelBook = ExcelApp.Workbooks.Open(sourcePath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);

                //ExcelApp.Visible = true;
                //ExcelApp.ScreenUpdating = true;

                ////ActiveWindow.SmallScroll Down:=6;
                //ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;
                ////ExcelApp.ActiveWindow.SmallScroll = Excel.;
                //ExcelApp.ActiveWindow.Zoom = 80;
                ////ActiveWindow.SmallScroll Down:=-3
                //// excelRange.WrapText = true;
                ////ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
                //ActiveWindow.SmallScroll Down:=30
                //Set ActiveSheet.HPageBreaks(1).Location = Range("A67")
                //ActiveWindow.SmallScroll Down:=-75

                //Microsoft.Office.Interop.Excel.Worksheet WS2 = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[2];

                ////上边距
                //double top = 0;
                ////左边距
                //double left = 0;
                ////右边距
                //double right = 0;
                ////下边距
                //double footer = 0;
                //WS2.DisplayAutomaticPageBreaks = false;//显示分页线
                //WS2.PageSetup.CenterFooter = "第   &P   页,共   &N   页";
                //WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(top / 2.54);//上
                //WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(footer / 15.54);//下
                //WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(left / 2.54);//左
                //WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(right / 2.54);//右
                //WS2.PageSetup.CenterHorizontally = true;//水平居中   xlSheet.PageSetup.PrintTitleRows = "$1:$3";//顶端标题行
                //WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA3;//A3纸张大小   xlSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;//纸张方向.横向


                //Excel.Range excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[64, 24]);
                //自动调整列宽
                ////  excelRange.EntireColumn.AutoFit();
                ////   excelRange.WrapText = false;     //文本自动换行
                //excelRange.ShrinkToFit = false;
                ////设置字体在单元格内的对其方式
                //excelRange.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                //// 文本水平居中方式
                //excelRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;

                //////设置为横向打印
                ////WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;


                ////ExcelApp.ActiveWindow.FreezePanes = true;

                ////excelRange.EntireColumn.AutoFit();
                ////WS2.PageSetup.Orientation = 2;
                //WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;

                //WS2.PageSetup.LeftMargin = ExcelApp.InchesToPoints(0.0);
                //WS2.PageSetup.RightMargin = ExcelApp.InchesToPoints(0.0);
                //WS2.PageSetup.TopMargin = ExcelApp.InchesToPoints(0.0);
                //WS2.PageSetup.BottomMargin = ExcelApp.InchesToPoints(0.0);
                //WS2.PageSetup.HeaderMargin = ExcelApp.InchesToPoints(0.0);
                //WS2.PageSetup.FooterMargin = ExcelApp.InchesToPoints(0.0);
                //WS2.PageSetup.CenterHorizontally = true;
                ////  WS2.PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4;
                //WS2.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape;
                //excelRange = WS2.get_Range(WS2.Cells[1, 1], WS2.Cells[2, 20]);
                //WS2.PageSetup.PrintTitleRows = excelRange.get_Address(excelRange.Row, excelRange.Column, Excel.XlReferenceStyle.xlA1, 1, 1);


                ExcelBook.ExportAsFixedFormat(targetType, target, Excel.XlFixedFormatQuality.xlQualityStandard, true, false, missing, missing, missing, missing);
                result = true;
            }

            catch
            {
                result = false;
            }
            finally
            {
                if (ExcelBook != null)
                {
                    ExcelBook.Close(true, missing, missing);
                    ExcelBook = null;
                }
                if (ExcelApp != null)
                {
                    ExcelApp.Quit();
                    ExcelApp = null;
                }
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            return(result);
        }
예제 #15
0
        private string DownLoadExcel_Crosscheckfile(List <clsFAinfo> Result)
        {
            try
            {
                ProcessLogger.Fatal("8948 Down Initial" + DateTime.Now.ToString());
                #region 获取模板路径

                string         fullPath    = AppDomain.CurrentDomain.BaseDirectory + "System\\confing.xls";
                SaveFileDialog sfdDownFile = new SaveFileDialog();
                sfdDownFile.OverwritePrompt = false;
                string DesktopPath = Convert.ToString(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop));
                sfdDownFile.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx";

                string fileinfo = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Resources\\");
                sfdDownFile.FileName = Path.Combine(fileinfo, "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls");
                string strExcelFileName = string.Empty;
                string ResaveName       = AppDomain.CurrentDomain.BaseDirectory + "Resources\\" + "print" + "_" + DateTime.Now.ToString("yyyyMMdd_mmss") + ".xls";

                #endregion

                #region 导出前校验模板信息
                if (string.IsNullOrEmpty(sfdDownFile.FileName))
                {
                    MessageBox.Show("File name can't be empty, please Check, thanks!");
                    return("");
                }
                if (!File.Exists(fullPath))
                {
                    MessageBox.Show("Template file does not exist, please Check, thanks!");
                    return("");
                }
                else
                {
                    strExcelFileName = sfdDownFile.FileName;
                }
                #endregion

                #region Excel 初始化
                System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                Microsoft.Office.Interop.Excel.Range rng;

                Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
                System.Reflection.Missing missingValue             = System.Reflection.Missing.Value;
                Microsoft.Office.Interop.Excel._Workbook ExcelBook =
                    ExcelApp.Workbooks.Open(fullPath, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue, missingValue);
                #endregion
                ProcessLogger.Fatal("8949  Input Initial" + DateTime.Now.ToString());

                #region 导入
                try
                {
                    #region Sheet 初始化
                    Microsoft.Office.Interop.Excel._Worksheet ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.Worksheets[1];


                    #region 填充数据
                    int    RowIndex        = 6;
                    string fapiaoleixingin = "";
                    string guidangren      = "";
                    string danghao         = "";
                    string guidangshijian  = "";
                    foreach (clsFAinfo item in Result)
                    {
                        fapiaoleixingin = item.fapiaoleixing;
                        guidangren      = item.guidangrenzhanghao;
                        danghao         = item.danganhao;
                        if (item.Input_Date != null && item.Input_Date.Length > 8)
                        {
                            guidangshijian = item.Input_Date.Substring(0, 8);
                        }

                        RowIndex++;


                        ExcelSheet.Cells[RowIndex, 1] = "'" + item.fapiaohao;
                        ExcelSheet.Cells[RowIndex, 2] = "'" + item.bianhao;
                        //ExcelSheet.Cells[RowIndex, 3] = item.fapiaohao;
                    }
                    //ExcelApp.Visible = true;
                    //ExcelApp.ScreenUpdating = true;

                    ExcelSheet.Cells[1, 1] = "总件数:" + Result.Count.ToString();
                    ExcelSheet.Cells[2, 1] = "发票类型:" + fapiaoleixingin;
                    ExcelSheet.Cells[3, 1] = "归档人:" + guidangren;
                    ExcelSheet.Cells[4, 1] = "档号:" + danghao;
                    ExcelSheet.Cells[5, 1] = "归档时间:" + guidangshijian;

                    //直接打印
                    //ExcelApp.ActiveWindow.View = Excel.XlWindowView.xlPageBreakPreview;

                    //ExcelSheet.PageSetup.PaperSize =Excel.XlPaperSize.xlPaperA4;

                    //ExcelBook.PrintOut();
                    #region 写入文件
                    ProcessLogger.Fatal("8950  Output Initial" + DateTime.Now.ToString());

                    ExcelApp.DisplayAlerts  = false;
                    ExcelApp.ScreenUpdating = true;
                    ExcelBook.SaveAs(ResaveName, missingValue, missingValue, missingValue, missingValue, missingValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, missingValue, missingValue, missingValue, missingValue, missingValue);
                    ExcelApp.DisplayAlerts = false;

                    ExcelBook.Close(false, missingValue, missingValue);
                    ExcelBook = null;
                    #endregion
                    //转换成PDF 文件
                    //if (XLSConvertToPDF(ResaveName, ResaveName.Replace("xlsx", "pdf")))
                    //{
                    //    // FilePath.Add(ResaveName.Replace("xlsx", "pdf"));
                    //}

                    return(ResaveName);

                    #endregion

                    #endregion
                }
                #endregion

                #region 异常处理
                catch (Exception ex)
                {
                    ExcelApp.DisplayAlerts = false;
                    ExcelApp.Quit();
                    ExcelBook = null;
                    ExcelApp  = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    throw ex;
                }
                #endregion

                #region Finally垃圾回收
                finally
                {
                    //ExcelBook.Close(false, missingValue, missingValue);
                    //ExcelBook = null;
                    ExcelApp.DisplayAlerts = true;
                    ExcelApp.Quit();
                    clsKeyMyExcelProcess.Kill(ExcelApp);

                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                }
                #endregion
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
예제 #16
0
    public void ExportDataSetToExcel(DataSet ds, string strPath)
    {
        string ruta = "c:\\DATA";

        if (!Directory.Exists(ruta))
        {
            Directory.CreateDirectory(ruta);
        }
        int CONTADOR = 0;

        int inHeaderLength = 3, inColumn = 0, inRow = 0;

        System.Reflection.Missing Default     = System.Reflection.Missing.Value;
        //Create Excel File
        //strPath += @"\Excel" + DateTime.Now.ToString().Replace(':', '-') + ".xlsx";
        OfficeExcel.Application excelApp      = new OfficeExcel.Application();
        OfficeExcel.Workbook    excelWorkBook = excelApp.Workbooks.Add(1);
        foreach (DataTable dtbl in ds.Tables)
        {
            //Create Excel WorkSheet
            OfficeExcel.Worksheet excelWorkSheet = excelWorkBook.Sheets.Add(Default, excelWorkBook.Sheets[excelWorkBook.Sheets.Count], 1, Default);
            excelWorkSheet.Name = dtbl.TableName;    //Name worksheet

            //Write Column Name
            for (int i = 0; i < dtbl.Columns.Count; i++)
            {
                excelWorkSheet.Cells[inHeaderLength + 1, i + 1] = dtbl.Columns[i].ColumnName.ToUpper();
            }

            //Write Rows
            for (int m = 0; m < dtbl.Rows.Count; m++)
            {
                for (int n = 0; n < dtbl.Columns.Count; n++)
                {
                    inColumn = n + 1;
                    inRow    = inHeaderLength + 2 + m;
                    excelWorkSheet.Cells[inRow, inColumn] = dtbl.Rows[m].ItemArray[n].ToString();
                    if (m % 2 == 0)
                    {
                        excelWorkSheet.get_Range("A" + inRow.ToString(), "AK" + inRow.ToString()).Interior.Color = System.Drawing.ColorTranslator.FromHtml("#FCE4D6");
                    }
                }
                CONTADOR = CONTADOR + 1;
                Console.WriteLine("Filas N°" + Convert.ToString(CONTADOR));
                mensajeexport = "Filas N°" + Convert.ToString(CONTADOR);
            }

            //Excel Header
            OfficeExcel.Range cellRang = excelWorkSheet.get_Range("A1", "AK3");
            cellRang.Merge(false);
            cellRang.Interior.Color      = System.Drawing.Color.White;
            cellRang.Font.Color          = System.Drawing.Color.Gray;
            cellRang.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignCenter;
            cellRang.VerticalAlignment   = OfficeExcel.XlVAlign.xlVAlignCenter;
            cellRang.Font.Size           = 26;
            excelWorkSheet.Cells[1, 1]   = "DETALLE DE PRESUPUESTO POR LOTE";

            //Style table column names
            cellRang                = excelWorkSheet.get_Range("A4", "AK4");
            cellRang.Font.Bold      = true;
            cellRang.Font.Color     = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
            cellRang.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#ED7D31");
            //excelWorkSheet.get_Range("F4").EntireColumn.HorizontalAlignment = OfficeExcel.XlHAlign.xlHAlignRight;
            //Formate price column
            //excelWorkSheet.get_Range("F5").EntireColumn.NumberFormat = "0.00";
            //Auto fit columns
            excelWorkSheet.Columns.AutoFit();
        }

        //Delete First Page
        excelApp.DisplayAlerts = false;
        Microsoft.Office.Interop.Excel.Worksheet lastWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
        lastWorkSheet.Delete();
        excelApp.DisplayAlerts = true;

        //Set Defualt Page
        (excelWorkBook.Sheets[1] as OfficeExcel._Worksheet).Activate();

        excelWorkBook.SaveAs(strPath);
        excelWorkBook.Close();
        excelApp.Quit();


        ProcessStartInfo startInfo            = new ProcessStartInfo();

        startInfo.FileName  = "EXCEL.EXE";
        startInfo.Arguments = strPath;
        Process.Start(startInfo);


        MessageBox.Show("Se genero el Arvicho en : \n As " + strPath);
    }