示例#1
0
文件: Excel.cs 项目: techcap/CSharp
 public void SaveCopyAs(string Ps_FileName)
 {
     try
     {
         Mwb_Book.SaveCopyAs(Ps_FileName);
     }
     catch (Exception)
     {
         throw;
     }
 }
        public static void Export1(DataTable dt, string filepath)
        {
            String strFileName = "";

            strFileName = filepath;

            // Server File Path Where you want to save excel file.

            ExcelApp.Application myExcel = new ExcelApp.Application();
            //Create a New file
            ExcelApp._Workbook mybook = myExcel.Workbooks.Add(System.Reflection.Missing.Value);
            //Open the exist file
            //ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
            //          Type.Missing, Type.Missing, Type.Missing,
            //    Type.Missing,Type.Missing, Type.Missing, Type.Missing,
            //    Type.Missing, Type.Missing, Type.Missing,
            //    Type.Missing, Type.Missing,Type.Missing, Type.Missing);
            //ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
            myExcel.Visible = false;
            try
            {
                mybook.Activate();
                ExcelApp._Worksheet mysheet = (ExcelApp._Worksheet)mybook.ActiveSheet;
                int colIndex = 0;///********////
                int rowIndex = 0;
                //foreach (DataColumn dcol in dt.Columns)
                //{
                //    colIndex = colIndex + 1;
                //    myExcel.Cells[1, colIndex] = dcol.ColumnName;

                //}
                foreach (DataColumn dcol in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    myExcel.Cells[rowIndex + 1, colIndex] = dcol.ColumnName;
                    mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).WrapText = true;

                    mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Bold      = true;
                    mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Size      = 10;
                    mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                    ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Borders;
                    borders[ExcelApp.XlBordersIndex.xlEdgeLeft].LineStyle   = ExcelApp.XlLineStyle.xlContinuous;
                    borders[ExcelApp.XlBordersIndex.xlEdgeTop].LineStyle    = ExcelApp.XlLineStyle.xlContinuous;
                    borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
                    borders[ExcelApp.XlBordersIndex.xlEdgeRight].LineStyle  = ExcelApp.XlLineStyle.xlContinuous;
                    borders.Color = 0;
                    borders[ExcelApp.XlBordersIndex.xlInsideVertical].LineStyle   = ExcelApp.XlLineStyle.xlLineStyleNone;
                    borders[ExcelApp.XlBordersIndex.xlInsideHorizontal].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
                    borders[ExcelApp.XlBordersIndex.xlDiagonalUp].LineStyle       = ExcelApp.XlLineStyle.xlLineStyleNone;
                    borders[ExcelApp.XlBordersIndex.xlDiagonalDown].LineStyle     = ExcelApp.XlLineStyle.xlLineStyleNone;
                    borders = null;
                    //  mysheet.Columns.WrapText = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]);
                    //mysheet.get_Range(myExcel.Cells[1, colIndex], myExcel.Cells[1, colIndex]).
                }
                ExcelApp.Style style1 = myExcel.ActiveWorkbook.Styles.Add("Content", Type.Missing);
                //style1.Borders.Color = Color.Black;
                style1.Font.Name = "Verdana";
                // style1.WrapText = true;
                style1.Font.Size = 10;

                style1.Font.Color     = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                style1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);

                foreach (DataRow drow in dt.Rows)
                {
                    rowIndex = rowIndex + 1;
                    colIndex = 0;
                    //string str = drow[3].ToString();

                    //if ((!string.IsNullOrEmpty(drow[3].ToString())))
                    //{
                    //    decimal sum = Convert.ToDecimal(str);
                    //    if (sum <= 0)
                    //    {
                    //        break;
                    //    }
                    //}
                    foreach (DataColumn dcol in dt.Columns)
                    {
                        colIndex = colIndex + 1;
                        myExcel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
                    }


                    //    //if (sum != 0)
                    //    //{
                    //    int col = colIndex;

                    //    for (int i = 0; i <= col; i++)
                    //    {
                    //        ExcelApp.Range rng = (ExcelApp.Range)mysheet.Cells[rowIndex + 1, col];
                    //        //  mysheet.Columns.WrapText = rng;
                    //        //  rng.WrapText = true;

                    //        rng.Style = style1;
                    //        ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, col], myExcel.Cells[rowIndex + 1, col]).Borders;

                    //        borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;

                    //    }
                    //    //}
                    //}
                }
                mysheet.Columns.AutoFit();



                //For Saving excel file on Server
                mybook.SaveCopyAs(strFileName);
            }
            catch (Exception wzx)
            {
                MessageBox.Show(wzx.Message);
            }
            finally
            {
                mybook.Close(false, false, System.Reflection.Missing.Value);

                myExcel.Quit();

                GC.Collect();
            }
        }
示例#3
0
        public void CrearExcel(System.Data.DataTable dt, string path)
        {
            object misvalue = System.Reflection.Missing.Value;

            try
            {
                oXL = new Microsoft.Office.Interop.Excel.Application();

                bool codescreenUpdateState = oXL.ScreenUpdating;
                bool statusBarState        = oXL.DisplayStatusBar;
                bool eventsState           = oXL.EnableEvents;

                string pathDefinitivo = path + ".xlsx";

                oWB = (Microsoft.Office.Interop.Excel._Workbook)(oXL.Workbooks.Add(""));

                oWB.SaveCopyAs(pathDefinitivo);

                //oWB.SaveAs(pathDefinitivo, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
                //  false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
                // Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);


                oWB             = oXL.Workbooks.Open(pathDefinitivo, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                oXL.Calculation = XlCalculation.xlCalculationManual;

                oXL.ScreenUpdating   = false;
                oXL.DisplayStatusBar = false;
                oXL.Calculation      = XlCalculation.xlCalculationManual;
                oXL.EnableEvents     = false;

                oXL.Visible = false;

                oSheet = (Microsoft.Office.Interop.Excel._Worksheet)oWB.ActiveSheet;

                //Cargar header
                oSheet.Cells[1, 1]  = "Empresa: ";
                oSheet.Cells[1, 2]  = "SURFACTAN S.A.";
                oSheet.Cells[1, 11] = "Fecha: ";
                oSheet.Cells[1, 12] = DateTime.Now.ToShortDateString();
                oSheet.Cells[2, 7]  = "Listado de Muestras";

                oSheet.get_Range("L1", "L1").EntireColumn.NumberFormat = "mm/dd/yyyy";
                oSheet.get_Range("A1", "L2").Font.Bold           = true;
                oSheet.get_Range("A1", "L1").Cells.Font.Size     = 10;
                oSheet.get_Range("A1", "L2").Cells.Font.Name     = "Times New Roman";
                oSheet.get_Range("G2", "G2").Cells.Font.Size     = 16;
                oSheet.get_Range("G2", "G2").HorizontalAlignment =
                    Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
                oSheet.get_Range("G2", "G2").Cells.Font.Size   = 16;
                oSheet.get_Range("A1", "L2").VerticalAlignment =
                    Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

                oSheet.get_Range("G2", "G2").Borders.LineStyle =
                    Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

                //Agrego los headers
                oSheet.Cells[4, 1]  = "Fecha";
                oSheet.Cells[4, 2]  = "Codigo";
                oSheet.Cells[4, 3]  = "Cantidad";
                oSheet.Cells[4, 4]  = "Nombre para el Cliente";
                oSheet.Cells[4, 6]  = "Cliente";
                oSheet.Cells[4, 8]  = "Observaciones";
                oSheet.Cells[4, 10] = "Lote";
                oSheet.Cells[4, 11] = "Cantidad";
                oSheet.Cells[4, 12] = "Observaciones";

                oSheet.get_Range("A4", "L4").Cells.Font.Size   = 8;
                oSheet.get_Range("A4", "L4").Cells.Font.Name   = "Times New Roman";
                oSheet.get_Range("A4", "L4").VerticalAlignment =
                    Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;
                oSheet.get_Range("A4", "L4").HorizontalAlignment =
                    Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

                int rango = 6;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string[,] valores = new string[dt.Rows.Count, 12];

                    DataRow dr = dt.Rows[i];

                    valores[0, 0]  = dr["Fecha"].ToString();
                    valores[0, 1]  = dr["Codigo"].ToString();
                    valores[0, 2]  = dr["Cantidad"].ToString();
                    valores[0, 3]  = dr["DescriCliente"].ToString();
                    valores[0, 4]  = "";
                    valores[0, 5]  = dr["Razon"].ToString();
                    valores[0, 6]  = "";
                    valores[0, 7]  = dr["Observaciones"].ToString();
                    valores[0, 8]  = "";
                    valores[0, 9]  = dr["Lote2"].ToString();
                    valores[0, 10] = dr["Cantidad2"].ToString();
                    valores[0, 11] = dr["Observaciones2"].ToString();

                    oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Value2          = valores;
                    oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Size = 8;
                    oSheet.get_Range("A" + rango.ToString(), "L" + rango.ToString()).Cells.Font.Name = "Times New Roman";

                    rango += 1;
                }


                oXL.Visible     = false;
                oXL.UserControl = false;

                oWB.Save();
                oWB.Close();

                oXL.ScreenUpdating   = codescreenUpdateState;
                oXL.DisplayStatusBar = statusBarState;
                oXL.EnableEvents     = eventsState;

                MessageBox.Show("El archivo se genero con exito", "Generacion de Excel",
                                MessageBoxButtons.OK, MessageBoxIcon.None);
            }
            catch (Exception err)
            {
                MessageBox.Show(err.Message, "Error al generar archivo",
                                MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
示例#4
0
 /// <summary>
 /// 另存为xls文件
 /// </summary>
 /// <param name="CurBook">Workbook</param>
 /// <param name="strFilePath">文件路径</param>
 public void Save(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
 {
     CurBook.SaveCopyAs(strFilePath);
 }
示例#5
0
        private void saveTOcsv(object sender, EventArgs e)
        {
            button1_Click(sender, e);
            // 開啟一個新的應用程式
            Excel.Application app = new Excel.Application();
            // 讓Excel文件可見
            app.Visible = true;
            // 停用警告訊息
            app.DisplayAlerts = false;
            // 加入新的活頁簿
            app.Workbooks.Add(Type.Missing);
            // 引用第一個活頁簿
            wbk = app.Workbooks[1];
            // 設定活頁簿焦點
            wbk.Activate();
            try
            {
                // 引用第一個工作表
                wst = (Excel._Worksheet)wbk.Worksheets[1];
                // 命名工作表的名稱
                wst.Name = db.Station_0001.mday;
                // 設定工作表焦點
                wst.Activate();
                app.Cells[1, 1] = "ubike retrieve data";
                // 設定metainfo
                app.Cells[1, 1] = "站點";
                app.Cells[2, 1] = "資料更新時間";
                app.Cells[3, 1] = "目前車輛數";
                app.Cells[4, 1] = "空位數量";
                app.Cells[5, 1] = "總停車格";
                // 設定第1列顏色
                wrange = wst.Range[wst.Cells[1, 1], wst.Cells[5, 1]];
                wrange.Select();
                wrange.Font.Color     = ColorTranslator.ToOle(Color.Blue);  //word color
                wrange.Interior.Color = ColorTranslator.ToOle(Color.White); //back color
                // 設定第2列資料
                for (int c = 2; c < db.Stations.Count; c++)
                {
                    app.Cells[1, c] = db.Stations[c - 2].sno;  //站點
                    app.Cells[2, c] = db.Stations[c - 2].mday; //資料更新時間
                    app.Cells[3, c] = db.Stations[c - 2].sbi;  //目前車數
                    app.Cells[4, c] = db.Stations[c - 2].bemp; //空格數
                    app.Cells[5, c] = db.Stations[c - 2].tot;  //總停車數
                }


                try
                {
                    //另存活頁簿
                    wbk.SaveAs(savePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    wbk.SaveCopyAs(db.Station_0001.mday);
                    Console.WriteLine("儲存文件於 " + Environment.NewLine + savePath);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("儲存檔案出錯,檔案可能正在使用" + Environment.NewLine + ex.Message);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("產生報表時出錯!" + Environment.NewLine + ex.Message);
            }

            //關閉活頁簿
            wbk.Close(false, Type.Missing, Type.Missing);
            //關閉Excel
            app.Quit();
            //釋放Excel資源
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            wbk    = null;
            wst    = null;
            wrange = null;
            app    = null;
            GC.Collect();
        }
示例#6
0
        private void XMLsave2excel(object sender, EventArgs e)
        {
            // 開啟一個新的應用程式
            Excel.Application app = new Excel.Application();
            // 讓Excel文件可見
            app.Visible = true;
            // 停用警告訊息
            app.DisplayAlerts = false;
            // 加入新的活頁簿
            app.Workbooks.Add(Type.Missing);
            // 引用第一個活頁簿
            wbk = app.Workbooks[1];
            // 設定活頁簿焦點
            wbk.Activate();
            try
            {
                // 引用第一個工作表
                wst = (Excel._Worksheet)wbk.Worksheets[1];
                // 命名工作表的名稱
                wst.Name = "kk citybike";
                // 設定工作表焦點
                wst.Activate();
                app.Cells[1, 1] = "city bike retrieve data";
                // 設定metainfo
                app.Cells[1, 1] = "站點";
                app.Cells[2, 1] = "目前車輛數";
                app.Cells[3, 1] = "空位數量";
                app.Cells[4, 1] = "總停車格";
                app.Cells[5, 1] = "lat,lon";
                // 設定第1列顏色
                wrange = wst.Range[wst.Cells[1, 1], wst.Cells[5, 1]];
                wrange.Select();
                wrange.Font.Color     = ColorTranslator.ToOle(Color.Blue);  //word color
                wrange.Interior.Color = ColorTranslator.ToOle(Color.White); //back color
                // 設定第2列資料
                for (int c = 2; c < bs.cbs.Count + 2; c++)
                {
                    app.Cells[1, c] = c - 1;                                                                                     //站點
                    app.Cells[2, c] = bs.cbs[c - 2].StationNums1;                                                                //資料更新時間
                    app.Cells[3, c] = Convert.ToInt32(bs.cbs[c - 2].StationNums2);                                               //目前車數
                    app.Cells[4, c] = Convert.ToInt32(bs.cbs[c - 2].StationNums1) + Convert.ToInt32(bs.cbs[c - 2].StationNums2); //空格數
                    app.Cells[5, c] = bs.cbs[c - 2].StationLat + "," + bs.cbs[c - 2].StationLon;                                 //總停車數
                }
                DateTime thisDay = DateTime.Today;
                savePath = "C:\\Users\\user\\Desktop\\rawdata\\" + "kk";
                try
                {
                    //另存活頁簿
                    wbk.SaveAs(savePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    wbk.SaveCopyAs("kk");
                    Console.WriteLine("儲存文件於 " + Environment.NewLine + savePath);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("儲存檔案出錯,檔案可能正在使用" + Environment.NewLine + ex.Message);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("產生報表時出錯!" + Environment.NewLine + ex.Message);
            }

            //關閉活頁簿
            wbk.Close(false, Type.Missing, Type.Missing);
            //關閉Excel
            app.Quit();
            //釋放Excel資源
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
            wbk    = null;
            wst    = null;
            wrange = null;
            app    = null;
            GC.Collect();
        }
示例#7
0
 /// <summary>
 /// 保存文件
 /// </summary>
 /// <param name="CurBook">Workbook</param>
 /// <param name="strFilePath">文件路径</param>
 public void Save(Excel._Workbook CurBook, string strFilePath)
 {
     CurBook.SaveCopyAs(strFilePath);
 }