/// <summary>
/// Get the height of specified rows.
/// </summary>
/// <param name="Unit">The unit of the height to be returned.</param>
/// <param name="StartRowIndex">The row index of the start row.</param>
/// <param name="EndRowIndex">The row index of the end row.</param>
/// <returns>The height in the specified unit type.</returns>
public double GetHeight(SLMeasureUnitTypeValues Unit, int StartRowIndex, int EndRowIndex)
{
if (StartRowIndex < 1)
{
StartRowIndex = 1;
}
if (StartRowIndex > SLConstants.RowLimit)
{
StartRowIndex = SLConstants.RowLimit;
}
if (EndRowIndex < 1)
{
EndRowIndex = 1;
}
if (EndRowIndex > SLConstants.RowLimit)
{
EndRowIndex = SLConstants.RowLimit;
}
long lHeight = 0;
var i = 0;
SLRowProperties rp;
for (i = StartRowIndex; i <= EndRowIndex; ++i)
{
if (slws.RowProperties.ContainsKey(i))
{
rp = slws.RowProperties[i];
lHeight += rp.HeightInEMU;
}
else
{
lHeight += slws.SheetFormatProperties.DefaultRowHeightInEMU;
}
}
double result = 0;
switch (Unit)
{
case SLMeasureUnitTypeValues.Centimeter:
result = SLConvert.FromEmuToCentimeter(lHeight);
break;
case SLMeasureUnitTypeValues.Emu:
result = lHeight;
break;
case SLMeasureUnitTypeValues.Inch:
result = SLConvert.FromEmuToInch(lHeight);
break;
case SLMeasureUnitTypeValues.Point:
result = SLConvert.FromEmuToPoint(lHeight);
break;
}
return(result);
}
/// <summary>
/// Get the width of the specified columns.
/// </summary>
/// <param name="Unit">The unit of the width to be returned.</param>
/// <param name="StartColumnIndex">The column index of the start column.</param>
/// <param name="EndColumnIndex">The column index of the end column.</param>
/// <returns>The width in the specified unit type.</returns>
public double GetWidth(SLMeasureUnitTypeValues Unit, int StartColumnIndex, int EndColumnIndex)
{
if (StartColumnIndex < 1)
{
StartColumnIndex = 1;
}
if (StartColumnIndex > SLConstants.ColumnLimit)
{
StartColumnIndex = SLConstants.ColumnLimit;
}
if (EndColumnIndex < 1)
{
EndColumnIndex = 1;
}
if (EndColumnIndex > SLConstants.ColumnLimit)
{
EndColumnIndex = SLConstants.ColumnLimit;
}
long lWidth = 0;
var i = 0;
SLColumnProperties cp;
for (i = StartColumnIndex; i <= EndColumnIndex; ++i)
{
if (slws.ColumnProperties.ContainsKey(i))
{
cp = slws.ColumnProperties[i];
lWidth += cp.WidthInEMU;
}
else
{
lWidth += slws.SheetFormatProperties.DefaultColumnWidthInEMU;
}
}
double result = 0;
switch (Unit)
{
case SLMeasureUnitTypeValues.Centimeter:
result = SLConvert.FromEmuToCentimeter(lWidth);
break;
case SLMeasureUnitTypeValues.Emu:
result = lWidth;
break;
case SLMeasureUnitTypeValues.Inch:
result = SLConvert.FromEmuToInch(lWidth);
break;
case SLMeasureUnitTypeValues.Point:
result = SLConvert.FromEmuToPoint(lWidth);
break;
}
return(result);
}
/// <summary>
/// demonstrate how to get used columns in the format a a letter rather than an integer
/// </summary>
/// <returns></returns>
public string[] UsedCellsInWorkSheet(string pFileName, string pSheetName)
{
using (var doc = new SLDocument(pFileName, pSheetName))
{
SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
IEnumerable <string> columnNames = Enumerable.Range(1, stats.EndColumnIndex)
// ReSharper disable once ConvertClosureToMethodGroup
.Select((cellIndex) => SLConvert.ToColumnName(cellIndex));
return(columnNames.ToArray());
}
}
public void ShouldWriteHello()
{
SLDocument sl = new SLDocument();
// set a boolean at "A1"
sl.SetCellValue("A1", true);
// set at row 2, columns 1 through 20, a value that's equal to the column index
for (int i = 1; i <= 20; ++i)
{
sl.SetCellValue(2, i, i);
}
// set the value of PI
sl.SetCellValue("B3", 3.14159);
// set the value of PI at row 4, column 2 (or "B4") in string form.
// use this when you already have numeric data in string form and don't
// want to parse it to a double or float variable type
// and then set it as a value.
// Note that "3,14159" is invalid. Excel (or Open XML) stores numerals in
// invariant culture mode. Frankly, even "1,234,567.89" is invalid because
// of the comma. If you can assign it in code, then it's fine, like so:
// double fTemp = 1234567.89;
sl.SetCellValueNumeric(4, 2, "3.14159");
// normal string data
sl.SetCellValue("C6", "This is at C6!");
// typical XML-invalid characters are taken care of,
// in particular the & and < and >
sl.SetCellValue("I6", "Dinner & Dance costs < $10");
// this sets a cell formula
// Note that if you want to set a string that starts with the equal sign,
// but is not a formula, prepend a single quote.
// For example, "'==" will display 2 equal signs
sl.SetCellValue(7, 3, "=SUM(A2:T2)");
// if you need cell references and cell ranges *really* badly, consider the SLConvert class.
sl.SetCellValue(SLConvert.ToCellReference(7, 4), string.Format("=SUM({0})", SLConvert.ToCellRange(2, 1, 2, 20)));
// dates need the format code to be displayed as the typical date.
// Otherwise it just looks like a floating point number.
sl.SetCellValue("C8", new DateTime(3141, 5, 9));
SLStyle style = sl.CreateStyle();
style.FormatCode = "d-mmm-yyyy";
sl.SetCellStyle("C8", style);
sl.SetCellValue(8, 6, "I predict this to be a significant date. Why, I do not know...");
sl.SetCellValue(9, 4, 456.123789);
// we don't have to create a new SLStyle because
// we only used the FormatCode property
style.FormatCode = "0.000%";
sl.SetCellStyle(9, 4, style);
sl.SetCellValue(9, 6, "Perhaps a phenomenal growth in something?");
sl.SaveAs("HelloWorld.xlsx");
}
private doGenerateDocumentResult GenerateACC010_Sheet1(DocumentContext context, SLDocument doc, string sheetname)
{
#region Prepare
doGenerateDocumentResult result = new doGenerateDocumentResult()
{
ErrorFlag = false,
Total = 0,
Complete = 0,
Failed = 0,
ResultDocumentNoList = string.Empty
};
#endregion
#region Constants
const int ROW_HEADER = 1;
const int COL_HEADER_TITLE = 1;
const int ROW_TBLHDR = 3;
int columncount = 0;
int COL_ROWNUMBER = ++columncount;
int COL_CUSTFULLNAMEEN = ++columncount;
int COL_CONTRACTCODE = ++columncount;
int COL_SITENAMEEN = ++columncount;
int COL_PLANCODE = ++columncount;
int COL_OFFICENAMEEN = ++columncount;
int COL_CUSTACCEPTANCEDATE = ++columncount;
int COL_PRODUCTNAMEEN = ++columncount;
int COL_SALESMANNAME = ++columncount;
int COL_ORDERPRODUCTPRICECURRENCY = ++columncount;
int COL_ORDERPRODUCTPRICE = ++columncount;
int COL_INSTRUMENTCOSTCURRENCY = ++columncount;
int COL_INSTRUMENTCOST = ++columncount;
int COL_ORDERINSTALLFEECURRENCY = ++columncount;
int COL_ORDERINSTALLFEE = ++columncount;
int COL_PAYTOSUBCONTRACTORCURRENCY = ++columncount;
int COL_PAYTOSUBCONTRACTOR = ++columncount;
#endregion
if (doc.GetSheetNames().Contains(sheetname))
{
doc.DeleteWorksheet(sheetname);
}
doc.AddWorksheet(sheetname);
doc.SelectWorksheet(sheetname);
doc.SetCellStyle(ROW_HEADER, COL_HEADER_TITLE, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 12, Bold = true
}
});
doc.SetCellValue(ROW_HEADER, COL_HEADER_TITLE, "New operation sales report");
doc.SetCellStyle(ROW_TBLHDR, COL_HEADER_TITLE, ROW_TBLHDR, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = true
}
});
doc.SetCellValue(ROW_TBLHDR, COL_ROWNUMBER, "NO.");
doc.SetCellValue(ROW_TBLHDR, COL_CUSTFULLNAMEEN, "CUSTFULLNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_CONTRACTCODE, "CONTRACTNO.");
doc.SetCellValue(ROW_TBLHDR, COL_SITENAMEEN, "PREMISE'SNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_PLANCODE, "PLANCODE");
doc.SetCellValue(ROW_TBLHDR, COL_OFFICENAMEEN, "OFFICENAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_CUSTACCEPTANCEDATE, "CUSTACCEPTANCEDATE");
doc.SetCellValue(ROW_TBLHDR, COL_PRODUCTNAMEEN, "PRODUCTNAMEEN");
doc.SetCellValue(ROW_TBLHDR, COL_SALESMANNAME, "SALESMANNAME");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERPRODUCTPRICECURRENCY, "ORDERPRODUCTPRICECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERPRODUCTPRICE, "ORDERPRODUCTPRICE");
doc.SetCellValue(ROW_TBLHDR, COL_INSTRUMENTCOSTCURRENCY, "INSTRUMENTCOSTCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_INSTRUMENTCOST, "INSTRUMENTCOST");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEECURRENCY, "ORDERINSTALLFEECURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_ORDERINSTALLFEE, "ORDERINSTALLFEE");
doc.SetCellValue(ROW_TBLHDR, COL_PAYTOSUBCONTRACTORCURRENCY, "PAYTOSUBCONTRACTORCURRENCY");
doc.SetCellValue(ROW_TBLHDR, COL_PAYTOSUBCONTRACTOR, "PAYTOSUBCONTRACTOR");
var accountData = this.GetAccountDataOfACC010(context.TargetPeriodFrom, context.TargetPeriodTo, context.UserHQCode);
if (accountData.Count == 0)
{
result.ErrorFlag = true;
result.ErrorCode = MessageUtil.MessageList.MSG8005;
return(result);
}
result.Complete = accountData.Count;
result.Total = accountData.Count;
int rowindex = ROW_TBLHDR + 1;
doc.SetCellStyle(rowindex, COL_HEADER_TITLE, rowindex + accountData.Count - 1, columncount, new SLStyle()
{
Font = new SLFont()
{
FontName = "Tahoma", FontSize = 10, Bold = false
}
});
foreach (var rowdata in accountData)
{
if (rowdata.RowNumber != null)
{
doc.SetCellValue(rowindex, COL_ROWNUMBER, rowdata.RowNumber.Value);
}
doc.SetCellValue(rowindex, COL_CUSTFULLNAMEEN, rowdata.CustFullNameEN);
doc.SetCellValue(rowindex, COL_CONTRACTCODE, rowdata.ContractCode);
doc.SetCellValue(rowindex, COL_SITENAMEEN, rowdata.SiteNameEN);
doc.SetCellValue(rowindex, COL_PLANCODE, rowdata.PlanCode);
doc.SetCellValue(rowindex, COL_OFFICENAMEEN, rowdata.OfficeNameEN);
if (rowdata.CustAcceptanceDate != null)
{
doc.SetCellValue(rowindex, COL_CUSTACCEPTANCEDATE, rowdata.CustAcceptanceDate.Value.ToString("dd-MMM-yyyy"));
}
doc.SetCellValue(rowindex, COL_PRODUCTNAMEEN, rowdata.ProductNameEN);
doc.SetCellValue(rowindex, COL_SALESMANNAME, rowdata.SalesmanName);
if (rowdata.OrderProductPriceCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERPRODUCTPRICECURRENCY, rowdata.OrderProductPriceCurrencyType);
}
if (rowdata.OrderProductPrice != null)
{
doc.SetCellValue(rowindex, COL_ORDERPRODUCTPRICE, rowdata.OrderProductPrice.Value);
}
if (rowdata.InstrumentCostCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_INSTRUMENTCOSTCURRENCY, rowdata.InstrumentCostCurrencyType);
}
if (rowdata.InstrumentCost != null)
{
doc.SetCellValue(rowindex, COL_INSTRUMENTCOST, rowdata.InstrumentCost.Value);
}
if (rowdata.OrderInstallFee != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEE, rowdata.OrderInstallFee.Value);
}
if (rowdata.OrderInstallFeeCurrencyType != null)
{
doc.SetCellValue(rowindex, COL_ORDERINSTALLFEECURRENCY, rowdata.OrderInstallFeeCurrencyType);
}
if (rowdata.PayToSubcontractor != null)
{
doc.SetCellValue(rowindex, COL_PAYTOSUBCONTRACTOR, rowdata.PayToSubcontractor.Value);
}
rowindex++;
}
doc.Filter(ROW_TBLHDR, COL_ROWNUMBER, rowindex - 1, COL_PAYTOSUBCONTRACTOR);
doc.SetCellValue(SLConvert.ToCellReference(rowindex, COL_ORDERPRODUCTPRICE), string.Format("=SUBTOTAL({0})", SLConvert.ToCellRange(ROW_TBLHDR + 1, COL_ORDERPRODUCTPRICE, rowindex - 1, COL_ORDERPRODUCTPRICE)));
return(result);
}
/// <summary>
/// Based on ImportDataTable2, added background color to odd rows,
/// conditional formatting, in this case the State column.
/// </summary>
/// <param name="pFileName">Existing excel file</param>
/// <param name="pStartReference">Row to start import</param>
/// <param name="pDataTable">Data source</param>
/// <param name="pIncludeHeaders">Should column headers be used (currently only does headers)</param>
/// <returns></returns>
public bool ImportDataTable4(string pFileName, string pStartReference, DataTable pDataTable, bool pIncludeHeaders = true)
{
bool success = false;
var sheetName = "Demo4";
try
{
// open to Sheet1 (will always exists in this code sample)
using (SLDocument doc = new SLDocument(pFileName, "Sheet1"))
{
var helper = new LightHelpers();
if (helper.SheetExists(pFileName, sheetName))
{
doc.DeleteWorksheet(sheetName);
}
// add the sheet
doc.AddWorksheet(sheetName);
var isSet = doc.SelectWorksheet(sheetName);
// next three lines are responsible for removal of grid-lines in the current WorkSheet
SLPageSettings ps = new SLPageSettings();
ps.ShowGridLines = false;
doc.SetPageSettings(ps);
// setup a format for the date column
var birthdayColumnStyle = doc.CreateStyle();
birthdayColumnStyle.FormatCode = "mm-dd-yyyy";
// create style for odd rows (we skip the header row)
var oddRowStyle = doc.CreateStyle();
oddRowStyle.SetPatternFill(PatternValues.LightGray, SLThemeColorIndexValues.Accent3Color, SLThemeColorIndexValues.Accent3Color);
var moCellStyle = doc.CreateStyle();
moCellStyle.Font.Bold = true;
moCellStyle.Font.FontColor = SysDraw.Color.Red;
// since we need these several times, set them up as variables
var activeCellIndex = pDataTable.Columns["Active"].Ordinal + 1;
var birthDayCellIndex = pDataTable.Columns["BirthDay"].Ordinal + 1;
// set birthday style to our data
doc.SetCellStyle(2, birthDayCellIndex, pDataTable.Rows.Count + 1, birthDayCellIndex, birthdayColumnStyle);
// do the Datatable import.
doc.ImportDataTable(pStartReference, pDataTable, pIncludeHeaders);
SLWorksheetStatistics stats = doc.GetWorksheetStatistics();
// change format of the bool column
for (int row = 1; row < stats.EndRowIndex + 1; row++)
{
var value = doc.GetCellValueAsString(row, activeCellIndex);
if (value == "TRUE")
{
doc.SetCellValue(row, activeCellIndex, "Yes");
}
else if (value == "FALSE")
{
doc.SetCellValue(row, activeCellIndex, "No");
}
// apply background color style to odd rows
if (row.IsOdd() && row > 1)
{
doc.SetRowStyle(row, oddRowStyle);
}
else
{
var cellReference = SLConvert.ToCellReference(row, 6);
var stateValue = doc.GetCellValueAsString(cellReference);
if (stateValue == "MO")
{
doc.SetCellStyle(cellReference, moCellStyle);
}
}
}
// delete the primary key column
doc.DeleteColumn(1, 1);
// does what it says, auto-fit cell content
doc.AutoFitColumn(2, pDataTable.Columns.Count);
// de-select the current sheet and select sheet1
doc.SelectWorksheet("Sheet1");
doc.Save();
success = true;
}
}
catch (Exception ex)
{
HasErrors = true;
ExceptionMessage = ex.Message;
success = false;
}
return(success);
}
static void Main(string[] args)
{
int i, j;
//double fValue;
Random rand = new Random();
double[] doubledata = new double[] { rand.Next(10), rand.NextDouble(), 2.3, 4.5, 6.9 };
using (SLDocument sl = new SLDocument())
{
sl.RenameWorksheet(SLDocument.DefaultFirstSheetName, "Random");
for (i = 1; i <= 16; ++i)
{
for (j = 1; j <= 6; ++j)
{
switch (rand.Next(5))
{
case 0:
case 1:
sl.SetCellValue(i, j, doubledata[rand.Next(doubledata.Length)]);
break;
case 2:
case 3:
sl.SetCellValue(i, j, rand.NextDouble() * 1000.0 + 350.0);
break;
case 4:
if (rand.NextDouble() < 0.5)
{
sl.SetCellValueNumeric(i, j, "3.1415926535898");
}
else
{
sl.SetCellValueNumeric(i, j, "2.7182818284590");
}
break;
}
}
}
//sl.Filter("A1", "F1");
//sl.FlattenAllSharedCellFormula();
//sl.DrawBorderGrid
//sl.SetCellValue("C6", "This is at C6!");
//sl.SetCellValue(SLConvert.ToCellReference(7, 4), string.Format("=SUM({0})", SLConvert.ToCellRange(2, 1, 2, 20)));
//sl.SetCellValue("C8", new DateTime(3141, 5, 9));
//SLStyle style = sl.CreateStyle();
//style.FormatCode = "d-mmm-yyyy";
//sl.SetCellStyle("C8", style);
//for (int i = 1; i <= 20; ++i) sl.SetCellValue(2, i, i);
sl.SaveAs("Miscellaneous1.xlsx");
SLDocument tl = new SLDocument("Miscellaneous1.xlsx", "Sheet");
//以下两个方法功能一致
tl.SetCellValue("G1", "=SUM(A1:F2)");
tl.SetCellValue(SLConvert.ToCellReference(2, 7), string.Format("=SUM({0})", SLConvert.ToCellRange(1, 1, 2, 6)));
tl.SetCellValue("G3", "=A2-A3");
//tl.SetCellValueNumeric("G6", "=AVERAGE(A1:F2)");
//tl.SetCellValue("G2", string.Format(" =SUM({0})", SLConvert.ToCellRange(1, 1, 2, 6)));
//以下两个方法功能一致
tl.SetCellValue("G4", StringValue.ToString("=AVERAGE(A1:F2)"));
tl.SetCellValue("G5", String.Format("=AVERAGE({0})", SLConvert.ToCellRange(1, 1, 2, 6)));
tl.SetCellValue("G6", "So this is the random number table");
tl.SetCellValue("G7", "The time is ");
tl.SetCellValue("H7", DateTime.Now.ToString()); //获取当前日期和时间
tl.AddWorksheet("Secret"); //添加工作表
tl.SelectWorksheet("Random"); //选择Random工作表
tl.RenameWorksheet("Secret", "again"); //重命名Secret
SLStyle style = tl.CreateStyle(); //设置单元格格式
style.SetFont("Impact", 24);
style.Font.Underline = UnderlineValues.Single;
tl.SetCellStyle(1, 7, style);
tl.SetCellStyle("G6", style);
//设置EXCEL属性
tl.DocumentProperties.Creator = "ZhouL";
tl.DocumentProperties.ContentStatus = "Secret";
tl.DocumentProperties.Title = "Random number table";
tl.DocumentProperties.Description = "Get data and manipulate it and export it";
tl.SaveAs("MiscellaneousModified.xlsx");
}
Console.WriteLine("End of program");
Console.ReadLine();
}