Excel工作表的页面设置在工作簿中不起作用

我试图将不同的数据放入同一工作簿中的不同工作表中,每个工作表都有不同的页面设置(如页眉,页脚等)。

我试图将不同的数据放入同一工作簿中的不同工作表中,每个工作表都有不同的页面设置(如页眉,页脚等)。

当我执行程序时,数据可以成功地显示在不同的工作表中,但是当我检查打印预览时,页面设置不起作用(如页眉和页脚丢失),有人知道原因吗?

波纹管是按钮点击事件:

private void btnExportAllToExcel_Click(object sender, EventArgs e)
    {
        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Filter = "Excel Documents (*.xls)|*.xls";
        sfd.FileName = "StockReport(ALL)_" + DateTime.Now.ToString("ddMMyyyy HHmmss") + ".xls";
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            Cursor = Cursors.WaitCursor; // change cursor to hourglass type
            object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
            xlexcel.PrintCommunication = false;
            xlexcel.ScreenUpdating = false;
            xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
            Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
            saveDataToSheet(xlWorkBook);
            xlexcel.Calculation = XlCalculation.xlCalculationManual;
            //Save the excel file under the captured location from the SaveFileDialog
            xlWorkBook.SaveAs(sfd.FileName, XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlexcel.DisplayAlerts = true;
            xlWorkBook.Close(true, misValue, misValue);
            xlexcel.Quit();
            releaseObject(xlWorkBook);
            releaseObject(xlexcel);
            // Clear Clipboard and DataGridView selection
            Clipboard.Clear();
            dgvStockReport.ClearSelection();
            // Open the newly saved excel file
            if (File.Exists(sfd.FileName))
                System.Diagnostics.Process.Start(sfd.FileName);
        }
    }

bellow 是将数据从 datagridview 复制到 sheet 方法的代码,我认为问题将在这里,但我找不到它:

 //copy data from datagridview to clipboard and paste to excel sheet
    private void saveDataToSheet(Workbook xlWorkBook)
    {
        int sheetNo = 1;
        Worksheet xlWorkSheet = null;
        bool gotData = false;
        //load different data list to datagridview by changing the comboBox selected index
        for (int i = 0; i <= cmbType.Items.Count - 1; i++)
        {
            cmbType.SelectedIndex = i;
            for (int j = 0; j <= cmbSubType.Items.Count - 1; j++)
            {
                cmbSubType.SelectedIndex = j;
                if (cmbType.Text.Equals(CMBPartHeader))
                {
                    gotData = loadPartStockData();//if data != empty return true, else false
                }
                else if (cmbType.Text.Equals(CMBMaterialHeader))
                {
                    gotData = loadMaterialStockData();//if data != empty return true, else false
                }
                if(gotData)//if datagridview have data
                {
                    copyAlltoClipboard();//select all from datagridview and copy to clipboard
                    //create new sheet
                    var xlSheets = xlWorkBook.Sheets as Sheets;
                    var xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[sheetNo], Type.Missing, Type.Missing, Type.Missing);
                    xlWorkSheet = xlNewSheet;
                    xlWorkSheet = (Worksheet)xlWorkBook.Worksheets.get_Item(sheetNo);
                    xlWorkSheet.Name = cmbSubType.Text;
                    //Header and Footer setup
                    xlWorkSheet.PageSetup.LeftHeader = "&\"Calibri,Bold\"&11 " + DateTime.Now.Date.ToString("dd/MM/yyyy"); ;
                    xlWorkSheet.PageSetup.CenterHeader = "&\"Calibri,Bold\"&16 (" + cmbSubType.Text + ") Stock List";
                    xlWorkSheet.PageSetup.RightHeader = "&\"Calibri,Bold\"&11 PG -&P";
                    xlWorkSheet.PageSetup.CenterFooter = "Printed By " + dalUser.getUsername(MainDashboard.USER_ID);
                    //Page setup
                    xlWorkSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
                    xlWorkSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
                    xlWorkSheet.PageSetup.Zoom = false;
                    xlWorkSheet.PageSetup.CenterHorizontally = true;
                    xlWorkSheet.PageSetup.LeftMargin = 1;
                    xlWorkSheet.PageSetup.RightMargin = 1;
                    xlWorkSheet.PageSetup.FitToPagesWide = 1;
                    xlWorkSheet.PageSetup.FitToPagesTall = false;
                    xlWorkSheet.PageSetup.PrintTitleRows = "$1:$1";
                    // Paste clipboard results to worksheet range
                    xlWorkSheet.Select();
                    Range CR = (Range)xlWorkSheet.Cells[1, 1];
                    CR.Select();
                    xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
                    //content edit
                    Range tRange = xlWorkSheet.UsedRange;
                    tRange.Borders.LineStyle = XlLineStyle.xlContinuous;
                    tRange.Borders.Weight = XlBorderWeight.xlThin;
                    tRange.Font.Size = 11;
                    tRange.EntireColumn.AutoFit();
                    tRange.Rows[1].interior.color = Color.FromArgb(237, 237, 237);//change first row back color to light grey
                    sheetNo++;
                    // Clear Clipboard and DataGridView selection
                    Clipboard.Clear();
                    dgvStockReport.ClearSelection();
                    releaseObject(xlWorkSheet);
                }  
            }
        }
    }

我是新来的,编码也是如此,所以如果我做错了什么,请告诉我,非常感谢 ^ ^

0

经过在线研究和修改我的代码约 4 小时,现在它的工作!

我尝试先保存工作簿,然后打开它以将数据插入工作表并更改页面设置。

CODE:
private void btnExportAllToExcel_Click(object sender, EventArgs e)
    {
        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Filter = "Excel Documents (*.xls)|*.xls";
        sfd.FileName = "StockReport(ALL)_" + DateTime.Now.ToString("ddMMyyyy_HHmmss") + ".xls";
        if (sfd.ShowDialog() == DialogResult.OK)
        {
            string path = Path.GetFullPath(sfd.FileName);
            Cursor = Cursors.WaitCursor; // change cursor to hourglass type
            object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Application xlexcel = new Microsoft.Office.Interop.Excel.Application();
            xlexcel.PrintCommunication = false;
            xlexcel.ScreenUpdating = false;
            xlexcel.DisplayAlerts = false; // Without this you will get two confirm overwrite prompts
            Workbook xlWorkBook = xlexcel.Workbooks.Add(misValue);
            //Save the excel file under the captured location from the SaveFileDialog
            xlWorkBook.SaveAs(sfd.FileName, 
                XlFileFormat.xlWorkbookNormal, 
                misValue, misValue, misValue, misValue, 
                XlSaveAsAccessMode.xlExclusive, 
                misValue, misValue, misValue, misValue, misValue);
            insertDataToSheet(path,sfd.FileName);
            xlexcel.DisplayAlerts = true;
            xlWorkBook.Close(true, misValue, misValue);
            xlexcel.Quit();
            releaseObject(xlWorkBook);
            releaseObject(xlexcel);
            // Clear Clipboard and DataGridView selection
            Clipboard.Clear();
            dgvStockReport.ClearSelection();
        }
    }

这里是将数据插入工作表方法的代码:

 private void insertDataToSheet(string path, string fileName)
    {
        Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
        excelApp.Visible = true;
        Workbook g_Workbook = excelApp.Workbooks.Open(
            path,
            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);
        bool gotData = false;
        object misValue = System.Reflection.Missing.Value;
        //load different data list to datagridview but changing the comboBox selected index
        for (int i = 0; i <= cmbType.Items.Count - 1; i++)
        {
            cmbType.SelectedIndex = i;
            for (int j = 0; j <= cmbSubType.Items.Count - 1; j++)
            {
                cmbSubType.SelectedIndex = j;
                if (cmbType.Text.Equals(CMBPartHeader))
                {
                    gotData = loadPartStockData();//if data != empty return true, else false
                }
                else if (cmbType.Text.Equals(CMBMaterialHeader))
                {
                    gotData = loadMaterialStockData();//if data != empty return true, else false
                }
                if (gotData)//if datagridview have data
                {
                    Worksheet addedSheet = null;
                    int count = g_Workbook.Worksheets.Count;
                    addedSheet = g_Workbook.Worksheets.Add(Type.Missing,
                            g_Workbook.Worksheets[count], Type.Missing, Type.Missing);
                    addedSheet.Name = cmbSubType.Text;
                    addedSheet.PageSetup.LeftHeader = "&\"Calibri,Bold\"&11 " + DateTime.Now.Date.ToString("dd/MM/yyyy"); ;
                    addedSheet.PageSetup.CenterHeader = "&\"Calibri,Bold\"&16 (" + cmbSubType.Text + ") STOCK LIST";
                    addedSheet.PageSetup.RightHeader = "&\"Calibri,Bold\"&11 PG -&P";
                    addedSheet.PageSetup.CenterFooter = "Printed By " + dalUser.getUsername(MainDashboard.USER_ID);
                    //Page setup
                    addedSheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4;
                    addedSheet.PageSetup.Orientation = XlPageOrientation.xlPortrait;
                    addedSheet.PageSetup.Zoom = false;
                    addedSheet.PageSetup.CenterHorizontally = true;
                    addedSheet.PageSetup.LeftMargin = 1;
                    addedSheet.PageSetup.RightMargin = 1;
                    addedSheet.PageSetup.FitToPagesWide = 1;
                    addedSheet.PageSetup.FitToPagesTall = false;
                    addedSheet.PageSetup.PrintTitleRows = "$1:$1";
                    copyAlltoClipboard();
                    addedSheet.Select();
                    Range CR = (Range)addedSheet.Cells[1, 1];
                    CR.Select();
                    addedSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
                    Range tRange = addedSheet.UsedRange;
                    tRange.Borders.LineStyle = XlLineStyle.xlContinuous;
                    tRange.Borders.Weight = XlBorderWeight.xlThin;
                    tRange.Font.Size = 11;
                    tRange.EntireColumn.AutoFit();
                    tRange.Rows[1].interior.color = Color.FromArgb(237, 237, 237);//change first row back color to light grey
                    Clipboard.Clear();
                    dgvStockReport.ClearSelection();
                }
            }
        }
        g_Workbook.Worksheets.Item[1].Delete();
        g_Workbook.Save();
    }

本站系公益性非盈利分享网址,本文来自用户投稿,不代表码文网立场,如若转载,请注明出处

(62)
使用 JavaScript比较两个日期
上一篇
Logstash管道推送数据而不将其转换为 Elasticsearch
下一篇

相关推荐

发表评论

登录 后才能评论

评论列表(37条)