C#下面調用 Excel 另存代碼

來源:互聯網
上載者:User
excel

private void btnSaveAsData_Click(object sender, EventArgs e)
        {
            Excel.Application xlsApp = new Excel.Application();
            Excel._Workbook xlsBook = xlsApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel._Worksheet xlsSheet = (Excel._Worksheet)xlsBook.Worksheets[1];
            xlsSheet.Activate();

            xlsApp.Caption = lblDMMC.Text + "斷面實測成果表 - 另存新檔 ";

            DataTable myDT1, myDT2;

            myDT1 = datatable(a,a1);
            myDT2 = datatable(a,a1);
           double minGaocheng = 0;//Get_min(a1);

            string[] strColName = new string[] { "起點距m", "河底高程m", "起點距m", "河底高程m", "起點距m", "河底高程m", "起點距m", "河底高程m", "起點距m", "河底高程m", "起點距m", "河底高程m", "" };
            int intCols = strColName.Length - 1;
            int intRows = myDT1.Rows.Count;

            int[] intRows1 = new int[myDT1.Rows.Count];
            int[] intcols1 = new int[myDT1.Columns.Count];

            int minrow = 0;
            int mincol = 0;
            int zhanyonghang = 3;
            xlsSheet.Cells[2, 7] = lblDMMC.Text + "斷面實測成果表";
            xlsSheet.Cells[zhanyonghang+2, intCols + 1] = "制";
            xlsSheet.Cells[zhanyonghang + 3, intCols + 1] = "表";
            xlsSheet.Cells[zhanyonghang + 4, intCols + 1] = ":";
            xlsSheet.Cells[zhanyonghang + 7, intCols + 1] = "一";
            xlsSheet.Cells[zhanyonghang + 8, intCols + 1] = "校";
            xlsSheet.Cells[zhanyonghang + 9, intCols + 1] = ":";
            xlsSheet.Cells[zhanyonghang + 12, intCols + 1] = "二";
            xlsSheet.Cells[zhanyonghang + 13, intCols + 1] = "校";
            xlsSheet.Cells[zhanyonghang + 14, intCols + 1] = ":";
            xlsSheet.Cells[zhanyonghang + 17, intCols + 1] = "驗";
            xlsSheet.Cells[zhanyonghang + 18, intCols + 1] = "收";
            xlsSheet.Cells[zhanyonghang + 19, intCols + 1] = ":";
            xlsSheet.Cells[zhanyonghang + 1, 3] = "施測日期:" + dtpS3.Text;
            xlsSheet.Cells[zhanyonghang + 1, 7] = "測探工具:測探杆 ";
            xlsSheet.Cells[zhanyonghang + 1, 10] = "水位:" + txtS4.Text + "m";
            // xlsSheet.Cells[1, intCols] = "製表:";
            for (int j = 0; j < intCols; j++)
            {
                xlsSheet.Cells[zhanyonghang + 2, j + 1] = strColName[j];
            }
            int tempi = 1;

            for (int i = 0; i < intRows; i++)
            {
                for (int j = 0; j < intCols; j++)
                {
                    if (i == temprows2 && j == tempcols2)
                    {
                        myDT1.Rows[i][j] = myDT1.Rows[i][j].ToString() + "▲";
                    }

                    if (i == temprows1 && j == tempcols1)
                    {
                        myDT1.Rows[i][j] = myDT1.Rows[i][j].ToString() + "▼";
                    }
                    xlsApp.Cells[zhanyonghang + i + 3, j + 1] = myDT1.Rows[i][j];
                }
            }

            xlsSheet.Cells[zhanyonghang + intRows + 3,6] = "附註:" + txtFuzhu.Text;
            xlsSheet.Columns.ColumnWidth = 9;
            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, intCols + 1], xlsSheet.Cells[zhanyonghang + intRows + 2, intCols + 1]).ColumnWidth = 3;

            xlsSheet.Rows.RowHeight = 18;
            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, 1], xlsSheet.Cells[zhanyonghang + 2, intCols]).RowHeight = 15;

            xlsSheet.Cells.Font.Name = "宋體";
            xlsSheet.Cells.Font.Size = 10;
            xlsSheet.Cells.Font.Bold = false;
            xlsSheet.Cells.WrapText = false;
            xlsSheet.get_Range(xlsSheet.Cells[ 2, 1], xlsSheet.Cells[ 2, intCols]).Font.Size = 15;

            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, 1], xlsSheet.Cells[zhanyonghang + 2, intCols]).Font.Name = "宋體";
            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, 1], xlsSheet.Cells[zhanyonghang + 2, intCols]).Font.Size = 10;
            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, 1], xlsSheet.Cells[zhanyonghang + 2, intCols]).Font.Bold = true;
            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, 1], xlsSheet.Cells[zhanyonghang + 2, intCols]).WrapText = false;

            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + temprows3 + 3, tempcols3], xlsSheet.Cells[zhanyonghang + temprows3 + 3, tempcols3 + 1]).Font.Underline = true;  

            for (int i = 0; i < myDT2.Rows.Count; i++)
            {
                for (int j = 0; j < myDT2.Columns.Count; j++)
                {
                    double temp1;
                    if (myDT2.Rows[i][j].ToString() == "")
                    {
                        temp1 = 0.123456;
                    }
                    else
                    {
                        temp1 = Convert.ToDouble(myDT2.Rows[i][j].ToString());
                    }

                    if (j % 2 == 0)
                    {
                        if (temp1 == 0)
                        {
                            xlsSheet.Cells[zhanyonghang + i + 3, j + 1] = 0;
                        }
                        else if (temp1 == 0.123456)
                        {
                            xlsSheet.Cells[i + 3, j + 1] = "";
                        }
                        else
                        {
                            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + i + 3, j + 1], xlsSheet.Cells[zhanyonghang + i + 3, j + 1]).NumberFormatLocal = "#####";
                        }
                    }
                    else
                    {
                        if (temp1 > 9)
                        {
                            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + i + 3, j + 1], xlsSheet.Cells[zhanyonghang + i + 3, j + 1]).NumberFormatLocal = "#####.0";
                        }
                    }
                }
            }
            // xlsSheet.get_Range(xlsSheet.Cells[3, 6], xlsSheet.Cells[3, 6]).NumberFormatLocal = "#####.0";
            //xlsSheet.get_Range(xlsSheet.Cells[3, 4], xlsSheet.Cells[intRows + 2, 4]).NumberFormatLocal = "#####.0";
            //xlsSheet.get_Range(xlsSheet.Cells[3, 6], xlsSheet.Cells[intRows + 2, 6]).NumberFormatLocal = "#####.0";
            //xlsSheet.get_Range(xlsSheet.Cells[3, 8], xlsSheet.Cells[intRows + 2, 8]).NumberFormatLocal = "#####.0";
            //xlsSheet.get_Range(xlsSheet.Cells[3, 10], xlsSheet.Cells[intRows + 2, 10]).NumberFormatLocal = "#####.0";
            //xlsSheet.get_Range(xlsSheet.Cells[3, 12], xlsSheet.Cells[intRows + 2, 12]).NumberFormatLocal = "#####.0";

            xlsSheet.Columns.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
            xlsSheet.Columns.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 1, intCols], xlsSheet.Cells[zhanyonghang + 1, intCols]).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;

            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, 1], xlsSheet.Cells[zhanyonghang + intRows + 2, intCols]).Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            xlsSheet.get_Range(xlsSheet.Cells[zhanyonghang + 2, 1], xlsSheet.Cells[zhanyonghang + intRows + 2, intCols]).Borders.Weight = Excel.XlBorderWeight.xlThin;

            string FileName = xlsApp.GetSaveAsFilename("斷面實測成果表 .xls", "Excel檔案 (*.xls), *.xls", 1, "斷面實測成果表", "斷面實測成果表").ToString().Trim();
            if (FileName != "False" && FileName != "")
            {
                xlsSheet.SaveAs(FileName, Excel.XlFileFormat.xlExcel9795, "", "", false, false, false, 1, 1, true);
            }
            xlsBook.Saved = true;
            xlsApp.Workbooks.Close();
            xlsApp.Quit();
        } 



相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.