C# Worksheet类代码示例

        public static void CreateResultsWorksheet(DataSet Schedule)
        {
            // Frequenty-used variable for optional arguments.
            object m_objOpt = System.Reflection.Missing.Value;

            Application xlApp = new Application();
            Worksheet ws = new Worksheet();
            int outRows, outCols;

            //Rows and columns are transposed for printing
            outCols = Schedule.Tables[0].Columns.Count;
            outRows = Schedule.Tables[0].Rows.Count + 1; //leave roof for row and column headers

            object[,] OutputArray = new object[outRows, outCols];
            ////Note Excel Arrays are 1-based, not 0-based
            for (int i = 0; i < outCols; i++)
            {
                //Put the titles into the spreadsheet.
                OutputArray[0, i] = Schedule.Tables[0].Columns[i].ColumnName;
            }

            for (int i = 1; i < outRows; i++)
            {
                for (int j = 0;j < outCols; j++)
                {
                    DataRow dr = Schedule.Tables[0].Rows[i - 1];
                    OutputArray[i, j] = dr[j];
                }
            }

            //Create a workbook and add a worksheet named "Schedule Results"
            xlApp.Workbooks.Add(m_objOpt);
            ws = (Worksheet) xlApp.Workbooks[1].Worksheets[1];
            ws.Name = "Schedule Results";

            Range r = ws.get_Range(ws.Cells[1,1],ws.Cells[outRows, outCols]);
            //put the output array into Excel in one step.
            r.Value2=OutputArray;

            //select the title columns and make them bold
            r = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, outCols + 1]);
            r.Font.Bold = true;
            r.Font.Size = 14;

            //format any DateTime Columns
            for (int i = 0; i<outCols; i++)
            {
                if (Schedule.Tables[0].Columns[i].DataType.ToString() == "System.DateTime" )
                {
                    r = ws.get_Range(ws.Cells[1, i+1], ws.Cells[outRows + 1, i+1]);
                    r.NumberFormat = "[$-409]m/d/yy h:mm AM/PM;@";
                }
            }

            //Select the entire spreadsheet and autofit the contents
            r = ws.get_Range(ws.Cells[1, 1], ws.Cells[outRows + 1, outCols + 1]);
            r.Columns.AutoFit();
            xlApp.Visible = true;
            xlApp = null;
        }

 public static void EnsureColumn(Worksheet worksheet, uint columnIndex)
 {
     var columns = worksheet.Elements<Columns>().FirstOrDefault();
     if (columns == null)
     {
         columns = worksheet.InsertAt(new Columns(), 0);
     }
     if (columns.Elements<Column>().Where(item => item.Min == columnIndex).Count() == 0)
     {
         Column previousColumn = null;
         for (uint counter = columnIndex - 1; counter > 0; counter--)
         {
             previousColumn = columns.Elements<Column>().Where(item => item.Min == counter).FirstOrDefault();
             if (previousColumn != null)
             {
                 break;
             }
         }
         columns.InsertAfter(new Column()
            {
                Min = columnIndex,
                Max = columnIndex,
                CustomWidth = true,
                Width = 9
            }, previousColumn);
     }
 }

 public IEnumerable<ExpandoObject> GetObjects(Worksheet worksheet, ISheetDefinition sheetDefinition)
 {
     if (worksheet == null) throw new ArgumentNullException("worksheet");
     if (sheetDefinition == null) throw new ArgumentNullException("sheetDefinition");
     var rows = worksheet.Descendants<Row>().Skip(1);
     return rows.Select(x => ParseFromRow(x, sheetDefinition));
 }

 private static void fillSection(SvodDdr.DdrDataContext dc, Worksheet ws, List<SourceData> sqlData, List<SourceData> usedData, int minRow, int maxRow)
 {
     for (int rowIndex = minRow; rowIndex <= maxRow; rowIndex++)
     {
         fillRow(dc, rowIndex, ws, sqlData, usedData);
     }
 }

        /// <summary>
        /// 取得欄位驗證字串
        /// </summary>
        /// <returns></returns>
        public static string GetChekcDataStr(int idx, Worksheet wst, Dictionary<string, int> ColIndexDic)
        {
            string chkStr = string.Empty;
            if (ColIndexDic.ContainsKey("學號"))
                chkStr += wst.Cells[idx, ColIndexDic["學號"]].StringValue;

            if (ColIndexDic.ContainsKey("姓名"))
                chkStr += wst.Cells[idx, ColIndexDic["姓名"]].StringValue;

            //if (ColIndexDic.ContainsKey("學年度"))
            //    chkStr += wst.Cells[idx, ColIndexDic["學年度"]].StringValue;

            //if (ColIndexDic.ContainsKey("學期"))
            //    chkStr += wst.Cells[idx, ColIndexDic["學期"]].StringValue;

            if (ColIndexDic.ContainsKey("異動日期"))
                chkStr += wst.Cells[idx, ColIndexDic["異動日期"]].StringValue;

            if (ColIndexDic.ContainsKey("異動代碼"))
                chkStr += wst.Cells[idx, ColIndexDic["異動代碼"]].StringValue;

            if (ColIndexDic.ContainsKey("原因及事項"))
                chkStr += wst.Cells[idx, ColIndexDic["原因及事項"]].StringValue;
            return chkStr;
        }

        public void Test()
        {
            Workbook workbook = new Workbook();
            Worksheet worksheet = new Worksheet("Test");
            string styleId = workbook.Styles.Add(new Style
                                                     {
                                                         Alignment = new Alignment
                                                                         {
                                                                             Horizontal = "Center"
                                                                         }

                                                     });
            for (int i = 1; i < 10; i++)
            {
                for (int j = 1; j <= i; j++)
                {
                    string str = string.Format("{0} X {1} = {2}", j, i, i*j);
                    worksheet.Tables.Columns[i].Width = 65;
                    worksheet.Tables.Rows[i].Cells[j] = new Cell(str)
                                                            {
                                                                StyleId = styleId
                                                            };
                }
            }
            workbook.WorkSheets.Add(worksheet);
            _excel.Workbooks.Add(workbook);
            workbook.Save("c:\\aa.xml");
        }

 public SheetViews(Worksheet worksheet, bool selected, int frozenRow, int frozenColumn)
     : this(worksheet)
 {
     Selected = selected;
     FrozenRow = frozenRow;
     FrozenColumn = frozenColumn;
 }

        public static void SaveExcelFile(string fileName, string tempFileName,string[,] contents, int startColumn, int startRow)
        {
            try
            {
                excelApp = new ApplicationClass();
                Workbooks myWorkBooks = excelApp.Workbooks;
                myWorkBooks.Open(tempFileName, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                Sheets sheets = excelApp.Sheets;
                mySheet1 = (Worksheet)sheets[1];
                mySheet1.Activate();

                //写入测试信息
                Range range1 = mySheet1.get_Range(mySheet1.Cells[startRow, startColumn], mySheet1.Cells[contents.GetLength(0)+startRow-1,contents.GetLength(1)+startColumn-1]);
                range1.Value2 = contents;
                mySheet1.SaveAs(fileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                myWorkBooks.Close();
                excelApp.Quit();
                excelApp = null;
            }
            catch (Exception ee)
            {

                throw ee;
            }
        }

        private void setInDTO(Worksheet sheet, HcServiceInfo serviceInfo, ref int irow)
        {
            var inDto = new HcDTOInfo();
            inDto.Caption = serviceInfo.Caption + "的InDTO";

            var range = (Range)sheet.Cells[19, 2];
            inDto.Name = range.Value.ToString();

            range = (Range)sheet.Cells[20, 15];
            var cellValue = range.Value;
            inDto.FieldArray = new List<HcFieldInfo>();
            while (cellValue != null && !string.IsNullOrEmpty(cellValue.ToString()))
            {
                var field = new HcFieldInfo();
                field.name = cellValue.ToString();
                range = (Range)sheet.Cells[irow, 3];
                field.caption = range.Value.ToString();
                range = (Range)sheet.Cells[irow, 22];
                field.FieldTypeString = range.Value.ToString();

                inDto.FieldArray.Add(field);
                irow += 1;

                range = (Range)sheet.Cells[irow, 15];
                cellValue = range.Value;
            }

            serviceInfo.InDTO = inDto;
        }

        public bool CheckValidTemplate(string username, string language_id, string store_procedure, string file_name, string module_id, string function_id, Worksheet ws)
        {
            bool result = false;
            DataRow dr = db.GetDataRow("SYS_spfrmImportFileConfig", new string[] { "Activity", "Username", "LanguageID", "ExcelFile", "FunctionID", "ModuleID" }, new object[] { "CheckValidTemplate", username, language_id, file_name, function_id, module_id });
            if (dr != null)
                result = true;
            else
            {
                DataTable dt = db.GetDataTable("SYS_spCommon", new string[] { "ObjectName" }, new object[] { store_procedure });
                if (dt != null && dt.Rows.Count > 0)
                {
                    int count = 0;
                    for (int i = 0; i < ws.Cells.MaxColumn; i++)
                    {
                        if (string.IsNullOrEmpty(ws.Cells[1, i].Value + ""))
                            break;
                        string tmp = ws.Cells[1, i].Value.ToString().ToLower().Trim();
                        if (string.IsNullOrEmpty(tmp))
                            break;
                        if (tmp != "stt" && tmp != "$hidecolumn$" && tmp != "$deletecolumn$")
                        {
                            DataRow[] rows = dt.Select("ColumnName='@" + tmp + "'");
                            if (rows.Length == 0) count++;
                        }
                    }
                    result = (count < 3);
                }
                else result = false;
            }

            return result;
        }

        internal ExcelWorksheet(Worksheet worksheet, ExcelDocument parent)
        {
            IsDisposed = false;

            Worksheet = worksheet;
            Parent = parent;
        }

 public Export(bool defaultBackgroundIsWhite)
 {
     app = new Application();
     app.Visible = true;
     workbook = app.Workbooks.Add(1);
     worksheet = (Worksheet)workbook.Sheets[1];
 }

        //Open Excel file
        public int FnOpenExcel(string sPath, string iSheet)
        {

            int functionReturnValue = 0;
            try
            {

                application = new Microsoft.Office.Interop.Excel.Application { Visible = true }; //Microsoft.Office.Interop.Excel.Application();
                workbook = application.Workbooks.Open(sPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                // get all sheets in workbook
                //worksheet = (Worksheet) workbook.Worksheets;

                // get some sheet
                //string currentSheet = "Main Invoice";
                worksheet = (Worksheet) workbook.Worksheets["" + iSheet + ""];
                // Get the active sheet
                worksheet = (Worksheet)workbook.ActiveSheet;
                functionReturnValue = 0;
            }
            catch (Exception ex)
            {
                functionReturnValue = -1;
                MessageBox.Show(ex.Message);
            }
            return functionReturnValue;
        }

        public DataGridViewExport(DataGridView dgv)
        {
            _workbook = new Workbook();
            _workbook.Worksheets.Clear();
            _worksheet = _workbook.Worksheets[_workbook.Worksheets.Add()];
            _worksheet.Name = "Sheet1";
            _colIndexes = new List<int>();

            int sheetRowIndex = 0;
            int sheetColIndex = 0;
            foreach (DataGridViewColumn col in dgv.Columns)
            {
                if (col.Visible == false) continue;
                _colIndexes.Add(col.Index);
                _worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue(col.HeaderText);
            }

            foreach (DataGridViewRow row in dgv.Rows)
            {
                sheetRowIndex++;
                sheetColIndex = 0;
                foreach (int colIndex in _colIndexes)
                    _worksheet.Cells[sheetRowIndex, sheetColIndex++].PutValue("" + row.Cells[colIndex].Value);
            }

            _worksheet.AutoFitColumns();
        }

        /// <summary>
        /// 添加Excel头部
        /// </summary>
        /// <param name="dt"></param>
        private void AddHeader(DataTable dt, Worksheet sheetNew = null, Hashtable ht = null)
        {
            if (sheetNew == null)
            {
                sheetNew = sheet;
            }
            Cell cell = null;

            for (int col = 0; col < dt.Columns.Count; col++)
            {
                cell = sheetNew.Cells[0, col];
                if (ht == null)
                {
                    cell.PutValue(dt.Columns[col].ColumnName);
                }
                else
                {
                    string name = ht[dt.Columns[col].ColumnName].ToString() ?? dt.Columns[col].ColumnName;
                    cell.PutValue(name);
                }
                Style style = new Style();
                style.Font.IsBold = true;
                cell.SetStyle(style);
            }
        }