/*
* You may amend and distribute as you like, but don't remove this header!
*
* EPPlus provides server-side generation of Excel 2007 spreadsheets.
* See http://www.codeplex.com/EPPlus for details.
*
* All rights reserved.
*
* EPPlus is an Open Source project provided under the
* GNU General Public License (GPL) as published by the
* Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*
* The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php
* If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
*
* The code for this project may be used and redistributed by any means PROVIDING it is
* not sold for profit without the author's written consent, and providing that this notice
* and the author's name and all copyright notices remain intact.
*
* All code and executables are provided "as is" with no warranty either express or implied.
* The author accepts no liability for any damage or loss of business that this product may cause.
*
* Code change notes:
*
* Author Change Date
* ******************************************************************************
* Jan Källman Added 2011-04-18
*******************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using OfficeOpenXml;
using System.Data.SqlClient;
using OfficeOpenXml.Table.PivotTable;
using OfficeOpenXml.Drawing.Chart;
namespace EPPlusSamples
{
/// <summary>
/// This class shows how to use pivottables
/// </summary>
public static class Sample12
{
public class SalesDTO
{
public string Title { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public string Name
{
get
{
return string.IsNullOrEmpty(MiddleName) ? FirstName + " " + LastName : FirstName + " " + MiddleName + " " + LastName;
}
}
public DateTime OrderDate { get; set; }
public decimal SubTotal { get; set; }
public decimal Tax { get; set; }
public decimal Freight { get; set; }
public decimal Total
{
get
{
return SubTotal + Tax + Freight;
}
}
}
public static string RunSample12(string sqlServerName, DirectoryInfo outputDir)
{
var list = new List<SalesDTO>();
if (sqlServerName == "")
{
list = GetRandomData();
}
else
{
list = GetDataFromSQL(sqlServerName);
}
string file = outputDir.FullName + @"\sample12.xlsx";
if (File.Exists(file)) File.Delete(file);
FileInfo newFile = new FileInfo(file);
using (ExcelPackage pck = new ExcelPackage(newFile))
{
// get the handle to the existing worksheet
var wsData = pck.Workbook.Worksheets.Add("SalesData");
var dataRange = wsData.Cells["A1"].LoadFromCollection
(
from s in list
orderby s.LastName, s.FirstName
select s,
true, OfficeOpenXml.Table.TableStyles.Medium2);
wsData.Cells[2, 6, dataRange.End.Row, 6].Style.Numberformat.Format = "mm-dd-yy";
wsData.Cells[2, 7, dataRange.End.Row, 11].Style.Numberformat.Format = "#,##0";
dataRange.AutoFitColumns();
var wsPivot = pck.Workbook.Worksheets.Add("PivotSimple");
var pivotTable1 = wsPivot.PivotTables.Add(wsPivot.Cells["A1"], dataRange, "PerEmploee");
pivotTable1.RowFields.Add(pivotTable1.Fields[4]);
var dataField = pivotTable1.DataFields.Add(pivotTable1.Fields[6]);
dataField.Format="#,##0";
pivotTable1.DataOnRows = true;
var chart = wsPivot.Drawings.AddChart("PivotChart", eChartType.Pie, pivotTable1);
chart.SetPosition(1, 0, 4, 0);
chart.SetSize(600, 400);
var wsPivot2 = pck.Workbook.Worksheets.Add("PivotDateGrp");
var pivotTable2 = wsPivot2.PivotTables.Add(wsPivot2.Cells["A3"], dataRange, "PerEmploeeAndQuarter");
pivotTable2.RowFields.Add(pivotTable2.Fields["Name"]);
//Add a rowfield
var rowField = pivotTable2.RowFields.Add(pivotTable2.Fields["OrderDate"]);
//This is a date field so we want to group by Years and quaters. This will create one additional field for years.
rowField.AddDateGrouping(eDateGroupBy.Years | eDateGroupBy.Quarters);
//Get the Quaters field and change the texts
var quaterField = pivotTable2.Fields.GetDateGroupField(eDateGroupBy.Quarters);
quaterField.Items[0].Text = "<"; //Values below min date, but we use auto so its not used
quaterField.Items[1].Text = "Q1";
quaterField.Items[2].Text = "Q2";
quaterField.Items[3].Text = "Q3";
quaterField.Items[4].Text = "Q4";
quaterField.Items[5].Text = ">"; //Values above max date, but we use auto so its not used
//Add a pagefield
var pageField = pivotTable2.PageFields.Add(pivotTable2.Fields["Title"]);
//Add the data fields and format them
dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["SubTotal"]);
dataField.Format = "#,##0";
dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Tax"]);
dataField.Format = "#,##0";
dataField = pivotTable2.DataFields.Add(pivotTable2.Fields["Freight"]);
dataField.Format = "#,##0";
//We want the datafields to appear in columns
pivotTable2.DataOnRows = false;
pck.Save();
}
return file;
}
private static List<SalesDTO> GetRandomData()
{
List<SalesDTO> ret = new List<SalesDTO>();
var firstNames = new string[] {"John", "Gunnar", "Karl", "Alice"};
var lastNames = new string[] {"Smith", "Johansson", "Lindeman"};
Random r = new Random();
for (int i = 0; i < 500; i++)
{
ret.Add(
new SalesDTO()
{
FirstName = firstNames[r.Next(4)],
LastName = lastNames[r.Next(3)],
OrderDate = new DateTime(2002, 1, 1).AddDays(r.Next(1000)),
Title="Sales Representative",
SubTotal = r.Next(100, 10000),
Tax = 0,
Freight = 0
});
}
return ret;
}
private static List<SalesDTO> GetDataFromSQL(string sqlServerName)
{
string connectionStr = string.Format(@"server={0};database=AdventureWorks;Integrated Security=true;", sqlServerName);
var ret = new List<SalesDTO>();
// lets connect to the AdventureWorks sample database for some data
using (SqlConnection sqlConn = new SqlConnection(connectionStr))
{
sqlConn.Open();
using (SqlCommand sqlCmd = new SqlCommand("select h.Title, FirstName, MiddleName, LastName, SubTotal, OrderDate, TaxAmt, Freight, TotalDue from Sales.SalesOrderHeader s inner join HumanResources.Employee h on s.SalesPersonID = h.EmployeeID inner join Person.Contact c on c.ContactID = h.ContactID order by LastName, FirstName, MiddleName;", sqlConn))
{
using (SqlDataReader sqlReader = sqlCmd.ExecuteReader())
{
//Get the data and fill rows 5 onwards
while (sqlReader.Read())
{
ret.Add(new SalesDTO
{
Title = sqlReader["Title"].ToString(),
FirstName=sqlReader["FirstName"].ToString(),
MiddleName=sqlReader["MiddleName"].ToString(),
LastName=sqlReader["LastName"].ToString(),
OrderDate = (DateTime)sqlReader["OrderDate"],
SubTotal = (decimal)sqlReader["SubTotal"],
Tax=(decimal)sqlReader["TaxAmt"],
Freight=(decimal)sqlReader["Freight"]
});
}
}
}
}
return ret;
}
}
}
การเขียน Windows Service มันไม่ยาก แต่มันยากตรงที่จะอธิบายว่า "ตัวของเรา/บริษัทของเรา" มีความต้องการอย่างไร?
ต่างหากเล่า?
(บางคนเขียนโปรแกรมมาทั้งชีวิตแต่ยังต้อง(อาศัย) ให้คนอื่นคิดความต้องการให้)
Imports System
Imports System.Collections
Imports System.IO
Imports System.Reflection
Imports System.ServiceProcess
Imports Seekafile.Configuration
Imports Seekafile.Server.Installation
Imports Seekafile.Server.Logging
Imports Seekafile.Server.Watching
Namespace Seekafile.Server
' <summary>
' Summary description for Go.
' </summary>
Public Class Go
Inherits System.ServiceProcess.ServiceBase
Implements IServiceConsole
Private watchers As New ArrayList()
Public Shared Sub Main(ByVal args As String())
If args.Length = 1 Then
Select Case args(0).ToUpper()
Case "/I"
serviceInstall()
Return
Case "/U"
' TODO stop the service
serviceUninstall()
Return
Case "/R"
runConsole()
Return
Case "/S"
runService()
Return
Case "/?", "/H"
Exit Select
Case Else
Exit Select
End Select
End If
help()
End Sub
' <summary>
' Set things in motion so your service can do its work.
' </summary>
Protected Overloads Overrides Sub OnStart(ByVal args As String())
Try
' This process will run with the lowest possible priority
System.Diagnostics.Process.GetCurrentProcess().PriorityClass = System.Diagnostics.ProcessPriorityClass.Idle
Dim cfg As ServerConfig = loadConfig()
If cfg Is Nothing Then
Return
End If
' Debug can be already enabled for console
If Not Log.DebugEnabled Then
Log.DebugEnabled = cfg.Service.Debug
End If
For Each ic As IndexConfig In cfg.Indices
Dim watcher As New Watcher()
watcher.GoAndWatch(ic)
watchers.Add(watcher)
Next
Catch e As Exception
Log.[Error](e)
End Try
End Sub
' <summary>
' Stop this service.
' </summary>
Protected Overloads Overrides Sub OnStop()
Log.Debug("Stopping service.")
For Each w As Watcher In watchers
w.[Stop]()
Next
End Sub
Private Shared Sub help()
Console.Title = "Windows Document Server 1.5"
Console.WriteLine("Command line parameters:")
Console.WriteLine(vbTab & "/i" & vbTab & "Install the service")
Console.WriteLine(vbTab & "/u" & vbTab & "Uninstall the service")
Console.WriteLine(vbTab & "/r" & vbTab & "Run in console")
Console.Read()
End Sub
Private Shared Sub runService()
Log.Debug("Starting service.")
ServiceBase.Run(New Go())
End Sub
Private Shared Sub runConsole()
Log.DebugEnabled = True
'New Go().OnStart(Nothing)
Dim s As New Go
s.OnStart(Nothing)
End Sub
Private Shared Sub serviceUninstall()
Dim cfg As ServerConfig = loadConfig()
If cfg Is Nothing Then
Return
End If
Dim srv As New DirectServiceInstaller()
Dim okUninstallation As Boolean = srv.UnInstallService(cfg.Service.Name)
If okUninstallation Then
Console.WriteLine("Service uninstalled.")
Else
Console.WriteLine("There was a problem with uninstallation.")
End If
End Sub
Private Shared Sub serviceInstall()
Dim cfg As ServerConfig = loadConfig()
If cfg Is Nothing Then
Return
End If
Dim srv As New DirectServiceInstaller()
Dim okInstallation As Boolean = srv.InstallService(Assembly.GetExecutingAssembly().Location + " /s", cfg.Service.Name, cfg.Service.DisplayName)
If okInstallation Then
Console.WriteLine("Service installed.")
Else
Console.WriteLine("There was a problem with installation.")
End If
End Sub
Private Shared Function loadConfig() As ServerConfig
Dim cfg As ServerConfig = Nothing
Try
Dim pthConfig As String = Path.Combine(Directory.GetParent(Assembly.GetExecutingAssembly().Location).FullName, "config.xml")
cfg = ServerConfig.Load(pthConfig)
If cfg Is Nothing Then
Log.[Error]("Config file not found.")
Return Nothing
End If
Catch e As Exception
Log.[Error]("Error loading the config file: " + e.Message)
End Try
Return cfg
End Function
Public Sub Start() Implements IServiceConsole.Start
Log.DebugEnabled = True
OnStart(Nothing)
End Sub
Public Overloads Sub [Stop]() Implements IServiceConsole.Stop
OnStop()
End Sub
Public Function WatchersCount() As Integer Implements Configuration.IServiceConsole.WatchersCount
Return watchers.Count
End Function
End Class
End Namespace
var dataRange = wsData.Cells["A1"].LoadFromCollection
(
from s in list
orderby s.LastName, s.FirstName
select s,
true, OfficeOpenXml.Table.TableStyles.Medium2
);
ผมอยากได้แบบนี้
var dataRange = wsData.Cells["A1"].LoadFromCollection
(
from s in list
orderby "s.LastName", "s.FirstName" // String Property (Field/Column Name)
select s,
true, OfficeOpenXml.Table.TableStyles.Medium2
);
Load balance : Server 02
