Some people cry but some people try ...... P & Q

제목: Microsoft SQL Server Management Studio

------------------------------


서버 '[컴퓨터 이름]'에 대한 데이터베이스 연결이(가) 실패했습니다.  (Microsoft.SqlServer.Smo)


도움말을 보려면 다음을 클릭하십시오: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=데이터베이스+연결+Server&LinkId=20476


------------------------------

추가 정보:


Transact-SQL 문 또는 일괄 처리를 실행하는 동안 예외가 발생했습니다. (Microsoft.SqlServer.ConnectionInfo)


------------------------------


데이터베이스 '[DB명]'은(는) 해당 버전이 706이므로 열 수 없습니다. 이 서버는 버전 661 및 이전 버전을 지원합니다. 다운그레이드 경로는 지원되지 않습니다.

새 데이터베이스 '[DB명]'을(를) 열 수 없습니다. CREATE DATABASE가 중단됩니다. (Microsoft SQL Server, 오류: 948)


도움말을 보려면 다음을 클릭하십시오: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=948&LinkId=20476


------------------------------



서버쪽은 64비트 SQL Server 2012 R2 가 설치되어 있고 프로그램에서 MST 라는 DB를 생성하여 데이터 압축 후 SQL Express 2008 사용하는 PC 에서 DB 연결하려고 할때 


"데이터베이스 'MST'은(는) 해당 버전이 706이므로 열 수 없습니다. 이 서버는 버전 661 및 이전 버전을 지원합니다. 다운그레이드 경로는 지원되지 않습니다."


이라고 PC 쪽에서 에러가 발생됩니다.

DB Compatibility Level 이 문제라고 하여 90으로 호환성을 낮추어도 위와 같습니다.

다른 어떤 문제가 있을까요??


 

 

댓글
2016.11.25 10:36:13 (*.6.129.207)
[레벨:37]Terry

말 그대로 DB 자체가 지원이 안되는겁니다.

상위호환이 안되는거죠..^^;;


2가지중 편하신걸로 처리하시면 될듯..


1안) SQL EXPRESS 2008 -> SQL EXPRESS 2012 로 변경

2안) SQL 2012 R2 의 DB -> 스크립트로 EXPORT 하여 , SQL 2008 SSMS에서 해당스크립트 실행


2안의 경우

하기 링크 참고하세요..


http://www.sqler.com/index.php?mid=bSQLQA&search_target=comment_userid&search_keyword=chy18&document_srl=851611


수고하세요~


Comment +0

사용 하면는 Const 문을 상수를 선언 하 고 해당 값을 설정 합니다. 상수를 선언 하 여 값으로 의미 있는 이름을 할당 합니다. 상수 선언 되 면 수정 또는 새 값을 지정할 수 없습니다.

프로시저 내에서 또는 모듈, 클래스 또는 구조체의 선언 섹션에는 상수를 선언합니다. 클래스 또는 구조 수준 상수는 Private 기본적으로도로 선언할 수 있지만 PublicFriendProtected, 또는 Protected Friend 적절 한 수준의 코드 액세스에 대 한 합니다.

올바른 심볼 이름을 (규칙은 변수 이름 만들기와 동일)와 숫자 또는 문자열 상수 및 연산자 (함수 호출 없음)의 구성 된 식의 상수 있어야 합니다.

참고

일부 Visual Studio 사용자 인터페이스 요소의 경우 다음 지침에 설명된 것과 다른 이름 또는 위치가 시스템에 표시될 수 있습니다. 이러한 요소는 사용하는 Visual Studio 버전 및 설정에 따라 결정됩니다. 자세한 내용은 IDE 개인 설정을 참조하세요.

상수 선언 하려면

  • 액세스 지정자를 포함 하는 선언을 작성 된 Const 키워드 및 다음 예와 같이 식:

    VB
    Public Const DaysInYear = 365
    Private Const WorkDays = 250
    

     Option Infer  Off  Option Strict  On, 데이터 형식을 지정 하 여 명시적으로 상수를 선언 해야 합니다 (Boolean, Byte, Char, DateTime, Decimal, Double, Integer, Long, Short, Single, 또는 String).

     Option Infer  On 또는 Option Strict  Off, 데이터 형식으로 지정 하지 않고 상수를 선언할 수 있습니다는 As 절. 컴파일러에는 상수 식의 형식에서의 유형을 결정합니다. 자세한 내용은 참조 상수 및 리터럴 데이터 형식합니다.

으로 명시적으로 지정된 하는 데이터 형식이 지정 된 상수를 선언 하려면

  • 포함 하는 선언을 작성는 As 키워드 및 명시적 데이터 형식, 다음 예제와 같이:

    VB
    Public Const MyInteger As Integer = 42
    Private Const DaysInWeek As Short = 7
    Protected Friend Const Funday As String = "Sunday"
    

    코드 줄에 하나씩 단일 상수만 선언 하는 경우 더 쉽게 읽을 수는 있지만 한 줄에 여러 개의 상수를 선언할 수 있습니다. 한 줄에 여러 개의 상수를 선언 하는 경우 이들은 모두 가져야 동일한 액세스 레벨 (Public, Private, Friend, Protected, 또는 Protected Friend).

한 줄에 여러 개의 상수를 선언 하려면

  • 다음 예제와 같이 공백, 쉼표와 선언을 구분 합니다.

    Public Const Four As Integer = 4, Five As Integer = 5, Six As Integer = 44  


'Knowledge > Visual Basic 6+' 카테고리의 다른 글

[VB6] 상수 선언  (0) 2018.05.09
VB6 단축키  (0) 2018.04.19
[VB6] 전역변수 선언 (Not Global keyword)  (0) 2018.04.02
[VB6] Replace Function - Visual Basic 6.0  (0) 2018.04.02
Visual Basic 문법  (0) 2018.03.27
[VB6] What is Me.Caption  (0) 2018.03.26

Comment +0



깃허브페이지, 티스토리, 네이버 블로그 서브도메인 연결하기 __ Ari's Github Blog.pdf




Comment +0

I'm using System.Data.SQLite provider in an ASP.NET application (framework 4.0). The issue I'm running into is that when I INSERT something in a table in the SQLite database, the database gets locked and the lock isn't being released even after the connection is disposed.

When trying to access the file, the error is: "The process cannot access the file 'catalog.sqlite' because it is being used by another process."

My code is pretty straightforward, I open the connection, read some data from a SQLServer database, insert that data into SQLite (through SQLiteDataAdapter) and then close the connection and dispose everything just to be on the safe side. But still, I get that error when I try to zip the file after it's being populated with the data.

I've read all kind of suggestions here on StackOverflow but none of them has helped solving the problem (turning off the antivirus, changing the transaction model, waiting a few seconds before zipping up the file, wrapping all the insert calls into a transaction, etc.. but none has helped solving this issue.

Maybe there's something specific to ASP.NET (multithreading being the issue? Even though I'm testing it on a development machine where there's only one call to that function and no concurrency?)

As a side note, I tried avoiding DataTable and SQLiteDataAdapter and using only SQLiteCommand directly and that way it works a charm. Of course I can keep building my queries as strings instead of using the data adapters, but I kind of find it a bit awkward when there's a framework built to do that.

    I had the same problem using the datasets/tableadapters generated with the designer shipped with System.Data.Sqlite.dll version 1.0.82.0 -- after closing the connection we were unable to read the database file using System.IO.FileStream. I was disposing correctly both connection and tableadapters and I was not using connection pooling.

    According to my first searches (for example this and this thread) that seemed a problem in the library itself -- either objects not correctly released and/or pooling issues (which I don't use).

    After reading your question I tried to replicate the problem using only SQLiteCommand objects and I found that the problem arises when you don't dispose them. Update 2012-11-27 19:37 UTC: this is further confirmed by this ticket for System.Data.SQLite, in which a developer explains that "allSQLiteCommand and SQLiteDataReader objects associated with the connection [should be] properly disposed".

    I then turned back on the generated TableAdapters and I saw that there was no implementation of the Dispose method -- so in fact the created commands were not disposed. I implemented it, taking care of disposing all the commands, and I have got no problem.

    Here's the code in C#, hope this helps. Please note that the code is converted from the original in Visual Basic, so expect some conversion errors.

    //In Table Adapter    
    protected override void Dispose(bool disposing)
    {
       base.Dispose(disposing);
    
        Common.DisposeTableAdapter(disposing, _adapter, _commandCollection);
    }
    
    public static class Common
    {
        /// <summary>
        /// Disposes a TableAdapter generated by SQLite Designer
        /// </summary>
        /// <param name="disposing"></param>
        /// <param name="adapter"></param>
        /// <param name="commandCollection"></param>
        /// <remarks>You must dispose all the command,
        /// otherwise the file remains locked and cannot be accessed
        /// (for example, for reading or deletion)</remarks>
        public static void DisposeTableAdapter(
            bool disposing,
            System.Data.SQLite.SQLiteDataAdapter adapter,
            IEnumerable<System.Data.SQLite.SQLiteCommand> commandCollection)
        {
            if (disposing) {
                DisposeSQLiteTableAdapter(adapter);
    
                foreach (object currentCommand_loopVariable in commandCollection)
                {
                    currentCommand = currentCommand_loopVariable;
                    currentCommand.Dispose();
                }
            }
        }
    
        public static void DisposeSQLiteTableAdapter(
                System.Data.SQLite.SQLiteDataAdapter adapter)
        {
            if (adapter != null) {
                DisposeSQLiteTableAdapterCommands(adapter);
    
                adapter.Dispose();
            }
        }
    
        public static void DisposeSQLiteTableAdapterCommands(
                System.Data.SQLite.SQLiteDataAdapter adapter)
        {
            foreach (object currentCommand_loopVariable in {
                adapter.UpdateCommand,
                adapter.InsertCommand,
                adapter.DeleteCommand,
                adapter.SelectCommand})
            {
                currentCommand = currentCommand_loopVariable;
                if (currentCommand != null) {
                    currentCommand.Dispose();
                }
            }
        }
    }

    Update 2013-07-05 17:36 UTC gorogm's answer highlights two important things:

    • according to the changelog on the official site of System.Data.SQLite, starting from version 1.0.84.0 the above code should not be needed, since the library takes care of this. I haven't tested this, but in the worst case you only need this snippet:

      //In Table Adapter    
      protected override void Dispose(bool disposing)
      {
        base.Dispose(disposing);
      
        this.Adapter.Dispose();
      }
    • about the implementation of the Dispose call of the TableAdapter: it is is better to put this in a partial class, so that a dataset regeneration does not affected this code (and any additional code you may need to add).

      I have the same problem. My scenario was after getting the data inside SQLite Database file I want to delete that file but it always throw an error "...using by other process". Even I dispose the SqliteConnection or SqliteCommand the error still occur. I've fixed the error by calling GC.Collect().

      Code snippet

      public void DisposeSQLite()
      {
          SQLiteConnection.Dispose();
          SQLiteCommand.Dispose();
      
          GC.Collect();
      }

      Hope this help.

        In my case I was creating SQLiteCommand objects without explicitly disposing them.

        var command = connection.CreateCommand();
        command.CommandText = commandText;
        value = command.ExecuteScalar();

        I wrapped my command in a using statement and it fixed my issue.

        static public class SqliteExtensions
        {
            public static object ExecuteScalar(this SQLiteConnection connection, string commandText)
            {
                // Added using
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = commandText;
                    return command.ExecuteScalar();
                }
            }
        }

        Then you can use it like this

        connection.ExecuteScalar(commandText);

          The following worked for me:  MySQLiteConnection.Close(); SQLite.SQLiteConnection.ClearAllPools()

          I found edymtt's answer right about blaming TableAdapters / Datasets, but instead of modifying the every time re-generated TableAdapter codefile, I found an other solution: to manually call .Dispose on the TableAdapter's child elements. (In .NET 4.5, latest SQLite 1.0.86)

          using (var db = new testDataSet())
          {
              using (testDataSetTableAdapters.UsersTableAdapter t = new testDataSetTableAdapters.UsersTableAdapter())
              {
                  t.Fill(db.Users);
                  //One of the following two is enough
                  t.Connection.Dispose(); //THIS OR
                  t.Adapter.Dispose();    //THIS LINE MAKES THE DB FREE
              }
              Console.WriteLine((from x in db.Users select x.Username).Count());
          }

            In most cases the problem will arise if you don't dispose your readers and commands properly. There is a scenario in which commands and readers will not dispose properly.

            Scenario 1: In case you are running a boolean function. before a result is reached the code in the finally block will not excecute. This is a big problem if you are going to be evaluating the results of function isDataExists while executing code if it suits the result i.e

                if(isDataExists){
                    // execute some code
                }

            The function being evaluated

                public bool isDataExists(string sql)
                {
                    try
                    {
                        OpenConnection();
                        SQLiteCommand cmd = new SQLiteCommand(sql, connection);
                        reader = cmd.ExecuteReader();
                        if (reader != null && reader.Read())
                        {
                            return true;
                        }
                        else
                        {
                            return false;
                        }
                    }
                    catch (Exception expMsg)
                    {
                        //Exception
                    }
                    finally
                    {
                        if (reader != null)
                        {
                            reader.Dispose();
                        }
                        CloseConnection();
                    }
                    return true;
                }

            Solution: Dispose your reader and command inside the try block as follows

                        OpenConnection();
                        SQLiteCommand cmd = new SQLiteCommand(sql, connection);
                        reader = cmd.ExecuteReader();
                        if (reader != null && reader.Read())
                        {
                            cmd.Dispose();
                            CloseConnection();
                            return true;
                        }
                        else
                        {
                            cmd.Dispose();
                            CloseConnection();
                            return false;
                        }

            Finally dispose the reader and command just in case some thing went wrong

                    finally
                    {
                        if (reader != null)
                        {
                            reader.Dispose();
                        }
                        CloseConnection();
                    }

              As said earlier SQLite objects must be destroyed. However, there is a strange behavior: connection must be open during a call Dispose on commands. For example:

              using(var connection = new SqliteConnection("source.db"))
              {
                  connection.Open();
                  using(var command = connection.CreateCommand("select..."))
                  {
                      command.Execute...
                  }
              }

              works fine, but:

              using(var connection = new SqliteConnection("source.db"))
              {
                  connection.Open();
                  using(var command = connection.CreateCommand("select..."))
                  {
                      command.Execute...
                      connection.Close();
                  }
              }

              gives the same file lock

              This was one of the top google results I had found when I ran into this error. However, none of the responses helped me so after more searching around and googling I came up with this code that works from some of the code from http://www.tsjensen.com/blog/post/2012/11/10/SQLite-on-Visual-Studio-with-NuGet-and-Easy-Instructions.aspx

              However, I did not have to use the NuGet at all. What my program does is downloads a db file from a server every time it is opened. Then if a user updates that db, it will be uploaded for everyone to get the next time they open the same program. I was getting the error that the file was in use after updating the local file and trying to upload it to our SharePoint. Now it works fine.

              Public Function sqLiteGetDataTable(sql As String) As DataTable
                  Dim dt As New DataTable()
                  Using cnn = New SQLiteConnection(dbConnection)
                      cnn.Open()
                      Using cmd As SQLiteCommand = cnn.CreateCommand()
                          cmd.CommandText = sql
                          Using reader As System.Data.SQLite.SQLiteDataReader = cmd.ExecuteReader()
                              dt.Load(reader)
                              reader.Dispose()
                          End Using
                          cmd.Dispose()
                      End Using
                      If cnn.State <> System.Data.ConnectionState.Closed Then
                          cnn.Close()
                      End If
                      cnn.Dispose()
                  End Using
                  Return dt
              End Function

              Ensuring that any IDisposable (e.g., SQLiteConnection, SQLiteCommand, etc) is properly disposed of solves this problem. I should re-iterate that one must be using "using" as a habit to ensure proper disposing of disposable resources.

              I had the same problem and it was only fixed by disposing the DbCommand in the using statement, but with Pooling = true my problem was fixed!!

                              SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
                              {
                                  Pooling = true
                              };


              Comment +0

              파일 속성에 보관 속성 혹은 읽기 전용 속성이 켜져있는 경우, 해당 오류가 난다면 다음의 방법이 통할 수 있습니다.



              --------------------------------------------------------------------------------------------------------------





              The problem can be that you can not delete or overwrite read-only files. The solution is to change the attributes.

              if(File.Exists(destFile))
              {
                  File.SetAttributes(destFile, FileAttributes.Normal);
              }
              File.Copy(sourcePath, destFile, true); 



              ----------------------------------------------------------------------------------------------------------------

              Comment +0




              screen 사용법

              http://sulac.egloos.com/5623987

              Vi와 같이 쓰기 좋은 Screen..
              매번 Vi로 열고 이동하기 힘들었는데.. 좋다.. 흐흐흐... 

              시작. : screen -S 세션명

              Ctrl+a A :현재 screen의 이름을 정한다. (나중에 Ctrl+a w 나 Ctrl+a "로 볼때 이름이 다 똑같아서 정의해 놓는게 정신건강에 좋다)
              Ctrl+a c : screen에서 새창 띄우기
              Ctrl+a k : 현재 screen 죽이기.


              Ctrl+a a : 바로 전 창으로
              Ctrl+a n : 다음 창으로 
              Ctrl+a p: 이전 창으로 

              Ctrl+a " : 열린 screen list 을 보여줌.

              Ctrl+a 0 : 0번째 창으로
              Ctrl+a 1 : 1번째 창으로
              Ctrl+a 9 : 10번째 창으로

              Ctrl+a d : screen 때어내기 (현재 상태로 남겨두는거..)
              Ctrl+a x : 이전에 남겨둔 screen에 다시 붙이기.
              screen -r 세션명 : 강제로 종료된거에 다시 붙이기

              ps -eaf | grep screen : 여러개 열려있을 경우 찾아서 죽여주는 센스.

              screen -list : 현재 열려있는 리스트를 보여줌.

              Ctrl+a ?  : 도움말
              Ctrl+a k  : 스크린 닫기
              Ctrl+a ESC, Ctrl+a [   : 화면 스크롤
              Ctrl+a ] : 붙여넣기.

              Ctrl+a S          화면 수평 분할
              Ctrl+a |            화면 수직 분할
              Ctrl + a :resize   resize region 
              Ctrl + a :fit         fit screen size to new terminal size
              Ctrl +a :remove  remove region
              Ctrl+a Q           현재 region을 제외한 나머지 숨기기
              Ctrl+ a tab      화면 분할 이동
              Ctrl+a, :            명령행 모드로 전환

              Ctrl+ X        : 현재 화면 닫기

              마지막 종료는 exit로 하면 깔끔하게 나온다고 함. 

              screen을 사용할때 마우스 휠로 올리거나 내리는게 안되는데... 이걸 해결하는 방법을 찾았다.
              ~/.screenrc에 아래 두줄을 추가하면.. 된다.. (문제는 창을 두개로 띄웠을때는 안되네 ㅡ.ㅡ)
              defscrollback 5000
              termcapinfo xterm* ti@:te@

              추가적으로... 그냥 키보드로 정보를 보고 싶으면....
              Ctrl+a, [ 누른후 화살표로 움직이면 된다. ^^;

              screen으로 화면을 분할해서 사용할때 다음 창으로 가는게 ctrl+a - tab 만 있는줄 알았으나.. 입맛대로 수정하면 되네..
              아래처럼 각 키를 넣어서 사용할수 있다.. ㅋㅋㅋ
              $ vi .screenrc 
              bind j focus down
              bind k focus up
              bind t focus top
              bind b focus bottom



              더 보시고 싶은 분들은 /usr/doc/screen 나 man을 활용하시기 바랍니다.

              screen -h
              -a            Force all capabilities into each window's termcap.
              -A -[r|R]     Adapt all windows to the new display width & height.
              -c file       Read configuration file instead of '.screenrc'.
              -d (-r)       Detach the elsewhere running screen (and reattach here).
              -dmS name     Start as daemon: Screen session in detached mode.
              -D (-r)       Detach and logout remote (and reattach here).
              -D -RR        Do whatever is needed to get a screen session.
              -e xy         Change command characters.
              -f            Flow control on, -fn = off, -fa = auto.
              -h lines      Set the size of the scrollback history buffer.
              -i            Interrupt output sooner when flow control is on.
              -l            Login mode on (update /var/run/utmp), -ln = off.
              -list         or -ls. Do nothing, just list our SockDir.
              -L            Turn on output logging.
              -m            ignore $STY variable, do create a new screen session.
              -O            Choose optimal output rather than exact vt100 emulation.
              -p window     Preselect the named window if it exists.
              -q            Quiet startup. Exits with non-zero return code if unsuccessful.
              -r            Reattach to a detached screen process.
              -R            Reattach if possible, otherwise start a new session.
              -s shell      Shell to execute rather than $SHELL.
              -S sockname   Name this session <pid>.sockname instead of <pid>.<tty>.<host>.
              -t title      Set title. (window's name).
              -T term       Use term as $TERM for windows, rather than "screen".
              -U            Tell screen to use UTF-8 encoding.
              -v            Print "Screen version 4.00.03jw4 (FAU) 2-May-06".
              -wipe         Do nothing, just clean up SockDir.
              -x            Attach to a not detached screen. (Multi display mode).
              -X            Execute <cmd> as a screen command in the specified session.

                                               Screen key bindings, page 1 of 1.

                                               Command key:  ^A   Literal ^A:  a

               break       ^B b        history     { }         other       ^A          split       S
               clear       C           info        i           pow_break   B           suspend     ^Z z
               colon       :           kill        K k         pow_detach  D           time        ^T t
               copy        ^[ [        lastmsg     ^M m        prev        ^H ^P p ^?  title       A
               detach      ^D d        license     ,           quit        \           vbell       ^G
               digraph     ^V          lockscreen  ^X x        readbuf     <           version     v
               displays    *           log         H           redisplay   ^L l        width       W
               dumptermcap .           login       L           remove      X           windows     ^W w
               fit         F           meta        a           removebuf   =           wrap        ^R r
               flow        ^F f        monitor     M           reset       Z           writebuf    >
               focus       ^I          next        ^@ ^N sp n  screen      ^C c        xoff        ^S s
               hardcopy    h           number      N           select      '           xon         ^Q q
               help        ?           only        Q           silence     _

              ^]   paste .
              "    windowlist -b
              -    select -
              0    select 0
              1    select 1
              2    select 2
              3    select 3
              4    select 4
              5    select 5
              6    select 6
              7    select 7
              8    select 8
              9    select 9
              I    login on
              O    login off
              ]    paste .
              |    split -v
              :kB: focus up

              가끔 screen이 먹통이 될때.. 아래의 방법을 사용하면 된다.
              요약하면
              screen -ls로 리스트 보고
              screen -r name 쳐서 붙이고
              Ctrl+a 누르고 :quit 치면...
              screen이 죽는다.. 라는거다.. 
               
              1. type "screen -list" to identify the (detached) screen session. eg: screen -list There are screens on: 20751.Melvin_Peter_V42 (Detached) Note: "20751.Melvin_Peter_V42" is your session id.

              2. get attached to the detached screen session eg: screen -r 20751.Melvin_Peter_V42

              3. Once connected to the session which might or might not respond, do the following. press "Ctrl + a" (there wont be any changes in your window now) type ":quit" ( its a colon[:] followed quit)

              4. Thats its your remote screen session will be terminated now.

              5. Hope this helps.



              Comment +0

              Create/Read/Edit Advance Excel 2007/2010 Report in C#.Net using EPPlus

              13 Nov 2013
              Export Advance Excel 2007 Report

              Contents

              Introduction

              Recently I was looking for an Advance tool through which I can generate complex Excel Reports. And after going through many tools I found EP Plus. For further details see this link. Through this tool we can easily create reports with charts, graphs and other drawing objects. I have planned to shared few samples with the community, so if any one is interested in using this library he will get a good kick start.

              Requirements

              • To compile the library, you need a C# 2010 Compiler or better, such as Visual Studio 2010 or Visual C# 2010 Express Edition.
              • To run the library code you need to have the .NET 4.0 framework installed.

              Installation

              Or you can download the library DLL from the above link. Then do the following:

              • Add the following library into your peoject by adding reference to that library dll.
              • After adding the reference you'll see the following scene in your 'Solution Explorer' window of your Visual Studio: 
                Added Reference
              • Then add these namespace into your C# file like this:
                /* To work eith EPPlus library */
                using OfficeOpenXml;
                using OfficeOpenXml.Drawing;
                
                /* For I/O purpose */
                using System.IO;
                
                /* For Diagnostics */
                using System.Diagnostics;

              Quick Start

              1. Reading From Excel Sheet into DataTable:

              Reading a simple excel sheet containing text and number into DataTable.

              private DataTable WorksheetToDataTable(ExcelWorksheet oSheet)
              {
              	int totalRows = oSheet.Dimension.End.Row;
              	int totalCols = oSheet.Dimension.End.Column;
              	DataTable dt = new DataTable(oSheet.Name);
              	DataRow dr = null;
              	for (int i = 1; i <= totalRows; i++)
              	{
              		if (i > 1) dr = dt.Rows.Add();
              		for (int j = 1; j <= totalCols; j++)
              		{
              			if (i == 1)
              				dt.Columns.Add(oSheet.Cells[i, j].Value.ToString());
              			else
              				dr[j - 1] = oSheet.Cells[i, j].Value.ToString();
              		}
              	}
              	return dt;
              }

              How you do this in this project?

              Import Excel 

              Select Excel 2007 File 

              The Sample Excel file is the following:

              Sample Excel File 

              Enter Sheet Name 

              The Final Resule is below:

              Final Output

              2. Setting Excel Workbook Properties:

              The Useful properties which you can set are:

              • Name of the Application by 'Application' property.
              • Version of the Application by 'AppVersion' property.
              • Name of the Author by 'Author' property.
              • Category of the Workbook by 'Category' property.
              • Comments in the Workbook by 'Comments' property.
              • Name of the Company by 'Company' property.
              • Last Modified Date of the Workbook by 'LastModifiedBy' property.
              • Last Printed Date of the Workbook by 'LastPrinted' property.
              • Keywords in Workbook if any by 'Keywords' property.
              • Status of the Workbook by 'Status' property.
              • Subject of the Workbook by 'Subject' property.
              • Title of the Workbook by 'Title' property.

              In the following way you can set the properties:

              using (ExcelPackage excelPkg = new ExcelPackage())
              {
              	excelPkg.Workbook.Properties.Author = "Debopam Pal";
              	excelPkg.Workbook.Properties.Title = "EPPlus Sample";
              }

              3. Merge Excel Columns:

              Merge Excell Cells by providing the Row Index and Column Index of the Start Cell and the End Cell. The syntax is: Cell[fromRow, fromCol, toRow, toCol]. In the following way you can merge excel cells:

              //Merge Excel Columns: Merging cells and create a center heading for our table
              oSheet.Cells[1, 1].Value = "Sample DataTable Export";
              oSheet.Cells[1, 1, 1, dt.Columns.Count].Merge = true;

              4. Setting Excel Cell Background Color and Fill Style:

              The following Fill Styles are available under OfficeOpenXml.Style.ExcelFillStyle:

              • DarkDown
              • DarkGrey
              • DarkGrid
              • DarkHorizontal
              • DarkTrellis
              • DarkUp
              • DarkVertical
              • Gray0625
              • Gray125
              • LightDown
              • LightGrey
              • LightHorizontal
              • LightTrellis
              • LightUp
              • LightVertical
              • MediumGrey
              • None
              • Solid

              You can use any color from System.Drawing.Color as your Background Color. In the following way you can set the Background Color along with Fill Style:

              var cell = oSheet.Cells[rowIndex, colIndex];
              
              //Setting the background color of header cells to Gray
              var fill = cell.Style.Fill;
              fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
              fill.BackgroundColor.SetColor(Color.Gray);

              5. Setting Excel Cell Border:

              The following Border styles are available under OfficeOpenXml.Style.ExcelBorderStyle:

              • DashDot
              • DashDotDot
              • Dashed
              • Dotted
              • Double
              • Hair
              • Medium
              • MediumDashDot
              • MediumDashDotDot
              • MediumDashed
              • None
              • Thick
              • Thin

              In the following way you can set the border style of a cell:

              var cell = oSheet.Cells[rowIndex, colIndex];
              
              //Setting top,left,right,bottom border of header cells
              var border = cell.Style.Border;
              border.Top.Style = border.Left.Style = border.Bottom.Style = border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;

              6. Setting Excel Formula:

              var cell = oSheet.Cells[rowIndex, colIndex];
              
              //Setting Sum Formula for each cell
              // Usage: Sum(From_Addres:To_Address)
              // e.g. - Sum(A3:A6) -> Sums the value of Column 'A' From Row 3 to Row 6
              cell.Formula = "Sum(" + oSheet.Cells[3, colIndex].Address + ":" + oSheet.Cells[rowIndex - 1, colIndex].Address + ")";

              7. Add Comments in Excel Cell:

              ///
              <summary>
              /// Adding custom comment in specified cell of specified excel sheet
              /// </summary>
              ///
              <param name="oSheet" />The ExcelWorksheet object
              /// <param name="rowIndex" />The row number of the cell where comment will put
              /// <param name="colIndex" />The column number of the cell where comment will put
              /// <param name="comment" />The comment text
              /// <param name="author" />The author name
              private void AddComment(ExcelWorksheet oSheet, int rowIndex, int colIndex, string comment, string author)
              {
              	// Adding a comment to a Cell
              	oSheet.Cells[rowIndex, colIndex].AddComment(comment, author);
              }

              8. Add Image in Excel Sheet:

              ///
              <summary>
              /// Adding custom image in spcified cell of specified excel sheet
              /// </summary>
              ///
              <param name="oSheet" />The ExcelWorksheet object
              /// <param name="rowIndex" />The row number of the cell where the image will put
              /// <param name="colIndex" />The column number of the cell where the image will put
              /// <param name="imagePath" />The path of the image file
              private void AddImage(ExcelWorksheet oSheet, int rowIndex, int colIndex, string imagePath)
              {
              	Bitmap image = new Bitmap(imagePath);
              	ExcelPicture excelImage = null;
              	if (image != null)
              	{
              		excelImage = oSheet.Drawings.AddPicture("Debopam Pal", image);
              		excelImage.From.Column = colIndex;
              		excelImage.From.Row = rowIndex;
              		excelImage.SetSize(100, 100);
              		// 2x2 px space for better alignment
              		excelImage.From.ColumnOff = Pixel2MTU(2);
              		excelImage.From.RowOff = Pixel2MTU(2);
              	}
              }
              
              public int Pixel2MTU(int pixels)
              {
              	int mtus = pixels * 9525;
              	return mtus;
              }

              9. Add Custom objects in Excel Sheet:

              The all shapes are available under enum eShapeStyle. In the following way we can create object of the specified shape and inserting text inside it.

              ///
              <summary>
              /// Adding custom shape or object in specifed cell of specified excel sheet
              /// </summary>
              ///
              <param name="oSheet" />The ExcelWorksheet object
              /// <param name="rowIndex" />The row number of the cell where the object will put
              /// <param name="colIndex" />The column number of the cell where the object will put
              /// <param name="shapeStyle" />The style of the shape of the object
              /// <param name="text" />Text inside the object
              private void AddCustomObject(ExcelWorksheet oSheet, int rowIndex, int colIndex, eShapeStyle shapeStyle, string text)
              {
              	ExcelShape excelShape = oSheet.Drawings.AddShape("Custom Object", shapeStyle);
              	excelShape.From.Column = colIndex;
              	excelShape.From.Row = rowIndex;
              	excelShape.SetSize(100, 100);
              	// 5x5 px space for better alignment
              	excelShape.From.RowOff = Pixel2MTU(5);
              	excelShape.From.ColumnOff = Pixel2MTU(5);
              	// Adding text into the shape
              	excelShape.RichText.Add(text);
              }
              
              public int Pixel2MTU(int pixels)
              {
              	int mtus = pixels * 9525;
              	return mtus;
              }

              The Final Exported Excel file:

              Exported Excel File

              10. Create a New Excel Sheet from an Existing Excel Sheet or Template:

              Now, we're going to take the Existing Excel Sheet what we've got from the extension of this article. The name of the existing excel sheet was 'Sample1.xlsx'. Now we are going to create 'Sample2.xlsx' by taking values from 'Sample1.xlsx' and adding some new values.

              Here is 'Sample1.xlsx':

              Sample1.xlsx

              Now, see how you can do it:

              // Taking existing file: 'Sample1.xlsx'. Here 'Sample1.xlsx' is treated as template file
              FileInfo templateFile = new FileInfo(@"Sample1.xlsx");
              // Making a new file 'Sample2.xlsx'
              FileInfo newFile = new FileInfo(@"Sample2.xlsx");
              
              // If there is any file having same name as 'Sample2.xlsx', then delete it first
              if (newFile.Exists)
              {
              	newFile.Delete();
              	newFile = new FileInfo(@"Sample2.xlsx");
              }
              
              using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
              {
              	// Openning first Worksheet of the template file i.e. 'Sample1.xlsx'
              	ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
              	// I'm adding 5th & 6th rows as 1st to 4th rows are already filled up with values in 'Sample1.xlsx'
              	worksheet.InsertRow(5, 2);
              
              	// Inserting values in the 5th row
              	worksheet.Cells["A5"].Value = "12010";
              	worksheet.Cells["B5"].Value = "Drill";
              	worksheet.Cells["C5"].Value = 20;
              	worksheet.Cells["D5"].Value = 8;
              
              	// Inserting values in the 6th row
              	worksheet.Cells["A6"].Value = "12011";
              	worksheet.Cells["B6"].Value = "Crowbar";
              	worksheet.Cells["C6"].Value = 7;
              	worksheet.Cells["D6"].Value = 23.48;
              }

              11. Adding 'R1C1' Formula in the Excel Cell:

              Now, we're going to add formula for 'Value' column i.e. 'E' as the values in column 'E' come from the product of 'Quantity' and 'Price' column, as you can see in the above picture of Sample1.xlsx. In the Extension of this Article, I've told how to add basic formula in this respect. So, I hope, you're now able to add basic formula:) Now, we're going to see how we add 'R1C1' formula. If you don't know what it is, just click here...I'm waiting for you here:) Lets see:

              worksheet.Cells["E2:E6"].FormulaR1C1 = "RC[-2]*RC[-1]";

              Just one line of code, its so simple:)

              12. Adding 'Named Range' in the Excel Sheet:

              You don't know 'Excel Named Range'? No problem, just read a few lines here. Like the following way we can add Named Range:

              var name = worksheet.Names.Add("SubTotalName", worksheet.Cells["C7:E7"]);

              By the following way we can add any formula to the Named Range:

              name.Formula = "SUBTOTAL(9, C2:C6)";

              13. Adding Pie Chart in the Excel Sheet:

              Read about Excel Chart here.
              Read about Pie Chart here.
              EPPlus Library suport following type of chart below:

              • Area
              • Area3D
              • AreaStacked
              • AreaStacked100
              • AreaStacked1003D
              • AreaStacked3D
              • BarClustered
              • BarClustered3D
              • BarOfPie
              • BarStacked
              • BarStacked100
              • BarStacked1003D
              • BarStacked3D
              • Bubble
              • Bubble3DEffect
              • Column3D
              • ColumnClustered
              • ColumnClustered3D
              • ColumnStacked
              • ColumnStacked100
              • ColumnStacked1003D
              • ColumnStacked3D
              • ConeBarClustered
              • ConeBarStacked
              • ConeBarStacked100
              • ConeCol
              • ConeColClustered
              • ConeColStacked
              • ConeColStacked100
              • CylinderBarClustered
              • CylinderBarStacked
              • CylinderBarStacked100
              • CylinderCol
              • CylinderColClustered
              • CylinderColStacked
              • CylinderColStacked100
              • Doughnut
              • DoughnutExploded
              • Line
              • Line3D
              • LineMarkers
              • LineMarkersStacked
              • LineMarkersStacked100
              • LineStacked
              • LineStacked100
              • Pie
              • Pie3D
              • PieExploded
              • PieExploded3D
              • PieOfPie
              • PyramidBarClustered
              • PyramidBarStacked
              • PyramidBarStacked100
              • PyramidCol
              • PyramidColClustered
              • PyramidColStacked
              • PyramidColStacked100
              • Radar
              • RadarFilled
              • RadarMarkers
              • StockHLC
              • StockOHLC
              • StockVHLC
              • StockVOHLC
              • Surface
              • SurfaceTopView
              • SurfaceTopViewWireframe
              • SurfaceWireframe
              • XYScatter
              • XYScatterLines
              • XYScatterLinesNoMarkers
              • XYScatterSmooth
              • XYScatterSmoothNoMarkers
              Now, I'm going to show you how you can create a Simple Pie Chart. I hope from this concept you will be able to create another type of Chart mentioned above. Let see how to create Pie Chart using EPPlus:
              First you need to add OfficeOpenXml.Drawing.Chart namespace to work with Chart. Now see the code below:

              // Adding namespace to work with Chart
              using OfficeOpenXml.Drawing.Chart;
              
              // Adding Pie Chart to the Worksheet and assigning it in a variable 'chart'
              var chart = (worksheet.Drawings.AddChart("PieChart", OfficeOpenXml.Drawing.Chart.eChartType.Pie3D) as ExcelPieChart);

              Setting title text of the chart:

              chart.Title.Text = "Total";

              Setting Chart Position: 5 pixel offset from 5th column of the 1st row:

              chart.SetPosition(0, 0, 5, 5);

              Setting width & height of the chart area:

              chart.SetSize(600, 300);

              In the Pie Chart value will come from 'Value' column and category name come from the 'Product' column, see how to do it:

              ExcelAddress valueAddress = new ExcelAddress(2, 5, 6, 5);
              var ser = (chart.Series.Add(valueAddress.Address, "B2:B6") as ExcelPieChartSerie);

              Setting Chart Properties:

              // To show the Product name within the Pie Chart along with value
              chart.DataLabel.ShowCategory = true;
              // To show the value in form of percentage
              chart.DataLabel.ShowPercent = true;

              Formmatting the style of the Chart:

              chart.Legend.Border.LineStyle = eLineStyle.Solid;
              chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
              chart.Legend.Border.Fill.Color = Color.DarkBlue;

              Finally Expoted Excel is here:

              Sample2.xlsx

              Declaration

              Please Download the source code for detail. I hope you'll understand as the source
              			code is documented. If any doubt, just post your comment below. Thank You.

              History

              License

              This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

              Share

              About the Author

              Debopam Pal
              Software Developer National Informatics Centre (NIC)
              India India
              Hello! Myself Debopam Pal. I've completed my MCA degree from West Bengal University of Technology at 2013. I'm from India. I’ve started to work with MS Technologies in 2013 specially in C# 4.0, ASP.NET 4.0. I've also worked in PHP 5. Now I work in JAVA/J2EE, Struts2. Currently I'm involved in a e-Governance Project since Jan, 2014. In my leisure time I write Blog, Articles as I think that every developer should contribute something otherwise resource will be finished one day. Thank you for your time.

              Visit: Linkedin Profile | Facebook Profile | Google+ Profile | CodeProject Profile


              Comment +0

              Create Excel Files in C#

              April 23, 2017 

              Creating report files in Excel format is a feature that many users need out of their software applications.  

              In this article. we'll cover creating an Excel spreadsheet in C# using a third-party library called EPPlus.

              EPPlus is a .NET library that reads and writes Excel files using the Open Office XML format (*.xlsx).  It is licensed under GNU Library General Public License (LGPL) so it can be freely used in commercial software.

              Installation

              In Visual Studio 2017, go to Tools -> NuGet Package Manager -> Package Manager Console

              In the console window that appears, type the following at the prompt to install it into the current project:

              PM > Install-Package EPPlus
              

              After it is finished, check the References under Solution Explorer and verify that the EPPlus assembly is listed.

              Add to Project

              We add line 7 below so we can use the types inside the namespace:

              using System;
              using System.Collections.Generic;
              using System.Linq;
              using System.Text;
              using System.Threading.Tasks;
              
              using OfficeOpenXml;
              
              namespace ConsoleApp2
              {
                  class Program
                  {
                      static void Main(string[] args)
                      {
                      }
                  }
              }

              Create a Workbook

              We call the ExcelPackage class constructor to create a workbook.   In the below code, we do this within a using block so we don't have to explicitly dispose of the object.

              We add 3 blank worksheets to the workbook by using the Add() method in the Worksheets class.   Finally, we save the file to a path using the SaveAs() method.

              using (ExcelPackage excel = new ExcelPackage())
              {
                excel.Workbook.Worksheets.Add("Worksheet1");
                excel.Workbook.Worksheets.Add("Worksheet2");
                excel.Workbook.Worksheets.Add("Worksheet3");
              
                FileInfo excelFile = new FileInfo(@"C:\Users\amir\Desktop\test.xlsx");
                excel.SaveAs(excelFile);
              }

              The SaveAs method will throw an exception if there are no worksheets defined. 

              Select a Worksheet

              Let's say we want to add some data to Worksheet1.   How can we target it?

              var excelWorksheet = excel.Workbook.Worksheets["Worksheet1"];

              Add a Row

              Spreadsheets typically have header rows.  Let's add a header row to Worksheet1.  

              For this, we create a List containing an array of strings:

              List<string[]> headerRow = new List<string[]>()
              {
                new string[] { "ID", "First Name", "Last Name", "DOB" }
              };

              Before we can pass this object into our worksheet, we must figure out the cell range for the header row:

              // Determine the header range (e.g. A1:E1)
              string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";

              Here's how we load the data into the worksheet:

              // Popular header row data
              excelWorksheet.Cells[headerRange].LoadFromArrays(headerRow);

              Putting it all together here is the code block:

              using (ExcelPackage excel = new ExcelPackage())
              {
                excel.Workbook.Worksheets.Add("Worksheet1");
                excel.Workbook.Worksheets.Add("Worksheet2");
                excel.Workbook.Worksheets.Add("Worksheet3");
                
                var headerRow = new List<string[]>()
                {
                  new string[] { "ID", "First Name", "Last Name", "DOB" }
                };
                
                // Determine the header range (e.g. A1:D1)
                string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";
              
                // Target a worksheet
                var worksheet = excel.Workbook.Worksheets["Worksheet1"];
                
                // Popular header row data
                worksheet.Cells[headerRange].LoadFromArrays(headerRow);
                
                FileInfo excelFile = new FileInfo(@"C:\Users\amir\Desktop\test.xlsx");
                excel.SaveAs(excelFile);
              }

              If we open the file in Excel, we see that the header row appears in the first worksheet:


              Row Styling

              We can easily change the font size, color, and weight of any row.

              worksheet.Cells[headerRange].Style.Font.Bold = true;
              worksheet.Cells[headerRange].Style.Font.Size = 14;
              worksheet.Cells[headerRange].Style.Font.Color.SetColor(System.Drawing.Color.Blue);

               

              Add Data to Specific Cell

              We can easily add data to a cell by setting the Value property of the Cell element.

              worksheet.Cells["A1"].Value = "Hello World!";

              Add Data to Multiple Cells

              Inserting data into multiple cells is also straightforward.   We can populate a list containing an array that corresponds to each row.    

              var cellData= new List()
              {
                new object[] {0, 0, 0, 1},
                new object[] {10,7.32,7.42,1},
                new object[] {20,5.01,5.24,1},
                new object[] {30,3.97,4.16,1},
                new object[] {40,3.97,4.16,1},
                new object[] {50,3.97,4.16,1},
                new object[] {60,3.97,4.16,1},
                new object[] {70,3.97,4.16,1},
                new object[] {80,3.97,4.16,1},
                new object[] {90,3.97,4.16,1},
                new object[] {100,3.97,4.16,1}
              };
              
              excelWorksheet.Cells[2, 1].LoadFromArrays(cellData);

              Here we add the data from cellData into the worksheet starting on row 2, column 1.   Remember that we already inserted a header row on row 1.

              Check if Excel is installed

              We can use this line of code to determine if Excel is installed:

              bool isExcelInstalled = Type.GetTypeFromProgID("Excel.Application") != null ? true : false;
              

              If the expression evaluates to true, then we can safely open the file using the below code:

              if (isExcelInstalled) 
              {
                System.Diagnostics.Process.Start(excelFile.ToString());
              }
              

              What about Microsoft.Office.Interop.Excel?

              If the user has Excel installed on their machine, we could tap into the Microsoft.Office.Interop.Excel assembly which instructs .NET on how to call the Excel COM libraries.

              This approach has a couple flaws though.   Excel file generation would fail if the user didn't have Excel installed.  Also, what happens when the version of the assembly we're using is incompatible with the latest version of Excel?

              A software application shouldn't rely on an Excel installation in order to create a spreadsheet which is why I discourage using Microsoft.Office.Interop.Excel.


              Comment +0