C#excelテンプレートへのデータのエクスポート

15860 ワード

同前データベース Json

オープンソース共有最近のお客様は、指定されたフォーマットのExcelテンプレートにデータを直接出力し、小計を少し施し、解決します.
Excelの増行と削除行、印刷プレビュー、ヘッダー、フォント、末尾の制御が含まれています

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Windows.Forms;

namespace UFIDA.U8.UAP.QW.Common
{
    ///


    /// Excel    
    /// 
    public class ExcelHandler
    {
        
       #region      
        ///


        ///      
        /// 
        ///   {   ：0，   ：1}
        ///    
        ///     errorMsg
        public string ExportModel(int type, DataTable dt)
        {
            //      
            FolderBrowserDialog fbd = new FolderBrowserDialog();
            if (fbd.ShowDialog() != DialogResult.OK)
                return "";
            string errorMsg = string.Empty;
            string fileName = type == 0 ? "       " : "       ";
            string path = Path.GetFullPath(@"Temp\" + fileName + ".xlsx");
            string savaPath = fbd.SelectedPath;
            savaPath=savaPath.EndsWith("\\")?savaPath:savaPath+"\\";
            savaPath += fileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            //     Microsoft.Office.Interop.Excel   
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            try
            {
                app.Visible = false;
                app.UserControl = true;
                Microsoft.Office.Interop.Excel.Workbooks workbooks = app.Workbooks;
                Microsoft.Office.Interop.Excel._Workbook workbook = workbooks.Add(path); //    
                Microsoft.Office.Interop.Excel.Sheets sheets = workbook.Sheets;
                Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1); //      。
                if (worksheet == null)
                {
                    errorMsg = "         ";
                    return errorMsg;
                }
                int rowIndex = 7;
                //    ，Excel   1  。
                //foreach (DataRow row in dt.Rows)
                //{
                if (rowIndex ==7)
                {
                    #region head
                    worksheet.Cells[2, 2] = "    1";//row["    "].ToStr();
                    worksheet.Cells[3, 2] = "    1";//row["    "].ToStr();

                    #endregion

                    #region bottom

                    #endregion
                }

                #region body
                worksheet.Cells[rowIndex, 1] = "    1";//row["    "].ToStr();
                worksheet.Cells[rowIndex, 2] = "    1";//row["    "].ToStr();
                worksheet.Cells[rowIndex, 3] = 1001.0000;//row["    "].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 4] = 1002.0000;//row["    "].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 5] = 1003.0000;//row["    "].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 6] = 1004.0000;//row["    "].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 7] = 1005.0000;//row["  "].ToStr().ToDouble();
                worksheet.Cells[rowIndex, 8] = 1006.0000;//row["    "].ToStr().ToDouble();
                #endregion

                rowIndex++;
                InsertRows(worksheet, rowIndex);

                //}

                //  Excel   。
                //Microsoft.Office.Interop.Excel.Range rg = worksheet.Cells.get_Range("A3", worksheet.Cells[dt.Rows.Count + 2, 8]);
                //rg.Borders.LineStyle = 1; //      。
                worksheet.Columns.AutoFit(); //      。
                //Missing  System.Reflection     。
                workbook.SaveAs(savaPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                //workbook.PrintPreview(true);//    ，   app.Visible = true,     ;
            }
            catch (Exception ex)
            {
                errorMsg += ex.Message;
            }
            finally
            {
                //       
                app.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
                GC.Collect();
            }
            return errorMsg;
        }
        ///


        ///         ，              
        /// 
        ///    
        ///    
        private void InsertRows(Microsoft.Office.Interop.Excel._Worksheet sheet, int rowIndex)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, missing];
            //object   Range.Insert(object   shift,   object   copyorigin);     
            //shift:   Variant  ，  。          。        XlInsertShiftDirection       ：   
            //xlShiftToRight       xlShiftDown。       ，Microsoft   Excel                。   
            range.Insert(Microsoft.Office.Interop.Excel.XlInsertShiftDirection.xlShiftDown, missing);
        }

        ///


        ///            
        /// 
        ///    
        ///    
        private void DeleteRows(Microsoft.Office.Interop.Excel.Worksheet sheet, int rowIndex)
        {
            Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)sheet.Rows[rowIndex, missing];
            range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp);
        }

        #endregion
    }
}

転載先:https://www.cnblogs.com/bingle/p/3716128.html

Android開発(41)ネットワークアクセスクラスライブラリVolley

JSON文字列をJSONオブジェクトに変換