/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 表头及样式
//{
// XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
// headerRow.HeightInPoints = 25;
// headerRow.CreateCell(0).SetCellValue(strHeaderText);
// XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
// headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
// XSSFFont font = workbook.CreateFont() as XSSFFont;
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// headStyle.SetFont(font);
// headerRow.GetCell(0).CellStyle = headStyle;
// //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// //headerRow.Dispose();
//}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// 写入DataTable到Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="excelFile"></param>
public virtual void writeDataTableToExcel(DataTable dt, string excelFile)
{
//Excel数据
MemoryStream memoryStream = new MemoryStream();
//创建Workbook
NPOI.XSSF.UserModel.XSSFWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region 设置Excel样式
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleA = workbook.CreateCellStyle();
cellStyleA.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellStyleA.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleA.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleA.WrapText = true;
//创建单元格设置对象(普通内容)
NPOI.SS.UserModel.ICellStyle cellStyleB = workbook.CreateCellStyle();
cellStyleB.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyleB.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
cellStyleB.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyleB.WrapText = true;
//创建设置字体对象(内容字体)
NPOI.SS.UserModel.IFont fontA = workbook.CreateFont();
fontA.FontHeightInPoints = 16;//设置字体大小
fontA.FontName = "宋体";
cellStyleA.SetFont(fontA);
//创建设置字体对象(标题字体)
NPOI.SS.UserModel.IFont fontB = workbook.CreateFont();
fontB.FontHeightInPoints = 16;//设置字体大小
fontB.FontName = "宋体";
fontB.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellStyleB.SetFont(fontB);
#endregion
//写入基本数据
writeSheet(workbook, cellStyleA, cellStyleB, dt);
#region 输出文件
//输出到流
workbook.Write(memoryStream);
//写Excel文件
File.WriteAllBytes(excelFile, memoryStream.ToArray());
#endregion
}
static void Main(string[] args)
{
IWorkbook wb = new XSSFWorkbook();
// Create a Worksheet
ISheet ws = wb.CreateSheet("Sheet1");
// Create a new font and alter it
IFont font = wb.CreateFont();
font.FontHeightInPoints = 24;
font.FontName = "Courier New";
font.IsItalic = true;
font.IsStrikeout = true;
// Fonts are set into a style so create a new one to use.
ICellStyle style = wb.CreateCellStyle();
style.SetFont(font);
IRow row = ws.CreateRow(0);
// Create a cell and put a value in it.
ICell cell = row.CreateCell(1);
cell.SetCellValue("Thisi s a test of fonts");
cell.CellStyle = style;
FileStream sw = File.Create("test.xlsx");
wb.Write(sw);
sw.Close();
}
public void TestSetString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet();
XSSFComment comment = (XSSFComment)sh.CreateDrawingPatriarch().CreateCellComment(new XSSFClientAnchor());
//passing HSSFRichTextString is incorrect
try
{
comment.String = (new HSSFRichTextString(TEST_RICHTEXTSTRING));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Only XSSFRichTextString argument is supported", e.Message);
}
//simple string argument
comment.SetString(TEST_RICHTEXTSTRING);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//if the text is already Set, it should be overridden, not Added twice!
comment.SetString(TEST_RICHTEXTSTRING);
CT_Comment ctComment = comment.GetCTComment();
// Assert.Fail("TODO test case incomplete!?");
//XmlObject[] obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//sequential call of comment.String should return the same XSSFRichTextString object
Assert.AreSame(comment.String, comment.String);
XSSFRichTextString richText = new XSSFRichTextString(TEST_RICHTEXTSTRING);
XSSFFont font1 = (XSSFFont)wb.CreateFont();
font1.FontName = ("Tahoma");
font1.FontHeight = 8.5;
font1.IsItalic = true;
font1.Color = IndexedColors.BlueGrey.Index;
richText.ApplyFont(0, 5, font1);
//check the low-level stuff
comment.String = richText;
//obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreSame(comment.String, richText);
//check that the rich text is Set in the comment
CT_RPrElt rPr = richText.GetCTRst().GetRArray(0).rPr;
Assert.AreEqual(true, rPr.GetIArray(0).val);
Assert.AreEqual(8.5, rPr.GetSzArray(0).val);
Assert.AreEqual(IndexedColors.BlueGrey.Index, (short)rPr.GetColorArray(0).indexed);
Assert.AreEqual("Tahoma", rPr.GetRFontArray(0).val);
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(anchor);
c1.LineWidth = 3;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(anchor);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(anchor);
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(anchor);
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
////cell style for hyperlinks
////by default hyperlinks are blue and underlined
ICellStyle hlink_style = workbook.CreateCellStyle();
IFont hlink_font = workbook.CreateFont();
hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
hlink_font.Color = HSSFColor.BLUE.index;
hlink_style.SetFont(hlink_font);
ICell cell;
ISheet sheet = workbook.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell(0);
cell.SetCellValue("URL Link");
XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell(0);
cell.SetCellValue("File Link");
link = new XSSFHyperlink(HyperlinkType.FILE);
link.Address = ("link1.xls");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//e-mail link
cell = sheet.CreateRow(2).CreateCell(0);
cell.SetCellValue("Email Link");
link = new XSSFHyperlink(HyperlinkType.EMAIL);
//note, if subject contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
//link to a place in this workbook
//Create a target sheet and cell
ISheet sheet2 = workbook.CreateSheet("Target ISheet");
sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");
cell = sheet.CreateRow(3).CreateCell(0);
cell.SetCellValue("Worksheet Link");
link = new XSSFHyperlink(HyperlinkType.DOCUMENT);
link.Address = ("'Target ISheet'!A1");
cell.Hyperlink = (link);
cell.CellStyle = (hlink_style);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
protected void btn_click(object sender, EventArgs e)
{
//FileStream fs = new FileStream(Server.MapPath(@"\Content\Sample.xlsx"), FileMode.Open, FileAccess.Read);
//XSSFWorkbook temWorkBook = new XSSFWorkbook(fs);
//ISheet nsheet = temWorkBook.GetSheet("Sheet1");
//IRow datarow = nsheet.GetRow(4);
//datarow.GetCell(0).SetCellValue(77);
//nsheet.ForceFormulaRecalculation = true;
//using (var ms = new MemoryStream())
//{
// temWorkBook.Write(ms);
// Response.Clear();
// Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
// Response.AppendHeader("Content-Disposition","inline;filename=Sample"+DateTime.Now.ToString("yyyyMMMdd")+".xlsx");
// Response.BinaryWrite(ms.ToArray());
// Response.End();
//}
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("New Sheet");
ISheet sheet2 = workbook.CreateSheet("Second Sheet");
ICell cell1 = sheet1.CreateRow(0).CreateCell(0);
IFont fontBold = workbook.CreateFont();
fontBold.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(fontBold);
cell1.CellStyle = style1;
cell1.SetCellValue("sample value");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for(int j = 0;j < 15;j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
Response.Clear();
Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AppendHeader("Content-Disposition", "inline;filename=Sample" + DateTime.Now.ToString("yyyyMMMdd") + ".xlsx");
Response.BinaryWrite(ms.ToArray());
Response.End();
}
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont());
Assert.AreEqual(18, orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont());
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
clone.Alignment = HorizontalAlignment.Left;
clone.DataFormat = 17;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(18, orig.DataFormat);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestSetTextSingleParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromArgb(0, 255, 255)));
font.FontName = ("Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
Assert.AreEqual("Test String", paras[0].Text);
List <XSSFTextRun> runs = paras[0].TextRuns;
Assert.AreEqual(1, runs.Count);
Assert.AreEqual("Arial", runs[0].FontFamily);
Color clr = runs[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb);
wb.Close();
}
private void button8_Click(object sender, EventArgs e)
{
ConexionPostgres conn = new ConexionPostgres();
var cadena = "SELECT id_pregunta from modelo.pregunta_actual where nit='" + this.valor + "'";
var id_pregunta = conn.consultar(cadena)[0]["id_pregunta"];
XSSFWorkbook workbook = new XSSFWorkbook();
//Se crea una hoja para el libro de la (hoja de cálculo)
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet1"); ;
//Creo una variable que es similar a la que se retorna en las consultas SQL
var cadenaSql = " SELECT DISTINCT ur.nit, ur.numero_unidad, ur.nombre_completo, ur.coeficiente, v.id_pregunta, v.id_opcion FROM modelo.unidad_residencial AS ur LEFT OUTER JOIN modelo.voto AS v ON ur.numero_unidad = v.numero_unidad left outer JOIN modelo.pregunta_actual AS pa ON pa.id_pregunta=v.id_pregunta WHERE ur.nit = '" + this.valor + "' and v.id_pregunta = '" + id_pregunta + "' order by ur.numero_unidad asc;";
var resultado = conn.consultar(cadenaSql);
var cadenasql = " SELECT p.pregunta FROM modelo.pregunta AS p LEFT OUTER JOIN modelo.voto AS v ON p.id_pregunta = '" + id_pregunta + "' LEFT OUTER JOIN modelo.pregunta_actual AS pa ON pa.id_pregunta = v.id_pregunta WHERE v.nit = '" + this.valor + "' ";
var result = conn.consultar(cadenasql);
var Cadenasql = "SELECT DISTINCT o.id_opcion, o.opcion FROM modelo.opcion_pregunta AS o LEFT OUTER JOIN modelo.voto AS v ON o.id_pregunta = '" + id_pregunta + "' LEFT OUTER JOIN modelo.pregunta_actual AS pa ON pa.id_pregunta = '" + id_pregunta + "' WHERE v.nit = '" + this.valor + "' ORDER BY o.id_opcion ";
var Resultado = conn.consultar(Cadenasql);
//Se escriben las cabeceras del reporte, primero se crea la fila
var primerFilaExcel = sheet.CreateRow(1);
//También creo una fuente NEGRILLA para ponerselas a esas celdas
var boldFont = workbook.CreateFont();
boldFont.FontHeightInPoints = 11;
boldFont.FontName = "Calibri";
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var style = workbook.CreateCellStyle();
style.SetFont(boldFont);
//Se crea una celda, se le pone el estilo NEGRILLA y se le pone el valor
var rowExcel = sheet.GetRow(1);
rowExcel = sheet.GetRow(1);
var cell = primerFilaExcel.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("NIT");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue("NUMERO UNIDAD");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue("NOMBRE");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue("COEFICIENTE");
////OTRA CELDA
cell = primerFilaExcel.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue("VOTACION");
////OTRA CELDA
cell = primerFilaExcel.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue("NUMERO");
////OTRA CELDA
cell = primerFilaExcel.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue("OPCION");
////OTRA CELDA
cell = primerFilaExcel.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue("NUMERO DE VOTOS");
////OTRA CELDA
cell = primerFilaExcel.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue("COEFICIENTE");
//Se desocupa la variables para que no ocupen espacio
cell = null;
primerFilaExcel = null;
int b = 0;
Dictionary<string, string> fila1 = result[b];
var filaExcel1 = sheet.CreateRow(0);//La fila comienza desde la posición 2
filaExcel1.CreateCell(0).SetCellValue(fila1["pregunta"]);
//Se crea un for como siempre que recorre el resultado
for (int a = 1; a < Resultado.ToArray().Length; a++)
{
string quorum1 = "";
Dictionary<string, string> fila3 = Resultado[a];
var filaExcell = sheet.CreateRow(a + 2);//La fila comienza desde la posición 2
filaExcell.CreateCell(8).SetCellValue(fila3["id_opcion"]);
filaExcell.CreateCell(9).SetCellValue(fila3["opcion"]);
//var coef = "SELECT sum(UR.coeficiente) FROM modelo.unidad_residencial AS ur LEFT OUTER JOIN modelo.voto AS v ON ur.numero_unidad = v.numero_unidad left outer JOIN modelo.pregunta_actual AS pa ON pa.id_pregunta=v.id_pregunta WHERE ur.nit = '"+this.valor+"' and v.id_pregunta = '"+id_pregunta+"' AND v.id_opcion='"+a+"';";
//double registradosCasoCoeficientes1 = Double.Parse(conn.consultar(coef)[0]["sum"]);
//quorum1 = (registradosCasoCoeficientes1).ToString();
}
//var rowExcel = sheet.GetRow(1);
for (int i = 0; i < resultado.Count; i++)
{
Dictionary<string, string> fila = resultado[i];
var filaExcel = sheet.CreateRow(i + 2);//La fila comienza desde la posición 2
filaExcel.CreateCell(0).SetCellValue(fila["nit"]);
filaExcel.CreateCell(1).SetCellValue(fila["numero_unidad"]);
filaExcel.CreateCell(2).SetCellValue(fila["nombre_completo"]);
filaExcel.CreateCell(3).SetCellValue(fila["coeficiente"]);
filaExcel.CreateCell(4).SetCellValue(fila["id_opcion"]);
}
using (var fs = new FileStream("votacion" + this.valor + ".xlsx", FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
fs.Close();
//borrar anuncio cuando ya no sea necesario
MessageBox.Show("El archivo se guardó en la ruta: " + fs.Name);
}
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List<POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(anchor);
c1.LineWidth=3;
c1.LineStyle= SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(anchor);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(anchor);
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(anchor);
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill=(true);
}
public void TestRichText()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromArgb(0, 128, 128)));
font.IsItalic = (true);
font.IsBold=(true);
font.Underline=FontUnderlineType.Single;
rt.ApplyFont(font);
shape.SetText(rt);
CT_TextParagraph pr = shape.GetCTShape().txBody.p[0];
Assert.AreEqual(1, pr.SizeOfRArray());
CT_TextCharacterProperties rPr = pr.r[0].rPr;
Assert.AreEqual(true, rPr.b);
Assert.AreEqual(true, rPr.i);
Assert.AreEqual(ST_TextUnderlineType.sng, rPr.u);
Assert.IsTrue(Arrays.Equals(
new byte[] { 0, (byte)128, (byte)128 },
rPr.solidFill.srgbClr.val));
}
private string ExportExcel(List<IWEHAVE.ERP.CenterBE.Deploy.HandleNumDTO> hnDTOList, List<IWEHAVE.ERP.CenterBE.Deploy.HandleQuanDTO> hqDTOList, string fileName)
{
if (hnDTOList == null || hqDTOList == null)
{
return string.Empty;
}
string url = "http://blessing.wang/ExcelModel/model.xlsx";
WebRequest request = WebRequest.Create(url);
WebResponse response = request.GetResponse();
Stream stream = response.GetResponseStream();
XSSFWorkbook workbook = new XSSFWorkbook(stream);
//导出个数
ISheet sheet = workbook.GetSheet("寄出个数");
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(1);
cell.SetCellValue(hnDTOList.Count + "个");
row = sheet.GetRow(2);
cell = row.GetCell(7);
cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
//设置单元格格式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int i = 0; i < hnDTOList.Count; i++)
{
row = sheet.CreateRow(5 + i);
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].ContractNo);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Student);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Application);
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Visa);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue("□拒");
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Author);
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].FirstFour);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Doctor);
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Cooperation);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(hnDTOList[i].Note);
}
style = workbook.CreateCellStyle();
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(5 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("编制人签名:");
cell.CellStyle = style;
cell = row.CreateCell(2);
cell.SetCellValue("经理审核签名：");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("总经理/分管总裁批准签字：");
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue("保持：人力资源中心");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1．《文案工作进度（量化）日报单》汇总动态记录PM-16《文案工作量化日报表》；此单必须机打，手写量化将直接作废；");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(7 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2．本报单请于16:00之前申报， □确认√确认填报项。请勿集中中延期申报");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(8 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3．高端合同（英国前四所大学、博士）需在“英国前四所大学”、“博士”项点黑");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
row = sheet.CreateRow(9 + hnDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4．国内合作合同需在“国内合作”项点黑");
cell.CellStyle = style;
sheet.AddMergedRegion(new CellRangeAddress(row.RowNum, row.RowNum, 0, 10));
//导出量化
sheet = workbook.GetSheet("寄出量化");
row = sheet.GetRow(1);
cell = row.GetCell(1);
cell.SetCellValue(hqDTOList.Count + "个");
row = sheet.GetRow(2);
cell = row.GetCell(6);
cell.SetCellValue(this.HandleDate.ToString("yyyy/MM/dd"));
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
for (int i = 0; i < hqDTOList.Count; i++)
{
row = sheet.CreateRow(5 + i);
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].ContractNo);
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Student);
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue("");
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].School);
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Online);
cell = row.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Ps);
cell = row.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue("□");
cell = row.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Visa);
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue("□");
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].ApplicationType);
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Author);
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(hqDTOList[i].Note);
}
style = workbook.CreateCellStyle();
font = workbook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 9;
style.SetFont(font);
row = sheet.CreateRow(5 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("编制人签名:");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核签名：");
cell.CellStyle = style;
cell = row.CreateCell(7);
cell.SetCellValue("总经理/分管总裁批准签字：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("保持：人力资源中心");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("说明：");
cell.CellStyle = style;
cell = row.CreateCell(1);
cell.SetCellValue("1.高端研究生");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("5.艺术");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("9.老学生换无条件材料翻译");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("13.有拒签史学生签证（VO）");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("17.学生签证（VO)");
cell.CellStyle = style;
row = sheet.CreateRow(7 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("2.博士");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("6.其他只申请语言学校");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("10.国内合作");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("14.探亲、访友、陪读 (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(8 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("3.非高端研究生");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("7.个人陈述+推荐信");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("11.本科预科和研究生预科/高中");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("15.学生访问 (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hqDTOList.Count);
cell = row.CreateCell(1);
cell.SetCellValue("4.本科");
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("8.单写个人陈述（推荐信）");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("12.至尊高中");
cell.CellStyle = style;
cell = row.CreateCell(8);
cell.SetCellValue("16.结婚、学术访问、定居、团聚、工作签证 (VO)");
cell.CellStyle = style;
row = sheet.CreateRow(10 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1．文案工作进度（量化）日报单》汇总动态记录JX-16《文案工作量化日报表》；此单必须机打，手写量化将直接作废；");
cell.CellStyle = style;
row = sheet.CreateRow(11 + hqDTOList.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2．本报单请于16:00之前申报，□确认√确认填报项。请勿集中延期申报");
cell.CellStyle = style;
//保存为文件
string path = HttpContext.Current.Server.MapPath("/ExportTemp/");
DirectoryInfo info = new DirectoryInfo(path);
FileInfo[] files = info.GetFiles();
if (files.Length > 0)
{
for (int i = files.Length - 1; i >= 0; i--)
{
files[i].Delete();
}
}
fileName += ".xlsx";
FileStream file = File.Create(path + fileName);
workbook.Write(file);
file.Close();
stream.Close();
return "http://blessing.wang/ExportTemp/" + fileName;
}
public void Test48325()
{
XSSFWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.CreateSheet("Test");
ICreationHelper factory = wb.GetCreationHelper();
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
IFont font = wb.CreateFont();
IRichTextString rts = factory.CreateRichTextString("");
rts.ApplyFont(font);
cell.SetCellValue(rts);
sheet.AutoSizeColumn(0);
}
public IHttpActionResult ExportGroupedSalesToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] headers = new string[] { filter.GroupBy == 1 ? "Practice Name" : filter.GroupBy == 2 ? "Rep Name" : "Sales Team", "Sales", "Last Activity On" };
var headerRow = sheet.CreateRow(0);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllGroupedSales(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <GroupedSales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
var objSalesItem = salesList[i];
if (objSalesItem == null)
{
continue;
}
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell1 = row.CreateCell(0);
cell1.CellStyle = workbook.CreateCellStyle();
cell1.CellStyle.SetFont(font);
cell1.SetCellValue(string.IsNullOrEmpty(objSalesItem.KeyName) ? "Missing Information" : objSalesItem.KeyName);
var cell2 = row.CreateCell(1);
cell2.CellStyle = workbook.CreateCellStyle();
cell2.CellStyle.SetFont(font);
cell2.SetCellValue(objSalesItem.Count);
var cell3 = row.CreateCell(2);
cell3.CellStyle = workbook.CreateCellStyle();
cell3.CellStyle.SetFont(font);
cell3.SetCellValue(Convert.ToString(objSalesItem.LastActivityOn));
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.RIGHT);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.RIGHT,orig.Alignment);
Assert.AreEqual(fnt,orig.GetFont());
Assert.AreEqual(18,orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreNotEqual(fnt,clone.GetFont());
Assert.AreNotEqual(18,clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
}
private IWorkbook GenerateHeader()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheetReport = workbook.CreateSheet("Report");
IFont fontBold = workbook.CreateFont();
fontBold.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle styleFontBold = workbook.CreateCellStyle();
styleFontBold.SetFont(fontBold);
ICell cell1 = sheetReport.CreateRow(0).CreateCell(0);
cell1.CellStyle = styleFontBold;
cell1.SetCellValue("From " + txtBoxFrom.Text + " to " + txtBoxTo.Text);
sheetReport.CreateRow(1).CreateCell(0).SetCellValue("Labor Cost Report");
sheetReport.GetRow(1).GetCell(0).CellStyle = styleFontBold;
return workbook;
}
public void Test48877()
{
String text = "Use \n with word wrap on to create a new line.\n" +
"This line finishes with two trailing spaces. ";
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
IFont font1 = wb.CreateFont();
font1.Color=((short)20);
IFont font2 = wb.CreateFont();
font2.Color = (short)(FontColor.RED);
IFont font3 = wb.GetFontAt((short)0);
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(2) as XSSFCell;
XSSFRichTextString richTextString =
wb.GetCreationHelper().CreateRichTextString(text) as XSSFRichTextString;
// Check the text has the newline
Assert.AreEqual(text, richTextString.String);
// Apply the font
richTextString.ApplyFont(font3);
richTextString.ApplyFont(0, 3, font1);
cell.SetCellValue(richTextString);
// To enable newlines you need Set a cell styles with wrap=true
ICellStyle cs = wb.CreateCellStyle();
cs.WrapText=(true);
cell.CellStyle=(cs);
// Check the text has the
Assert.AreEqual(text, cell.StringCellValue);
// Save the file and re-read it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
cell = row.GetCell(2) as XSSFCell;
Assert.AreEqual(text, cell.StringCellValue);
// Now add a 2nd, and check again
int fontAt = text.IndexOf("\n", 6);
cell.RichStringCellValue.ApplyFont(10, fontAt + 1, font2);
Assert.AreEqual(text, cell.StringCellValue);
Assert.AreEqual(4, (cell.RichStringCellValue as XSSFRichTextString).NumFormattingRuns);
Assert.AreEqual("Use", (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[0].t);
String r3 = (cell.RichStringCellValue as XSSFRichTextString).GetCTRst().r[2].t;
Assert.AreEqual("line.\n", r3.Substring(r3.Length - 6));
// Save and re-check
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
cell = row.GetCell(2) as XSSFCell;
Assert.AreEqual(text, cell.StringCellValue);
// FileOutputStream out = new FileOutputStream("/tmp/test48877.xlsx");
// wb.Write(out);
// out.Close();
}
public void Test49941()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet s = wb.CreateSheet() as XSSFSheet;
XSSFRow r = s.CreateRow(0) as XSSFRow;
XSSFCell c = r.CreateCell(0) as XSSFCell;
// First without fonts
c.SetCellValue(
new XSSFRichTextString(" with spaces ")
);
Assert.AreEqual(" with spaces ", c.RichStringCellValue.ToString());
Assert.AreEqual(0, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray());
Assert.AreEqual(true, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().IsSetT());
// Should have the preserve Set
//TODO: make test pass
Assert.Fail("xml serializer bug about preserve spaces");
//Assert.AreEqual(
// 1,
// (c.RichStringCellValue as XSSFRichTextString).GetCTRst().xgetT().GetDomNode().GetAttributes().GetLength()
//);
//Assert.AreEqual(
// "preserve",
// (c.RichStringCellValue as XSSFRichTextString).GetCTRst().xgetT().GetDomNode().GetAttributes().item(0).GetNodeValue()
//);
// Save and check
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
s = wb.GetSheetAt(0) as XSSFSheet;
r = s.GetRow(0) as XSSFRow;
c = r.GetCell(0) as XSSFCell;
Assert.AreEqual(" with spaces ", c.RichStringCellValue.ToString());
Assert.AreEqual(0, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray());
Assert.AreEqual(true, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().IsSetT());
// Change the string
c.SetCellValue(
new XSSFRichTextString("hello world")
);
Assert.AreEqual("hello world", c.RichStringCellValue.ToString());
// Won't have preserve
//Assert.AreEqual(
// 0,
// c.RichStringCellValue.GetCTRst().xgetT().GetDomNode().GetAttributes().GetLength()
//);
// Apply a font
XSSFFont f = wb.CreateFont() as XSSFFont;
f.IsBold=(true);
c.RichStringCellValue.ApplyFont(0, 5, f);
Assert.AreEqual("hello world", c.RichStringCellValue.ToString());
// Does need preserving on the 2nd part
Assert.AreEqual(2, (c.RichStringCellValue as XSSFRichTextString).GetCTRst().sizeOfRArray());
//Assert.AreEqual(
// 0,
// c.RichStringCellValue.GetCTRst().GetRArray(0).xgetT().GetDomNode().GetAttributes().GetLength()
//);
//Assert.AreEqual(
// 1,
// c.RichStringCellValue.GetCTRst().GetRArray(1).xgetT().GetDomNode().GetAttributes().GetLength()
//);
//Assert.AreEqual(
// "preserve",
// c.RichStringCellValue.GetCTRst().GetRArray(1).xgetT().GetDomNode().GetAttributes().item(0).GetNodeValue()
//);
// Save and check
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
s = wb.GetSheetAt(0) as XSSFSheet;
r = s.GetRow(0) as XSSFRow;
c = r.GetCell(0) as XSSFCell;
Assert.AreEqual("hello world", c.RichStringCellValue.ToString());
}
public void TestAddNewTextParagraphWithRTS()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
XSSFRichTextString rt = new XSSFRichTextString("Test Rich Text String");
XSSFFont font = wb1.CreateFont() as XSSFFont;
font.SetColor(new XSSFColor(Color.FromArgb(0, 255, 255)));
font.FontName = ("Arial");
rt.ApplyFont(font);
XSSFFont midfont = wb1.CreateFont() as XSSFFont;
midfont.SetColor(new XSSFColor(Color.FromArgb(0, 255, 0)));
rt.ApplyFont(5, 14, midfont); // Set the text "Rich Text" to be green and the default font
XSSFTextParagraph para = shape.AddNewTextParagraph(rt);
// Save and re-load it
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.GetSheetAt(0) as XSSFSheet;
// Check
Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> shapes = Drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape sshape = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = sshape.TextParagraphs;
Assert.AreEqual(2, paras.Count); // this should be 2 as XSSFSimpleShape Creates a default paragraph (no text), and then we add a string to that.
List <XSSFTextRun> runs = para.TextRuns;
Assert.AreEqual(3, runs.Count);
// first run properties
Assert.AreEqual("Test ", runs[0].Text);
Assert.AreEqual("Arial", runs[0].FontFamily);
Color clr = runs[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
// second run properties
Assert.AreEqual("Rich Text", runs[1].Text);
Assert.AreEqual(XSSFFont.DEFAULT_FONT_NAME, runs[1].FontFamily);
clr = runs[1].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 0 },
new int[] { clr.R, clr.G, clr.B }));
// third run properties
Assert.AreEqual(" String", runs[2].Text);
Assert.AreEqual("Arial", runs[2].FontFamily);
clr = runs[2].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb2);
wb2.Close();
}
public void CreateExcelFile()
{
string FileName = @"d:\5.5.5.101_5555_Log_18.txt";
string[] filelist = File.ReadAllLines(FileName, Encoding.Default);
List <string> StringLists = new List <string>();
int gg = 0;
for (int linenum = filelist.Length - 1; linenum >= 0; linenum--)
{
if (filelist[linenum].IndexOf("ANR") > -1)
{
int first = filelist[linenum].IndexOf("ANR in ") + "ANR in ".Length;
int last = filelist[linenum].LastIndexOf(" (");
string str2 = filelist[linenum].Substring(first, last - first);
StringLists.Add(str2);
}
}
////建立Excel 2007檔案
IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//合併區
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(5, 5, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(7, 7, 1, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 0, 6)); //合併Summary行
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10, 10, 0, 5)); //合併Error List行
//背景色（藍色）
ICellStyle cellStyle0 = workbook.CreateCellStyle();
cellStyle0.FillPattern = FillPattern.SolidForeground;
cellStyle0.FillForegroundColor = IndexedColors.PaleBlue.Index;
cellStyle0.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle0.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（綠色）
ICellStyle cellStyle1 = workbook.CreateCellStyle();
cellStyle1.FillPattern = FillPattern.SolidForeground;
cellStyle1.FillForegroundColor = IndexedColors.Lime.Index;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（粉色）
ICellStyle cellStyle2 = workbook.CreateCellStyle();
cellStyle2.FillPattern = FillPattern.SolidForeground;
cellStyle2.FillForegroundColor = IndexedColors.Tan.Index;
cellStyle2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle2.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//背景色（灰色）
ICellStyle cellStyle3 = workbook.CreateCellStyle();
cellStyle3.FillPattern = FillPattern.SolidForeground;
cellStyle3.FillForegroundColor = IndexedColors.Grey25Percent.Index;
//背景色（白色）
ICellStyle cellStyle4 = workbook.CreateCellStyle();
cellStyle4.FillPattern = FillPattern.SolidForeground;
cellStyle4.FillForegroundColor = IndexedColors.White.Index;
//Summary儲存格格式
ICellStyle summaryStyle = workbook.CreateCellStyle();
IFont summaryFont = workbook.CreateFont();
summaryFont.FontHeightInPoints = 18;
summaryStyle.SetFont(summaryFont);
summaryStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
summaryStyle.FillPattern = FillPattern.SolidForeground;
summaryStyle.FillForegroundColor = IndexedColors.PaleBlue.Index;
//A列
sheet.CreateRow(0).CreateCell(0).SetCellValue("Project Name");
sheet.CreateRow(1).CreateCell(0).SetCellValue("Model Name");
sheet.CreateRow(2).CreateCell(0).SetCellValue("Start Time");
sheet.CreateRow(3).CreateCell(0).SetCellValue("Renew Time");
sheet.CreateRow(4).CreateCell(0).SetCellValue("SW Build Time");
sheet.CreateRow(5).CreateCell(0).SetCellValue("Project No.");
sheet.CreateRow(6).CreateCell(0).SetCellValue("Test Device");
sheet.CreateRow(7).CreateCell(0).SetCellValue("Tester");
for (int A = 0; A < 8; A++)
{
sheet.GetRow(A).GetCell(0).CellStyle = cellStyle0;
}
//E列
sheet.GetRow(0).CreateCell(4).SetCellValue("Date");
sheet.GetRow(1).CreateCell(4).SetCellValue("Period (H)");
sheet.GetRow(2).CreateCell(4).SetCellValue("SW ISSUES");
sheet.GetRow(3).CreateCell(4).SetCellValue("System Crash");
sheet.GetRow(4).CreateCell(4).SetCellValue("Result");
sheet.GetRow(5).CreateCell(4).SetCellValue("MTBF_SW");
sheet.GetRow(6).CreateCell(4).SetCellValue("MTBF_Crash");
for (int E = 0; E < 7; E++)
{
sheet.GetRow(E).GetCell(4).CellStyle = cellStyle0;
}
sheet.GetRow(4).GetCell(4).CellStyle = cellStyle4;
//F列
sheet.GetRow(0).CreateCell(5).SetCellValue("-----");
sheet.GetRow(1).CreateCell(5).SetCellValue("-----");
sheet.GetRow(2).CreateCell(5).SetCellValue("-----");
sheet.GetRow(3).CreateCell(5).SetCellValue("-----");
sheet.GetRow(4).CreateCell(5).SetCellValue("");
sheet.GetRow(5).CreateCell(5).SetCellValue("-----");
sheet.GetRow(6).CreateCell(5).SetCellValue("-----");
for (int F = 0; F < 7; F++)
{
sheet.GetRow(F).GetCell(5).CellStyle = cellStyle2;
}
sheet.GetRow(4).GetCell(5).CellStyle = cellStyle4;
//Summary
sheet.CreateRow(9).CreateCell(0).SetCellValue("Summary");
sheet.GetRow(9).GetCell(0).CellStyle = summaryStyle;
//Error List
sheet.CreateRow(10).CreateCell(0).SetCellValue("Error List");
sheet.GetRow(10).GetCell(0).CellStyle = cellStyle3;
//Total
sheet.GetRow(10).CreateCell(6).SetCellValue("Total");
sheet.GetRow(10).GetCell(6).CellStyle = cellStyle3;
//搜尋相同字串並記次
Dictionary <string, int> dict = new Dictionary <string, int>();
foreach (string myStringList in StringLists)
{
if (dict.ContainsKey(myStringList))
{
//如果Dictionary中存在这个关键词元素，则把这个Dictionary的key+1
dict[myStringList]++;
}
else
{
//如果Dictionary中不存在这个关键词元素，则把它添加进Dictionary
dict.Add(myStringList, 1);
}
}
int rowcnt = dict.Count;
while (rowcnt != 0)
{
foreach (KeyValuePair <string, int> item in dict)
{
Console.WriteLine(item.Key);
Console.WriteLine(item.Value);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(10 + rowcnt, 10 + rowcnt, 0, 5)); //合併Error List行
sheet.CreateRow(10 + rowcnt).CreateCell(0).SetCellValue(item.Key);
sheet.GetRow(10 + rowcnt).CreateCell(6).SetCellValue(item.Value);
rowcnt--;
}
}
for (int c = 0; c <= 25; c++)
{
sheet.AutoSizeColumn(c);
}
FileStream file = new FileStream(@"d:\npoi.xlsx", FileMode.Create);//產生檔案
workbook.Write(file);
file.Close();
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("some comments");
// Create the drawing patriarch. This is the top level container for all shapes including cell comments.
IDrawing patr = sheet.CreateDrawingPatriarch();
//Create a cell in row 3
ICell cell1 = sheet.CreateRow(3).CreateCell(1);
cell1.SetCellValue(new XSSFRichTextString("Hello, World"));
//anchor defines size and position of the comment in worksheet
IComment comment1 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 2, 6, 5));
// set text in the comment
comment1.String = (new XSSFRichTextString("We can set comments in POI"));
//set comment author.
//you can see it in the status bar when moving mouse over the commented cell
comment1.Author = ("Apache Software Foundation");
// The first way to assign comment to a cell is via HSSFCell.SetCellComment method
cell1.CellComment = (comment1);
//Create another cell in row 6
ICell cell2 = sheet.CreateRow(6).CreateCell(1);
cell2.SetCellValue(36.6);
IComment comment2 = patr.CreateCellComment(new XSSFClientAnchor(0, 0, 0, 0, 4, 8, 6, 11));
//modify background color of the comment
//comment2.SetFillColor(204, 236, 255);
XSSFRichTextString str = new XSSFRichTextString("Normal body temperature");
//apply custom font to the text in the comment
IFont font = workbook.CreateFont();
font.FontName = ("Arial");
font.FontHeightInPoints = 10;
font.Boldweight = (short)FontBoldWeight.Bold;
font.Color = HSSFColor.Red.Index;
str.ApplyFont(font);
comment2.String = str;
comment2.Visible = true; //by default comments are hidden. This one is always visible.
comment2.Author = "Bill Gates";
/**
* The second way to assign comment to a cell is to implicitly specify its row and column.
* Note, it is possible to set row and column of a non-existing cell.
* It works, the commnet is visible.
*/
comment2.Row = 6;
comment2.Column = 1;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
public void XSSFTextParagraph_()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/"Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List<XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
XSSFTextParagraph text = paras[(0)];
Assert.AreEqual("Test String", text.Text);
Assert.IsFalse(text.IsBullet);
Assert.IsNotNull(text.GetXmlObject());
Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
Assert.IsNotNull(text.GetEnumerator());
Assert.IsNotNull(text.AddLineBreak());
Assert.IsNotNull(text.TextRuns);
Assert.AreEqual(2, text.TextRuns.Count);
text.AddNewTextRun();
Assert.AreEqual(3, text.TextRuns.Count);
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = (/*setter*/TextAlign.CENTER);
Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
text.TextAlign = (/*setter*/TextAlign.RIGHT);
Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextFontAlign = (/*setter*/TextFontAlign.BASELINE);
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = (/*setter*/TextFontAlign.BOTTOM);
Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
Assert.IsNull(text.BulletFont);
text.BulletFont = (/*setter*/"Arial");
Assert.AreEqual("Arial", text.BulletFont);
Assert.IsNull(text.BulletCharacter);
text.BulletCharacter = (/*setter*/".");
Assert.AreEqual(".", text.BulletCharacter);
//Assert.IsNull(text.BulletFontColor);
Assert.AreEqual(Color.Empty, text.BulletFontColor);
text.BulletFontColor = (/*setter*/color);
Assert.AreEqual(color, text.BulletFontColor);
Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/-9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/2.0);
Assert.AreEqual(2.0, text.Indent, 0.01);
text.Indent = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/3.0);
Assert.AreEqual(3.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/4.5);
Assert.AreEqual(4.5, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/-1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);
Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
text.AddTabStop(3.14);
Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);
Assert.AreEqual(100.0, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/3.15);
Assert.AreEqual(3.15, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/-2.13);
Assert.AreEqual(-2.13, text.LineSpacing, 0.01);
Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/3.17);
Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/-4.7);
Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);
Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/6.17);
Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/-8.17);
Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);
Assert.AreEqual(0, text.Level);
text.Level = (/*setter*/1);
Assert.AreEqual(1, text.Level);
text.Level = (/*setter*/4);
Assert.AreEqual(4, text.Level);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (false);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.IsBullet = (true);
Assert.IsTrue(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
Assert.AreEqual(0, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
//Assert.AreEqual(0, text.BulletAutoNumberStart);
//This value should be 1, see CT_TextAutonumberBullet.startAt, default value is 1;
Assert.AreEqual(1, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
text.IsBullet = (false);
Assert.IsFalse(text.IsBullet);
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
Assert.IsTrue(text.IsBullet);
Assert.IsTrue(text.IsBulletAutoNumber);
Assert.AreEqual(10, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);
Assert.IsNotNull(text.ToString());
new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
}
finally
{
wb.Close();
}
}
public void XSSFTextParagraphTest()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/ "Arial");
rt.ApplyFont(font);
shape.SetText(rt);
List <XSSFTextParagraph> paras = shape.TextParagraphs;
Assert.AreEqual(1, paras.Count);
XSSFTextParagraph text = paras[(0)];
Assert.AreEqual("Test String", text.Text);
Assert.IsFalse(text.IsBullet());
Assert.IsNotNull(text.GetXmlObject());
Assert.AreEqual(shape.GetCTShape(), text.ParentShape);
Assert.IsNotNull(text.GetEnumerator());
Assert.IsNotNull(text.AddLineBreak());
Assert.IsNotNull(text.TextRuns);
Assert.AreEqual(2, text.TextRuns.Count);
text.AddNewTextRun();
Assert.AreEqual(3, text.TextRuns.Count);
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextAlign = (/*setter*/ TextAlign.CENTER);
Assert.AreEqual(TextAlign.CENTER, text.TextAlign);
text.TextAlign = (/*setter*/ TextAlign.RIGHT);
Assert.AreEqual(TextAlign.RIGHT, text.TextAlign);
text.TextAlign = TextAlign.None;
Assert.AreEqual(TextAlign.LEFT, text.TextAlign);
text.TextFontAlign = (/*setter*/ TextFontAlign.BASELINE);
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = (/*setter*/ TextFontAlign.BOTTOM);
Assert.AreEqual(TextFontAlign.BOTTOM, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
text.TextFontAlign = TextFontAlign.None;
Assert.AreEqual(TextFontAlign.BASELINE, text.TextFontAlign);
Assert.IsNull(text.BulletFont);
text.BulletFont = (/*setter*/ "Arial");
Assert.AreEqual("Arial", text.BulletFont);
Assert.IsNull(text.BulletCharacter);
text.BulletCharacter = (/*setter*/ ".");
Assert.AreEqual(".", text.BulletCharacter);
Assert.IsNull(text.BulletFontColor);
text.BulletFontColor = (/*setter*/ color);
Assert.AreEqual(color, text.BulletFontColor);
Assert.AreEqual(100.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ 1.0);
Assert.AreEqual(1.0, text.BulletFontSize, 0.01);
text.BulletFontSize = (/*setter*/ -9.0);
Assert.AreEqual(-9.0, text.BulletFontSize, 0.01);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/ 2.0);
Assert.AreEqual(2.0, text.Indent, 0.01);
text.Indent = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
text.Indent = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.Indent, 0.01);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ 3.0);
Assert.AreEqual(3.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
text.LeftMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.LeftMargin, 0.01);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ 4.5);
Assert.AreEqual(4.5, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
text.RightMargin = (/*setter*/ -1.0);
Assert.AreEqual(0.0, text.RightMargin, 0.01);
Assert.AreEqual(0.0, text.DefaultTabSize, 0.01);
Assert.AreEqual(0.0, text.GetTabStop(0), 0.01);
text.AddTabStop(3.14);
Assert.AreEqual(3.14, text.GetTabStop(0), 0.01);
Assert.AreEqual(100.0, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/ 3.15);
Assert.AreEqual(3.15, text.LineSpacing, 0.01);
text.LineSpacing = (/*setter*/ -2.13);
Assert.AreEqual(-2.13, text.LineSpacing, 0.01);
Assert.AreEqual(0.0, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/ 3.17);
Assert.AreEqual(3.17, text.SpaceBefore, 0.01);
text.SpaceBefore = (/*setter*/ -4.7);
Assert.AreEqual(-4.7, text.SpaceBefore, 0.01);
Assert.AreEqual(0.0, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/ 6.17);
Assert.AreEqual(6.17, text.SpaceAfter, 0.01);
text.SpaceAfter = (/*setter*/ -8.17);
Assert.AreEqual(-8.17, text.SpaceAfter, 0.01);
Assert.AreEqual(0, text.Level);
text.Level = (/*setter*/ 1);
Assert.AreEqual(1, text.Level);
text.Level = (/*setter*/ 4);
Assert.AreEqual(4, text.Level);
Assert.IsTrue(text.IsBullet());
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(false);
text.SetBullet(false);
Assert.IsFalse(text.IsBullet());
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(true);
Assert.IsTrue(text.IsBullet());
Assert.IsFalse(text.IsBulletAutoNumber);
Assert.AreEqual(0, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.ARABIC_PLAIN, text.BulletAutoNumberScheme);
text.SetBullet(false);
Assert.IsFalse(text.IsBullet());
text.SetBullet(ListAutoNumber.CIRCLE_NUM_DB_PLAIN);
Assert.IsTrue(text.IsBullet());
Assert.IsTrue(text.IsBulletAutoNumber);
Assert.AreEqual(0, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_DB_PLAIN, text.BulletAutoNumberScheme);
text.SetBullet(false);
Assert.IsFalse(text.IsBullet());
Assert.IsFalse(text.IsBulletAutoNumber);
text.SetBullet(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, 10);
Assert.IsTrue(text.IsBullet());
Assert.IsTrue(text.IsBulletAutoNumber);
Assert.AreEqual(10, text.BulletAutoNumberStart);
Assert.AreEqual(ListAutoNumber.CIRCLE_NUM_WD_BLACK_PLAIN, text.BulletAutoNumberScheme);
Assert.IsNotNull(text.ToString());
new XSSFTextParagraph(text.GetXmlObject(), shape.GetCTShape());
}
finally
{
wb.Close();
}
}
public static void ExportHandleNumExcel(Dictionary<string, Model.DTO.HKHandleNumDetail> hkHandleNumDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号6 香港新马寄出个数量化单（5.5之后）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("香港新马");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.GetRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚网申3");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(hkHandleNumDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.HKHandleNumDetail item in hkHandleNumDetails.Values)
{
row = sheet.CreateRow(4 + i);
row.HeightInPoints = 25;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//申请学历
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.Education);
//学校申请
cell = row.CreateCell(4);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Application);
//新加坡公立硕士
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.HK.SingaporeMaster);
//香港博士
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.HK.Doctor);
//签证申请
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Visa);
//资深文案
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//寄出日期
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
//备注
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(4 + hkHandleNumDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(7);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
row = sheet.CreateRow(5 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1、新马申请学历：幼稚园、小学、O Level、A Level、中学、预科、专科、本科、硕士、博士、语言");
cell.CellStyle = style;
row = sheet.CreateRow(6 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2、香港申请学历：本科、硕士和博士");
cell.CellStyle = style;
row = sheet.CreateRow(7 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3、量化手写无效；需要机打。");
cell.CellStyle = style;
row = sheet.CreateRow(8 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、寄出后2个工作日内填报量化。");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("5、新加坡公立硕士以及香港博士申请一个合同号算2个寄出个数。");
cell.CellStyle = style;
row = sheet.CreateRow(10 + hkHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("6、未精益，只填报制作文案即可。");
cell.CellStyle = style;
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public IHttpActionResult ExportAllToExcel(FilterSales filter)
{
var response = new DataResponse <string>();
try
{
if (filter.GroupBy <= 0)
{
#region Sales Default Export
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("SalesReport");
NPOI.SS.UserModel.ICreationHelper cH = workbook.GetCreationHelper();
string[] columnname = filter.DynamicFilters.Where(a => a.IsVisible == true).Select(a => a.ColumnName).ToArray();
string[] headers, columns = null;
headers = columns = columnname;
columns = columns.Select(x => x.Replace("SalesTeam", "RepGroup")).ToArray();
columns = columns.Select(x => x.Replace("CollectedDate", "CollectionDate")).ToArray();
//byte[] rgb = new byte[3] { 22, 183, 223 };
//XSSFCellStyle HeaderCellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//HeaderCellStyle.SetFillBackgroundColor(new XSSFColor(rgb));
var headerRow = sheet.CreateRow(0);
//headerRow.RowStyle.FillBackgroundColor= (short)ColorTranslator.ToWin32(Color.Aqua);
//create header
for (int i = 0; i < headers.Length; i++)
{
sheet.DefaultColumnWidth = 20;
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFColor colorToFill = new XSSFColor(Color.Aqua);
style.FillBackgroundColor = (short)ColorTranslator.ToWin32(Color.Aqua);
headerRow.RowStyle = style;
var cell = headerRow.CreateCell(i);
cell.SetCellValue(headers[i]);
}
string[] allowedRoles = { "RDSLS" };
string[] superRoles = { "RDSLSALL" };
bool hasSuperRight = HasRight(superRoles);
bool displayPatientName = HasRight(new string[] { "VWSLSPTNT" });
if (HasRight(allowedRoles) || hasSuperRight)
{
var repository = new RepositorySales();
var dataResponse = repository.GetAllList(filter, CurrentUser.BusinessId, CurrentUserId, hasSuperRight, CurrentUserRoles, CurrentUserDepartments, CurrentUserPrivileges, IsRep || IsSalesManager, IsSalesDirector, displayPatientName, 10, 0, false, true, mapperFilePath: this.MapperFilePath);
List <EntitySales> salesList = dataResponse.Model.List.ToList();
int recordCount = dataResponse.Model.Pager.TotalCount;
//fill content
var rowIndex = 0;
for (int i = 0; i < recordCount; i++)
{
rowIndex++;
var row = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var font = workbook.CreateFont();
font.FontHeightInPoints = 11;
font.FontName = "Calibri";
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var cell = row.CreateCell(j);
cell.CellStyle = workbook.CreateCellStyle();
cell.CellStyle.SetFont(font);
string value = null;
var objSalesItem = salesList[i];
var objStaticItem = objSalesItem.GetType().GetTypeInfo().GetProperty(columns[j]);
if (objStaticItem != null)
{
var property = salesList[i].GetType().GetRuntimeProperties().FirstOrDefault(p => string.Equals(p.Name, objStaticItem.Name, StringComparison.OrdinalIgnoreCase));
if (property != null)
{
value = Convert.ToString(property.GetValue(salesList[i], null));
}
}
else
{
var objDynamicItem = objSalesItem.ReportColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
bool isFinanceDataCount = salesList[i].FinanceDataRecordCount > 1;
if (isFinanceDataCount)
{
for (int k = 0; k < salesList[i].FinanceDataRecordCount; k++)
{
rowIndex++;
var financeRow = sheet.CreateRow(rowIndex);
for (int j = 0; j < columns.Length; j++)
{
var cell = financeRow.CreateCell(j);
string value = null;
var objSalesItem = salesList[i].FinanceDataList.ToList()[k];
var objStaticItem = objSalesItem.GetType().GetProperty(columns[j]);
if (objStaticItem != null)
{
value = Convert.ToString(objStaticItem.GetValue(objSalesItem, null));
}
else
{
var objDynamicItem = objSalesItem.FinanceColumnValues.FirstOrDefault(a => a.ColumnName == columns[j]);
if (objDynamicItem != null)
{
value = objDynamicItem.Value;
}
}
cell.SetCellValue(value);
}
}
}
}
string directory = Path.Combine("Assets", CurrentBusinessId.Value.ToString(), "Sales", "Sales-Archives", "Exports");
string fileUri = HttpContext.Current.Server.MapPath(Path.Combine("~/", directory));
if (!Directory.Exists(fileUri))
{
Directory.CreateDirectory(fileUri);
}
string fileName = string.Format("{0:yyyyMMddhhmmssfff}", DateTime.Now),
extension = "xlsx";
string filePath = Path.Combine(fileUri, string.Format("{0}.{1}", fileName, extension));
int count = 1;
isExist:
if (File.Exists(filePath))
{
fileName = string.Format("{0}{1}{2}", fileName, count, extension);
filePath = Path.Combine(fileUri, fileName);
count++;
goto isExist;
}
using (FileStream stream = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
workbook.Write(stream);
}
response.Model = Path.Combine(directory, string.Format("{0}.{1}", fileName, extension));
}
return(Ok <DataResponse>(response));
#endregion
}
#region Sales Grouped Report
return(ExportGroupedSalesToExcel(filter));
#endregion
}
catch (Exception ex)
{
ex.Log();
}
response.Message = "Internal Server Error";
return(Ok <DataResponse>(response));
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
private void formatStyleQuestion(string p, ref ICell cell, ref XSSFWorkbook wb)
{
List<FormatExcelTextAux> list = new List<FormatExcelTextAux>();
string question = p.Replace("<br>", System.Environment.NewLine).Replace("<br/>", System.Environment.NewLine); ;
getStyleListFromHTMLTags(ref question, ref list);
//Atribui o texto sem HTML para celula
cell.SetCellValue(question);
//Aplica a fonte com os estilos
foreach (var i in list)
{
IFont font = wb.CreateFont();
if (i.FormatType.Contains(FORMAT_BOLD_TYPE))
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
if (i.FormatType.Contains(FORMAT_ITALIC_TYPE))
font.IsItalic = true;
if (i.FormatType.Contains(FORMAT_UNDERLINE_TYPE))
font.Underline = FontUnderlineType.Single;
cell.RichStringCellValue.ApplyFont(i.OpenTagIndex, i.OpenTagIndex + i.Length, font);
}
}
/// <summary>
/// 获取字体样式
/// </summary>
/// <param name="hssfworkbook">Excel操作类</param>
/// <param name="fontfamily">字体名</param>
/// <param name="fontcolor">字体颜色</param>
/// <param name="fontsize">字体大小</param>
/// <param name="bold">是否加粗</param>
/// <param name="typeOffset">上下标</param>
/// <returns></returns>
public static IFont GetFontStyle(XSSFWorkbook hssfworkbook, string fontfamily, XSSFColor fontcolor, int fontsize, FontBoldWeight bold = FontBoldWeight.Normal, FontSuperScript typeOffset = FontSuperScript.None)
{
XSSFFont xfont = hssfworkbook.CreateFont() as XSSFFont;
if (string.IsNullOrEmpty(fontfamily))
{
xfont.FontName = fontfamily;
}
if (fontcolor != null)
{
xfont.SetColor(fontcolor);
}
xfont.IsItalic = false;
xfont.FontHeightInPoints = (short)fontsize;
xfont.Boldweight = (short)bold;
xfont.TypeOffset = typeOffset;
return xfont;
}
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单（2015年1月1日后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("量化单");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
IRow row = sheet.GetRow(3);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(11);
cell.SetCellValue(ukHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values)
{
row = sheet.CreateRow(5 + i);
row.HeightInPoints = 16.7F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//套磁
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Magnetic);
//文书
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.PS);
//录取
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//资深文案
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//文书文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//转案区间
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(5 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//设置合并单元格区域
CellRangeAddress cellRangeAddress = new CellRangeAddress(7 + ukHandleQuanDetails.Count, 7 + ukHandleQuanDetails.Count, 0, 12);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(7 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
//设置合并单元格区域
cellRangeAddress = new CellRangeAddress(8 + ukHandleQuanDetails.Count, 8 + ukHandleQuanDetails.Count, 0, 12);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(8 + ukHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
cell.CellStyle.Alignment = HorizontalAlignment.Left;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
//设置合并单元格区域
cellRangeAddress = new CellRangeAddress(9 + ukHandleQuanDetails.Count, 9 + ukHandleQuanDetails.Count, 0, 1);
sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(9 + ukHandleQuanDetails.Count);
row.HeightInPoints = 37.5F;
cell = row.CreateCell(0);
cell.SetCellValue("3、申请学历填写标准的申请类别");
cell.CellStyle = style;
for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = style;
}
////设置单元格格式
//style = workbook.CreateCellStyle();
//style.Alignment = HorizontalAlignment.Center;
//style.VerticalAlignment = VerticalAlignment.Center;
//style.BorderTop = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderBottom = BorderStyle.Thin;
//font = workbook.CreateFont();
//font.FontName = "宋体";
//font.FontHeightInPoints = 11;
//style.SetFont(font);
//cell = row.CreateCell(2);
//cell.SetCellValue("高中及以下（无文书）");
//style.WrapText = true;
//cell.CellStyle = style;
//cell = row.CreateCell(3);
//cell.SetCellValue("高端本科");
//cell.CellStyle = style;
//cell = row.CreateCell(4);
//cell.SetCellValue("本科预科/硕士预科（无文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(5);
//cell.SetCellValue("非高端本科院校");
//cell.CellStyle = style;
//cell = row.CreateCell(6);
//cell.SetCellValue("高端硕士院校");
//cell.CellStyle = style;
//cell = row.CreateCell(7);
//cell.SetCellValue("非高端研究生");
//cell.CellStyle = style;
//cell = row.CreateCell(8);
//cell.SetCellValue("博士");
//cell.CellStyle = style;
//cell = row.CreateCell(9);
//cell.SetCellValue("只申请语言学校");
//cell.CellStyle = style;
//cell = row.CreateCell(10);
//cell.SetCellValue("高中及以下（有文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(11);
//cell.SetCellValue("本科预科/硕士预科（有文书）");
//cell.CellStyle = style;
//cell = row.CreateCell(12);
//cell.CellStyle = style;
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
public static byte[] GenerateTemplate(List<Business.Entities.company> listCompany,List<Business.Entities.contractor> listContractor,List<Business.Entities.project> listProject)
{
//culture
Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); //supaya file tidak corrupt
int parseRecordNumber = 100; // number of rows that has style or validation
int startRowIndex = 3;
XSSFCellStyle styleCurrency;
XSSFCellStyle styleDate;
XSSFCellStyle styleNumeric;
XSSFCellStyle styleDecimal;
//kamus
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet; XSSFRow row; XSSFCell cell;
XSSFCellStyle style; XSSFFont font;
CellRangeAddressList addressList; XSSFDataValidationHelper dvHelper; XSSFDataValidationConstraint dvConstraint; XSSFDataValidation validation;
List<string> listCompanyString = new List<string>();
foreach(var data in listCompany)
{
listCompanyString.Add(data.name);
}
List<string> listContractorString = new List<string>();
foreach(var data in listContractor)
{
listContractorString.Add(data.name);
}
List<string> listProjectString = new List<string>();
foreach(var data in listProject)
{
listProjectString.Add(data.name);
}
styleCurrency = (XSSFCellStyle)workbook.CreateCellStyle();
styleCurrency.DataFormat = workbook.CreateDataFormat().GetFormat("$#,##0.00_);($#,##0.00)");
styleNumeric = (XSSFCellStyle)workbook.CreateCellStyle();
styleNumeric.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
styleDate = (XSSFCellStyle)workbook.CreateCellStyle();
styleDate.DataFormat = workbook.CreateDataFormat().GetFormat("mm/dd/yyyy");
styleDecimal = (XSSFCellStyle)workbook.CreateCellStyle();
styleDecimal.DataFormat = workbook.CreateDataFormat().GetFormat("0.00");
List<string> columnList = new List<string>();
columnList.Add("Name");
int ContractorStringLocation = 1;
columnList.Add("Contractor");
columnList.Add("Photo");
columnList.Add("Description");
columnList.Add("Start Date");
columnList.Add("Finish Date");
columnList.Add("Highlight");
columnList.Add("Project Stage");
columnList.Add("Status");
columnList.Add("Budget");
columnList.Add("Currency");
columnList.Add("Num");
int PmcStringLocation = 12;
columnList.Add("Pmc");
columnList.Add("Summary");
int CompanyStringLocation = 14;
columnList.Add("Company");
columnList.Add("Status Non Technical");
columnList.Add("Is Completed");
columnList.Add("Completed Date");
int ProjectStringLocation = 18;
columnList.Add("Project");
columnList.Add("Submit For Approval Time");
columnList.Add("Approval Status");
columnList.Add("Approval Time");
columnList.Add("Deleted");
columnList.Add("Approval Message");
columnList.Add("Status Technical");
columnList.Add("Scurve Data");
sheet = (XSSFSheet)workbook.CreateSheet("Data");
int col = 0;
int rowNumber = 0;
//create row (header)
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
dvHelper = new XSSFDataValidationHelper(sheet);
//header data
style = (XSSFCellStyle)workbook.CreateCellStyle();
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue("M Project");
font = (XSSFFont)workbook.CreateFont();
font.FontHeight = 24;
style.SetFont(font);
cell.CellStyle = style;
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
style = (XSSFCellStyle)workbook.CreateCellStyle();
font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
style.SetFont(font);
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//header data
foreach (string data in columnList)
{
cell = (XSSFCell)row.CreateCell(col);
cell.SetCellValue(data);
cell.CellStyle = style;
//cell.CellStyle.IsLocked = true;
//column width
sheet.SetColumnWidth(col, (30 * 256));
++col;
}
//sheet.CreateFreezePane(0, 4);
//dropdownlist Company
if(listCompanyString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterCompany");
int i=0;
foreach(string a in listCompanyString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,CompanyStringLocation,CompanyStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterCompany!$A$1:$A$" + listCompanyString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(1, true);
sheet.AddValidationData(validation);
}
//dropdownlist Contractor
if(listContractorString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterContractor");
int i=0;
foreach(string a in listContractorString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ContractorStringLocation,ContractorStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterContractor!$A$1:$A$" + listContractorString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(2, true);
sheet.AddValidationData(validation);
}
//dropdownlist Project
if(listProjectString.Count > 0)
{
XSSFSheet hidden = (XSSFSheet)workbook.CreateSheet("MasterProject");
int i=0;
foreach(string a in listProjectString)
{
row = (XSSFRow)hidden.CreateRow(i);
cell = (XSSFCell)row.CreateCell(0);
cell.SetCellValue(a);
i++;
}
validation = null;
dvConstraint = null;
dvHelper = null;
dvHelper=new XSSFDataValidationHelper(sheet);
addressList = new CellRangeAddressList(startRowIndex,parseRecordNumber,ProjectStringLocation,ProjectStringLocation);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("MasterProject!$A$1:$A$" + listProjectString.Count);
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.SuppressDropDownArrow = true;
validation.ShowErrorBox = true;
workbook.SetSheetHidden(3, true);
sheet.AddValidationData(validation);
}
/*Cell formatting*/
for (int i = startRowIndex; i <= parseRecordNumber; i++)
{
rowNumber++;
row = (XSSFRow)sheet.CreateRow((short)rowNumber);
//start_date
col = 4;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 4, 4);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//finish_date
col = 5;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 5, 5);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//budget
col = 9;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDecimal;
if(i==startRowIndex)
{
}
//num
col = 11;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleNumeric;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 11, 11);
dvHelper = new XSSFDataValidationHelper(sheet);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateNumericConstraint(ValidationType.INTEGER, OperatorType.BETWEEN, "0", "1000000000000000000");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a number, maximum 1.000.000.000.000.000.000");
sheet.AddValidationData(validation);
}
//completed_date
col = 17;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 17, 17);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//submit_for_approval_time
col = 19;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 19, 19);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
//approval_time
col = 21;
cell = (XSSFCell)row.CreateCell((short)col);
cell.CellStyle = styleDate;
if(i==startRowIndex)
{
addressList = new CellRangeAddressList(startRowIndex, parseRecordNumber, 21, 21);
dvConstraint = (XSSFDataValidationConstraint)dvHelper.CreateDateConstraint(OperatorType.GREATER_THAN, "01/01/1900", "", "mm/dd/yyyy");
validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
validation.ShowErrorBox = true;
validation.CreateErrorBox("Input Error", "Value must be a date, example 12/30/2000");
sheet.AddValidationData(validation);
}
}
//write to byte[]
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return ms.ToArray();
}
public void TestSetString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet();
XSSFComment comment = (XSSFComment)sh.CreateDrawingPatriarch().CreateCellComment(new XSSFClientAnchor());
//passing HSSFRichTextString is incorrect
try
{
comment.String = (new HSSFRichTextString(TEST_RICHTEXTSTRING));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("Only XSSFRichTextString argument is supported", e.Message);
}
//simple string argument
comment.SetString(TEST_RICHTEXTSTRING);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//if the text is already Set, it should be overridden, not Added twice!
comment.SetString(TEST_RICHTEXTSTRING);
CT_Comment ctComment = comment.GetCTComment();
// Assert.Fail("TODO test case incomplete!?");
//XmlObject[] obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreEqual(TEST_RICHTEXTSTRING, comment.String.String);
//sequential call of comment.String should return the same XSSFRichTextString object
Assert.AreSame(comment.String, comment.String);
XSSFRichTextString richText = new XSSFRichTextString(TEST_RICHTEXTSTRING);
XSSFFont font1 = (XSSFFont)wb.CreateFont();
font1.FontName = ("Tahoma");
//font1.FontHeight = (short)8.5;
font1.SetFontHeight(8.5);
font1.IsItalic = true;
font1.Color = IndexedColors.BlueGrey.Index;
richText.ApplyFont(0, 5, font1);
//check the low-level stuff
comment.String = richText;
//obj = ctComment.selectPath(
// "declare namespace w='http://schemas.Openxmlformats.org/spreadsheetml/2006/main' .//w:text");
//Assert.AreEqual(1, obj.Length);
Assert.AreSame(comment.String, richText);
//check that the rich text is Set in the comment
CT_RPrElt rPr = richText.GetCTRst().GetRArray(0).rPr;
Assert.AreEqual(true, rPr.GetIArray(0).val);
Assert.AreEqual(8.5, rPr.GetSzArray(0).val);
Assert.AreEqual(IndexedColors.BlueGrey.Index, (short)rPr.GetColorArray(0).indexed);
Assert.AreEqual("Tahoma", rPr.GetRFontArray(0).val);
}
public static void ExportHandleQuanExcel(Dictionary<string, Model.DTO.HKHandleQuanDetail> hkHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\编号12 香港新马激励量化单（5.5日之后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("香港新马");
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
IRow row = sheet.GetRow(1);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚网申3");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue(hkHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.HKHandleQuanDetail item in hkHandleQuanDetails.Values)
{
row = sheet.CreateRow(4 + i);
row.HeightInPoints = 25;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//翻译
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Translation);
//签证寄出
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Visa);
//寄出日期
cell = row.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue(item.SendDate.ToString("yyyy/MM/dd"));
//资深文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//新马文案
cell = row.CreateCell(11);
cell.CellStyle = style;
//录取
cell = row.CreateCell(12);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//获签
cell = row.CreateCell(13);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Sign);
//文书文案
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(15);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//备注
cell = row.CreateCell(16);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.CreateRow(4 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(9);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
//style.BorderTop = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
////设置合并单元格区域
//CellRangeAddress cellRangeAddress = new CellRangeAddress(5 + hkHandleQuanDetails.Count, 5 + hkHandleQuanDetails.Count, 0, 15);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(5 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
////设置合并单元格区域
//cellRangeAddress = new CellRangeAddress(6 + hkHandleQuanDetails.Count, 6 + hkHandleQuanDetails.Count, 0, 15);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(6 + hkHandleQuanDetails.Count);
row.HeightInPoints = 25;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
cell.CellStyle.Alignment = HorizontalAlignment.Left;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
//设置合并单元格区域
//cellRangeAddress = new CellRangeAddress(7 + hkHandleQuanDetails.Count, 7 + hkHandleQuanDetails.Count, 0, 1);
//sheet.AddMergedRegion(cellRangeAddress);
row = sheet.CreateRow(7 + hkHandleQuanDetails.Count);
//row.HeightInPoints = 37.5F;
cell = row.CreateCell(0);
cell.SetCellValue("3、新加坡量化类别：");
cell.CellStyle = style;
//for (int j = cellRangeAddress.FirstColumn; j <= cellRangeAddress.LastColumn; j++)
//{
// ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
// singleCell.CellStyle = style;
//}
row = sheet.CreateRow(8 + hkHandleQuanDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、香港量化类别：");
cell.CellStyle = style;
row = sheet.CreateRow(9 + hkHandleQuanDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("5、马来西亚量化类别：");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
style.SetFont(font);
row = sheet.GetRow(7 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("公立硕士");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("幼儿园，小学，公立大学，大专、本科、语言/预科/私立大专，本科、研究生");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("NEXUS/BC幼儿园");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("visa only");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.CellStyle = style;
row = sheet.GetRow(8 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("博士/研究生");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("PS（1封）+推荐信（2封）+CV（1封）");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("个人陈述PS（1封）");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("推荐信（2封）");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue("CV（1封）");
cell.CellStyle = style;
row = sheet.GetRow(9 + hkHandleQuanDetails.Count);
cell = row.CreateCell(2);
cell.SetCellValue("公立本科");
style.WrapText = true;
cell.CellStyle = style;
cell = row.CreateCell(3);
cell.SetCellValue("公立硕士");
cell.CellStyle = style;
cell = row.CreateCell(4);
cell.SetCellValue("语言/预科/私立本科、研究生");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue("家长旅游签");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.CellStyle = style;
sheet.AutoSizeColumn(4);
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
internal void createExcelReport(string filePath, string carRegNo, string organizationName, List<DriveHistoryView> recordList)
{
try
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Drive Record");
int rowIndex = 0;
IRow row = sheet.CreateRow(rowIndex);
ICell cell = row.CreateCell(0);
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
cell.SetCellValue("Doctor Car Drive Operation Management System Report - Drive Records");
cell.CellStyle.SetFont(font);
CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 4);
//cell = row.CreateCell(5);
//cell.SetCellValue(record.StartDate.ToString());
//CellManager.columnMerge(sheet, rowIndex, 5, 6, false);
rowIndex++;
sheet.CreateRow(rowIndex);
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, "Car Reg No", false);
CellStyle.GrayBackground(workbook, cell, false);
cell = CellManager.CreateCell(workbook, row, 2, carRegNo, false);
CellManager.topBottomBorder(workbook, cell, row, 1);
CellManager.columnMerge(sheet, rowIndex, 2, 3, true);
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, "Organization", false);
CellStyle.GrayBackground(workbook, cell, false);
cell = CellManager.CreateCell(workbook, row, 2, organizationName.Trim(), false);
CellManager.topBottomBorder(workbook, cell, row, 1);
CellManager.columnMerge(sheet, rowIndex, 2, 3, true);
rowIndex++;
sheet.CreateRow(rowIndex);
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(1);
cell.SetCellValue("Drive Details");
CellManager.topBottomBorder(workbook, cell, row, 11);
CellManager.columnMerge(sheet, rowIndex, 1, 12, true);
CellStyle.GrayBackground(workbook, cell, true);
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, "#", true);
cell = CellManager.CreateCell(workbook, row, 2, "Start Date time", true);
cell = CellManager.CreateCell(workbook, row, 3, "End Date Time", true);
cell = CellManager.CreateCell(workbook, row, 4, "Operation Time (h)", true);
cell = CellManager.CreateCell(workbook, row, 5, "Start Odometer (km)", true);
cell = CellManager.CreateCell(workbook, row, 6, "End Odometer (km)", true);
cell = CellManager.CreateCell(workbook, row, 7, "Distance (km)", true);
cell = CellManager.CreateCell(workbook, row, 8, "Velocity (km/h)", true);
cell = CellManager.CreateCell(workbook, row, 9, "Drive From", true);
cell = CellManager.CreateCell(workbook, row, 10, "Drive To", true);
cell = CellManager.CreateCell(workbook, row, 11, "Driver", true);
cell = CellManager.CreateCell(workbook, row, 12, "No. Patients", true);
int recordNo = 1;
foreach (DriveHistoryView record in recordList)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, recordNo, true);
cell = CellManager.CreateCell(workbook, row, 2, record.StartDate.ToString(), false);
cell = CellManager.CreateCell(workbook, row, 3, record.EndDate.ToString(), false);
TimeSpan duration = ((DateTime)record.EndDate - (DateTime)record.StartDate);
cell = CellManager.CreateCell(workbook, row, 4, duration.Hours, false);
cell = CellManager.CreateCell(workbook, row, 5, record.StartOdometer, false);
cell = CellManager.CreateCell(workbook, row, 6, record.EndOdometer, false);
cell = CellManager.CreateCell(workbook, row, 7, (record.EndOdometer - record.StartOdometer), false);
if (duration.Hours > 0)
{
cell = CellManager.CreateCell(workbook, row, 8, Math.Round((double)(record.EndOdometer - record.StartOdometer) / duration.Hours, 1), false);
}
else
{
cell = CellManager.CreateCell(workbook, row, 8, "", false);
}
cell = CellManager.CreateCell(workbook, row, 9, record.From.ToString(), false);
cell = CellManager.CreateCell(workbook, row, 10, record.To.ToString(), false);
cell = CellManager.CreateCell(workbook, row, 11, record.DriverName.ToString(), false);
cell = CellManager.CreateCell(workbook, row, 12, record.NumPatients, false);
recordNo++;
for (int i = 0; i < 15; i++)
{
sheet.AutoSizeColumn(i);
}
}
rowIndex++;
row = sheet.CreateRow(rowIndex);
if (recordNo - 1 > 0)
{
cell = CellManager.CreateTotalCell(workbook, row, 12, "", false);
cell.SetCellFormula("SUM(M" + (rowIndex + 1 - recordNo + 1) + ":M" + rowIndex + ")");
}
sheet = workbook.CreateSheet("Fuel Record");
rowIndex = 0;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("Doctor Car Drive Operation Management System Report - Fuel Records");
CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 4);
rowIndex++;
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, "Fuel Expenses", true);
CellManager.topBottomBorder(workbook, cell, row, 3);
CellStyle.GrayBackground(workbook, cell, true);
CellManager.columnMerge(sheet, rowIndex, 1, 4, true);
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, "Date Time", true);
cell = CellManager.CreateCell(workbook, row, 2, "Odometer (km)", true);
cell = CellManager.CreateCell(workbook, row, 3, "Capacity (L)", true);
cell = CellManager.CreateCell(workbook, row, 4, "Amount(SDG)", true);
int numFuelRecords = 0;
foreach (DriveHistoryView record in recordList)
{
if (record.FuelTopup != null)
{
foreach (tbl_fuel fuel in record.FuelTopup)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, fuel.ADD_TIME.ToString(), false);
cell = CellManager.CreateCell(workbook, row, 2, fuel.ODOMETER, false);
cell = CellManager.CreateCell(workbook, row, 3, fuel.VOLUME, false);
cell = CellManager.CreateCell(workbook, row, 4, fuel.AMOUNT, false);
numFuelRecords++;
}
}
}
rowIndex++;
row = sheet.CreateRow(rowIndex);
if (numFuelRecords > 0)
{
cell = CellManager.CreateLabelCell(workbook, row, 2, "Total", false);
cell = CellManager.CreateTotalCell(workbook, row, 3, "", false);
cell.SetCellFormula("SUM(D" + (rowIndex + 1 - numFuelRecords) + ":D" + rowIndex + ")");
cell = CellManager.CreateTotalCell(workbook, row, 4, "", false);
cell.SetCellFormula("SUM(E" + (rowIndex + 1 - numFuelRecords) + ":E" + rowIndex + ")");
}
for (int i = 0; i < 15; i++)
{
sheet.AutoSizeColumn(i);
}
sheet = workbook.CreateSheet("Other Expenses Record");
rowIndex = 0;
row = sheet.CreateRow(rowIndex);
cell = row.CreateCell(0);
cell.SetCellValue("Doctor Car Drive Operation Management System Report - Other Expenses");
CellManager.columnMerge(sheet, cell.CellStyle, rowIndex, 0, 5);
rowIndex++;
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, "Other Expenses", true);
CellManager.topBottomBorder(workbook, cell, row, 3);
CellStyle.GrayBackground(workbook, cell, true);
CellManager.columnMerge(sheet, rowIndex, 1, 4, true);
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, "Date Time", true);
cell = CellManager.CreateCell(workbook, row, 2, "Item", true);
cell = CellManager.CreateCell(workbook, row, 3, "Amount(SDG)", true);
cell = CellManager.CreateCell(workbook, row, 4, "Description", true);
DrCarDriveService service = new DrCarDriveService();
int numExpenses = 0;
foreach (DriveHistoryView record in recordList)
{
List<ExpenseView> expenses = service.getOtherExpensesByDriveId(record.DriveID);
if (expenses != null)
{
foreach (ExpenseView expense in expenses)
{
rowIndex++;
row = sheet.CreateRow(rowIndex);
cell = CellManager.CreateCell(workbook, row, 1, expense.AddDate.ToString(), false);
cell = CellManager.CreateCell(workbook, row, 2, expense.Item, false);
cell = CellManager.CreateCell(workbook, row, 3, expense.Amount, false);
cell = CellManager.CreateCell(workbook, row, 4, expense.Remark, false);
numExpenses++;
}
}
}
rowIndex++;
row = sheet.CreateRow(rowIndex);
if (numExpenses > 0)
{
cell = CellManager.CreateLabelCell(workbook, row, 2, "Total", false);
cell = CellManager.CreateTotalCell(workbook, row, 3, "", false);
cell.SetCellFormula("SUM(D" + (rowIndex + 1 - numExpenses) + ":D" + rowIndex + ")");
}
for (int i = 0; i < 15; i++)
{
sheet.AutoSizeColumn(i);
}
FileStream stream = File.Create(filePath);
workbook.Write(stream);
stream.Close();
}
catch (Exception ex)
{
}
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List <POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
//XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
c1.LineWidth = 2.5;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
Assert.IsNotNull(c2);
XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFConnector);
Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);
// Save and re-load it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
// Check
dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
CT_Drawing ctDrawing = dr1.GetCTDrawing();
// Connector, shapes and text boxes are all two cell anchors
Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());
shapes = dr1.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFConnector);
Assert.IsTrue(shapes[1] is XSSFShapeGroup);
Assert.IsTrue(shapes[2] is XSSFSimpleShape);
Assert.IsTrue(shapes[3] is XSSFSimpleShape); //
// Ensure it got the right namespaces
//String xml = ctDrawing.ToString();
//Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
//Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
//multiple calls of CreateDrawingPatriarch should return the same instance of XSSFDrawing
XSSFDrawing dr1 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFDrawing dr2 = (XSSFDrawing)sheet.CreateDrawingPatriarch();
Assert.AreSame(dr1, dr2);
List<POIXMLDocumentPart> rels = sheet.GetRelations();
Assert.AreEqual(1, rels.Count);
Assert.IsTrue(rels[0] is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rels[0];
String drawingId = drawing.GetPackageRelationship().Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
//XSSFClientAnchor anchor = new XSSFClientAnchor();
XSSFConnector c1 = drawing.CreateConnector(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 2, 2));
c1.LineWidth = 2.5;
c1.LineStyle = SS.UserModel.LineStyle.DashDotSys;
XSSFShapeGroup c2 = drawing.CreateGroup(new XSSFClientAnchor(0, 0, 0, 0, 0, 0, 5, 5));
XSSFSimpleShape c3 = drawing.CreateSimpleShape(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4));
c3.SetText(new XSSFRichTextString("Test String"));
c3.SetFillColor(128, 128, 128);
XSSFTextBox c4 = (XSSFTextBox)drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 4, 4, 5, 6));
XSSFRichTextString rt = new XSSFRichTextString("Test String");
rt.ApplyFont(0, 5, wb.CreateFont());
rt.ApplyFont(5, 6, wb.CreateFont());
c4.SetText(rt);
c4.IsNoFill = (true);
Assert.AreEqual(4, drawing.GetCTDrawing().SizeOfTwoCellAnchorArray());
List<XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[(0)] is XSSFConnector);
Assert.IsTrue(shapes[(1)] is XSSFShapeGroup);
Assert.IsTrue(shapes[(2)] is XSSFSimpleShape);
Assert.IsTrue(shapes[(3)] is XSSFSimpleShape);
// Save and re-load it
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
sheet = wb.GetSheetAt(0) as XSSFSheet;
// Check
dr1 = sheet.CreateDrawingPatriarch() as XSSFDrawing;
CT_Drawing ctDrawing = dr1.GetCTDrawing();
// Connector, shapes and text boxes are all two cell anchors
Assert.AreEqual(0, ctDrawing.SizeOfAbsoluteAnchorArray());
Assert.AreEqual(0, ctDrawing.SizeOfOneCellAnchorArray());
Assert.AreEqual(4, ctDrawing.SizeOfTwoCellAnchorArray());
shapes = dr1.GetShapes();
Assert.AreEqual(4, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFConnector);
Assert.IsTrue(shapes[1] is XSSFShapeGroup);
Assert.IsTrue(shapes[2] is XSSFSimpleShape);
Assert.IsTrue(shapes[3] is XSSFSimpleShape); //
// Ensure it got the right namespaces
//String xml = ctDrawing.ToString();
//Assert.IsTrue(xml.Contains("xmlns:xdr=\"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing\""));
//Assert.IsTrue(xml.Contains("xmlns:a=\"http://schemas.openxmlformats.org/drawingml/2006/main\""));
}
public void TestCloneStyleDiffWB()
{
XSSFWorkbook wbOrig = new XSSFWorkbook();
Assert.AreEqual(1, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);
XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
fmt.GetFormat("MadeUpOne");
fmt.GetFormat("MadeUpTwo");
XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (fmt.GetFormat("Test##"));
Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
Assert.IsTrue(fnt == orig.GetFont());
Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);
Assert.AreEqual(2, wbOrig.NumberOfFonts);
Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);
// Now a style on another workbook
XSSFWorkbook wbClone = new XSSFWorkbook();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(1, wbClone.NumCellStyles);
XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();
Assert.AreEqual(1, wbClone.NumberOfFonts);
Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
Assert.IsFalse("TestingFont" == clone.GetFont().FontName);
clone.CloneStyleFrom(orig);
Assert.AreEqual(2, wbClone.NumberOfFonts);
Assert.AreEqual(2, wbClone.NumCellStyles);
Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual("TestingFont", clone.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
// Save it and re-check
XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
Assert.AreEqual(2, wbReload.NumberOfFonts);
Assert.AreEqual(2, wbReload.NumCellStyles);
Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);
XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
Assert.AreEqual("TestingFont", reload.GetFont().FontName);
Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
}
public static void ExportUKExcel(Dictionary<string, Model.DTO.UKHandleNumDetail> ukHandleNumDetails, Dictionary<string, Model.DTO.UKHandleQuanDetail> ukHandleQuanDetails, DateTime dt, ref string fileName)
{
FileStream stream = new FileStream(System.Windows.Forms.Application.StartupPath + @"\~temp\template\英国学校申请量化激励日报单（2015年1月1日后转案）.xlsx ", FileMode.Open, FileAccess.Read, FileShare.None);
XSSFWorkbook workbook = new XSSFWorkbook(stream);
ISheet sheet = workbook.GetSheet("个数单");
//下面开始导出个数
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
IRow row = sheet.GetRow(3);
ICell cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(5);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(12);
cell.SetCellValue(ukHandleNumDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
ICellStyle trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
int i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleNumDetail item in ukHandleNumDetails.Values)
{
row = sheet.CreateRow(6 + i);
row.HeightInPoints = 16.5F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//申请学历
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.Education);
//前四
cell = row.CreateCell(4);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.FirstFour);
//博士
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.Doctor);
//国内合作
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.UK.Cooperation);
//申请个数
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.ApplyNum);
//网申个数
cell = row.CreateCell(8);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.OnlineNum);
//文书个数
cell = row.CreateCell(9);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.QuanType.PSNum);
//资深文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//高级文书文案
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.OldSenior);
//文书文案
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.OldAuthor);
//转案区间
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(15);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(6 + ukHandleNumDetails.Count);
row.HeightInPoints = 12;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
row = sheet.CreateRow(7+ ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("1、申请学历填写标准的申请类别");
cell.CellStyle = style;
row = sheet.CreateRow(8 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("2、量化手写无效；需要机打。");
cell.CellStyle = style;
row = sheet.CreateRow(9 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("3、寄出后2个工作日内填报量化。");
cell.CellStyle = style;
row = sheet.CreateRow(10 + ukHandleNumDetails.Count);
cell = row.CreateCell(0);
cell.SetCellValue("4、博士申请及英国前四所本硕申请一个合同号算2个寄出个数，国内合作一个合同号算0.5个寄出个数。");
cell.CellStyle = style;
//下面开始导出量化
sheet = workbook.GetSheet("量化单");
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.GetRow(3);
cell = row.CreateCell(1);
cell.SetCellValue("英亚院校申请3部");
cell.CellStyle = style;
cell = row.CreateCell(6);
cell.SetCellValue(dt.ToString("yyyy/MM/dd"));
cell.CellStyle = style;
cell = row.CreateCell(11);
cell.SetCellValue(ukHandleQuanDetails.Count + "个");
cell.CellStyle = style;
//设置单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
//方框样式
trueOrFalseStyle = workbook.CreateCellStyle();
trueOrFalseStyle.Alignment = HorizontalAlignment.Center;
trueOrFalseStyle.VerticalAlignment = VerticalAlignment.Center;
trueOrFalseStyle.BorderTop = BorderStyle.Thin;
trueOrFalseStyle.BorderRight = BorderStyle.Thin;
trueOrFalseStyle.BorderLeft = BorderStyle.Thin;
trueOrFalseStyle.BorderBottom = BorderStyle.Thin;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
trueOrFalseStyle.SetFont(font);
i = 0;
//for (int i = 0; i < ukHandleNumDetails.Count; i++)
//{
foreach (Model.DTO.UKHandleQuanDetail item in ukHandleQuanDetails.Values)
{
row = sheet.CreateRow(6 + i);
row.HeightInPoints = 16.7F;
//序号
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(i + 1);
//合同号
cell = row.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue(item.ContractNum);
//学生姓名
cell = row.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue(item.StudentName);
//院校英文名称
cell = row.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue(item.University);
//量化类别
cell = row.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue(item.ApplicationType);
//网申寄出
cell = row.CreateCell(5);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Online);
//套磁
cell = row.CreateCell(6);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.Magnetic);
//文书
cell = row.CreateCell(7);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.SendQuan.PS);
//录取
cell = row.CreateCell(8);
cell.CellStyle = trueOrFalseStyle;
cell.SetCellValue(item.Admission);
//资深文案
cell = row.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Senior);
//制作文案
cell = row.CreateCell(10);
cell.CellStyle = style;
cell.SetCellValue(item.CopyWriting.Author);
//文书文案
cell = row.CreateCell(11);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Author);
//文书部门
cell = row.CreateCell(12);
cell.CellStyle = style;
cell.SetCellValue(item.PS.Department);
//转案区间
cell = row.CreateCell(13);
cell.CellStyle = style;
cell.SetCellValue(item.GetPeriod);
//备注
cell = row.CreateCell(14);
cell.CellStyle = style;
cell.SetCellValue(item.Note);
i++;
}
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(6 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("编制人:");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell = row.CreateCell(3);
cell.SetCellValue("经理审核：");
cell.CellStyle = style;
cell = row.CreateCell(10);
cell.SetCellValue("总监审核：");
cell.CellStyle = style;
//设置左对齐单元格格式
style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
style.SetFont(font);
row = sheet.CreateRow(7 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("1、量化手写无效；需要机打。");
cell.CellStyle = style;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
row = sheet.CreateRow(8 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("2、寄出后2个工作日内填报量化。");
row = sheet.CreateRow(9 + ukHandleQuanDetails.Count);
row.HeightInPoints = 16.7F;
cell = row.CreateCell(0);
cell.SetCellValue("3、申请学历填写标准的申请类别");
cell.CellStyle = style;
//下面开始保存
fileName = getAvailableFileName(fileName, System.IO.Path.GetFileNameWithoutExtension(fileName), 1);
FileStream file = File.Create(fileName);
workbook.Write(file);
file.Close();
stream.Close();
}
private void button7_Click(object sender, EventArgs e)
{
ConexionPostgres conn = new ConexionPostgres();
ConexionPostgres conn1 = new ConexionPostgres();
string quorum1 = "";
var cadenaSqll = "SELECT sum(b.coeficiente) FROM modelo.asamblea_unidad_residencial AS a LEFT JOIN modelo.unidad_residencial AS b ON (a.numero_unidad = b.numero_unidad AND a.nit = b.nit) WHERE a.nit='" + this.valor + "' AND a.fecha='" + this.fecha + "';";
double asistenciaCasoCoeficientes1 = Double.Parse(conn1.consultar(cadenaSqll)[0]["sum"]);
cadenaSqll = "SELECT sum(coeficiente) FROM modelo.unidad_residencial WHERE nit='" + this.valor + "';";
double registradosCasoCoeficientes1 = Double.Parse(conn1.consultar(cadenaSqll)[0]["sum"]);
double porcentaje1 = (100 * (asistenciaCasoCoeficientes1) / registradosCasoCoeficientes1);
porcentaje1 = Math.Round(porcentaje1, 2);
quorum1 = (porcentaje1).ToString();
string quorum = "";
var cadenaSql2 = "SELECT sum(b.coeficiente) FROM modelo.asamblea_unidad_residencial AS a LEFT JOIN modelo.unidad_residencial AS b ON (a.numero_unidad = b.numero_unidad AND a.nit = b.nit) WHERE a.nit='" + this.valor + "' AND a.fecha='" + this.fecha + "';";
double asistenciaCasoCoeficientes = Double.Parse(conn.consultar(cadenaSql2)[0]["sum"]);
cadenaSql2 = "SELECT sum(coeficiente) FROM modelo.unidad_residencial WHERE nit='" + this.valor + "';";
double registradosCasoCoeficientes = Double.Parse(conn.consultar(cadenaSql2)[0]["sum"]);
var cadenaSql1 = "SELECT sum(b.coeficiente) FROM modelo.asamblea_unidad_residencial AS a LEFT JOIN modelo.unidad_residencial AS b ON (a.numero_unidad = b.numero_unidad AND a.nit = b.nit) WHERE a.nit='" + this.valor + "' AND a.fecha='" + this.fecha + "' and id_tipo_asistencia_final ='4';";
var registro = conn.consultar(cadenaSql1);
if (registro[0]["sum"] != "")
{
double registradosCasoUnidadesdescargue = Double.Parse(conn.consultar(cadenaSql1)[0]["sum"]);
double porcentaje2 = (100 * (asistenciaCasoCoeficientes - registradosCasoUnidadesdescargue) / registradosCasoCoeficientes);
porcentaje1 = Math.Round(porcentaje1, 2);
quorum = (porcentaje1).ToString();
}
else
{
double porcentaje = (100 * (asistenciaCasoCoeficientes) / registradosCasoCoeficientes);
porcentaje = Math.Round(porcentaje, 2);
quorum = (porcentaje).ToString();
}
//Cómo tú usaste XLSX para la lectura de archivos utilicé esta librería
//Si quieres XlS sería otro cuento
//Se crea un libro de trabajo (como siempre pueden ser atributos de la clase si se desea usar en varios m[etodos)
XSSFWorkbook workbook = new XSSFWorkbook();
//Se crea una hoja para el libro de la (hoja de cálculo)
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet1"); ;
//Creo una variable que es similar a la que se retorna en las consultas SQL
var cadenaSql = " SELECT ur.nit, ur.numero_unidad, ur.nombre_completo, ur.coeficiente, ur.documento, CASE WHEN aur.id_tipo_asistencia_inicial=1 THEN 'presencial' WHEN aur.id_tipo_asistencia_inicial=2 THEN 'poder' WHEN aur.id_tipo_asistencia_inicial=3 THEN 'no asistio' ELSE 'no asistio' END AS tipo_asistencia_inicial, CASE WHEN aur.id_tipo_asistencia_final=1 THEN 'presencial' WHEN aur.id_tipo_asistencia_final=2 THEN 'poder' WHEN aur.id_tipo_asistencia_final=3 THEN 'no asistio' WHEN aur.id_tipo_asistencia_final=4 THEN 'se retiro antes de finalizar' ELSE 'no asistio' END AS tipo_asistencia_final FROM modelo.unidad_residencial AS ur LEFT OUTER JOIN modelo.asamblea_unidad_residencial AS aur ON ur.numero_unidad = aur.numero_unidad WHERE ur.nit='" + this.valor + "' order by ur.numero_unidad asc;";
var resultado = conn.consultar(cadenaSql);
//List<Dictionary<string, string>> resultado = new List<Dictionary<string, string>>();
//resultado.Add(new Dictionary<string, string>{
// { "nit", "999999999" },
// { "nombre", "Primera asamblea de Jennifer, mucha plata" },
// { "fecha", "2016-01-29" },
// { "id", "1" }
//});
//resultado.Add(new Dictionary<string, string>{
// { "nit", "999999999" },
// { "nombre", "Un gran día 1234569" },
// { "fecha", "2069-01-29" },
// { "id", "2" }
//});
////Se termina la creación de la variable
//Se escriben las cabeceras del reporte, primero se crea la fila
var primerFilaExcel = sheet.CreateRow(0);
var segundaFilaExcel = sheet.CreateRow(1);
//También creo una fuente NEGRILLA para ponerselas a esas celdas
var boldFont = workbook.CreateFont();
boldFont.FontHeightInPoints = 11;
boldFont.FontName = "Calibri";
boldFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
var style = workbook.CreateCellStyle();
style.SetFont(boldFont);
//Se crea una celda, se le pone el estilo NEGRILLA y se le pone el valor
var cell = primerFilaExcel.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue("NIT");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(1);
cell.CellStyle = style;
cell.SetCellValue("NUMERO UNIDAD");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue("NOMBRE");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue("COEFICIENTE");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue("DOCUMENTO");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue("ASISTENCIA INICIAL");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue("ASISTENCIA FINAL");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(7);
cell.CellStyle = style;
cell.SetCellValue("QUORUM INICIAL");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue("");
//OTRA CELDA
cell = primerFilaExcel.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue("QUORUM FINAL");
//Se desocupa la variables para que no ocupen espacio
cell = null;
primerFilaExcel = null;
int contadornoasistio = 0, contadornoasistiof = 0;
int contadornpoder = 0, contadornpoderf = 0;
int contadorpresen = 0, contadorpresenf = 0, contadorreutiro = 0;
//Se crea un for como siempre que recorre el resultado
for (int i = 0; i < resultado.Count; i++)
{
Dictionary<string, string> fila = resultado[i];
var filaExcel = sheet.CreateRow(i + 1);//La fila comienza desde la posición 1
filaExcel.CreateCell(0).SetCellValue(fila["nit"]);
filaExcel.CreateCell(1).SetCellValue(fila["numero_unidad"]);
filaExcel.CreateCell(2).SetCellValue(fila["nombre_completo"]);
filaExcel.CreateCell(3).SetCellValue(fila["coeficiente"]);
filaExcel.CreateCell(4).SetCellValue(fila["documento"]);
filaExcel.CreateCell(5).SetCellValue(fila["tipo_asistencia_inicial"]);
filaExcel.CreateCell(6).SetCellValue(fila["tipo_asistencia_final"]);
if (fila["tipo_asistencia_inicial"] == "no asistio")
{
contadornoasistio++;
}
if (fila["tipo_asistencia_inicial"] == "poder")
{
contadornpoder++;
}
if (fila["tipo_asistencia_inicial"] == "presencial")
{
contadorpresen++;
}
if (fila["tipo_asistencia_final"] == "no asistio")
{
contadornoasistiof++;
}
if (fila["tipo_asistencia_final"] == "poder")
{
contadornpoderf++;
}
if (fila["tipo_asistencia_final"] == "presencial")
{
contadorpresenf++;
}
if (fila["tipo_asistencia_final"] == "se retiro antes de finalizar")
{
contadorreutiro++;
}
}
var rowExcel = sheet.GetRow(1);
rowExcel = sheet.GetRow(4);
rowExcel.CreateCell(7).SetCellValue("No Asistió");
rowExcel.CreateCell(8).SetCellValue(contadornoasistio);
rowExcel.CreateCell(9).SetCellValue("No Asistió");
rowExcel.CreateCell(10).SetCellValue(contadornoasistiof);
rowExcel = sheet.GetRow(2);
rowExcel.CreateCell(7).SetCellValue("Por Poder");
rowExcel.CreateCell(8).SetCellValue(contadornpoder);
rowExcel.CreateCell(9).SetCellValue("Por Poder");
rowExcel.CreateCell(10).SetCellValue(contadornpoderf);
rowExcel = sheet.GetRow(3);
rowExcel.CreateCell(7).SetCellValue("Presencial");
rowExcel.CreateCell(8).SetCellValue(contadorpresen);
rowExcel.CreateCell(9).SetCellValue("Presencial");
rowExcel.CreateCell(10).SetCellValue(contadorpresenf);
rowExcel = sheet.GetRow(5);
rowExcel.CreateCell(9).SetCellValue("Se retiro");
rowExcel.CreateCell(10).SetCellValue(contadorreutiro);
rowExcel = sheet.GetRow(6);
rowExcel.CreateCell(8).SetCellValue(quorum1 + "%");
rowExcel.CreateCell(10).SetCellValue(quorum + "%");
//Falta validar si el archivo está o no abierto por otra aplicación...
var cadenaSql11 = "select nombre from modelo.propiedad_horizontal where nit='" + this.valor + "' ";
var nombre1 = conn.consultar(cadenaSql11);
var nombre = conn.consultar(cadenaSql11)[0]["nombre"];
using (var fs = new FileStream("lista_asistencia-" + nombre + ".xlsx", FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
fs.Close();
//borrar anuncio cuando ya no sea necesario
MessageBox.Show("El archivo se guardó en la ruta: " + fs.Name);
}
}
public void TestCloneStyleSameWB()
{
XSSFWorkbook wb = new XSSFWorkbook();
Assert.AreEqual(1, wb.NumberOfFonts);
XSSFFont fnt = (XSSFFont)wb.CreateFont();
fnt.FontName = ("TestingFont");
Assert.AreEqual(2, wb.NumberOfFonts);
XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
orig.Alignment = (HorizontalAlignment.Right);
orig.SetFont(fnt);
orig.DataFormat = (short)18;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(fnt, orig.GetFont());
Assert.AreEqual(18, orig.DataFormat);
XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreNotEqual(fnt, clone.GetFont());
Assert.AreNotEqual(18, clone.DataFormat);
clone.CloneStyleFrom(orig);
Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
Assert.AreEqual(fnt, clone.GetFont());
Assert.AreEqual(18, clone.DataFormat);
Assert.AreEqual(2, wb.NumberOfFonts);
clone.Alignment = HorizontalAlignment.Left;
clone.DataFormat = 17;
Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
Assert.AreEqual(18, orig.DataFormat);
Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
}
public static System.IO.MemoryStream createExcelReport(DataTable dt)
{
using (System.IO.MemoryStream mem = new System.IO.MemoryStream())
{
var workbook = new NPOI.XSSF.UserModel.XSSFWorkbook();
#region Cell Styles
#region HeaderLabel Cell Style
var headerLabelCellStyle = workbook.CreateCellStyle();
headerLabelCellStyle.Alignment = HorizontalAlignment.Center;
headerLabelCellStyle.BorderBottom = BorderStyle.Thin;
headerLabelCellStyle.BorderRight = BorderStyle.Thin;
headerLabelCellStyle.BorderTop = BorderStyle.Thin;
headerLabelCellStyle.BorderLeft = BorderStyle.Thin;
var headerLabelFont = workbook.CreateFont();
headerLabelFont.Boldweight = (short)FontBoldWeight.Bold;
headerLabelCellStyle.SetFont(headerLabelFont);
#endregion
#region RightAligned Cell Style
var rightAlignedCellStyle = workbook.CreateCellStyle();
rightAlignedCellStyle.Alignment = HorizontalAlignment.Right;
rightAlignedCellStyle.BorderBottom = BorderStyle.Thin;
rightAlignedCellStyle.BorderRight = BorderStyle.Thin;
rightAlignedCellStyle.BorderTop = BorderStyle.Thin;
rightAlignedCellStyle.BorderLeft = BorderStyle.Thin;
#endregion
#region Currency Cell Style
var CellStyleRight = workbook.CreateCellStyle();
CellStyleRight.Alignment = HorizontalAlignment.Right;
CellStyleRight.BorderBottom = BorderStyle.Thin;
CellStyleRight.BorderRight = BorderStyle.Thin;
CellStyleRight.BorderTop = BorderStyle.Thin;
CellStyleRight.BorderLeft = BorderStyle.Thin;
#endregion
#region Detail Currency Subtotal Style
var CellStyleNormal = workbook.CreateCellStyle();
CellStyleNormal.BorderBottom = BorderStyle.Thin;
CellStyleNormal.BorderRight = BorderStyle.Thin;
CellStyleNormal.BorderTop = BorderStyle.Thin;
CellStyleNormal.BorderLeft = BorderStyle.Thin;
var detailCurrencySubtotalFont = workbook.CreateFont();
detailCurrencySubtotalFont.Boldweight = (short)FontBoldWeight.Normal;
CellStyleNormal.SetFont(detailCurrencySubtotalFont);
#endregion
#endregion
var sheet = workbook.CreateSheet(dt.TableName);
var rowIndex = 0;
var row = sheet.CreateRow(rowIndex);
List <String> columns = new List <string>();
for (int i = 0; i < dt.Columns.Count; i++)
{
columns.Add(dt.Columns[i].ColumnName);
var cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
cell.CellStyle = headerLabelCellStyle;
}
rowIndex++;
//// Add data rows
foreach (System.Data.DataRow item in dt.Rows)
{
row = sheet.CreateRow(rowIndex);
for (int ii = 0; ii < dt.Columns.Count; ii++)
{
var cell = row.CreateCell(ii);
cell.SetCellValue(dt.Columns[ii].ColumnName);
if (dt.Columns[ii].DataType == typeof(System.Decimal))
{
cell.CellStyle = CellStyleRight;
}
else
{
cell.CellStyle = CellStyleNormal;
}
cell.SetCellValue(item[dt.Columns[ii].ColumnName].ToString());
}
rowIndex++;
}
for (var i = 0; i < sheet.GetRow(0).LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(mem);
return(mem);
}
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/"Arial");
rt.ApplyFont(font);
shape.SetText(/*setter*/rt);
Assert.IsNotNull(shape.GetCTShape());
Assert.IsNotNull(shape.GetEnumerator());
Assert.IsNotNull(XSSFSimpleShape.GetPrototype());
foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
{
shape.TextParagraphs[(0)].SetBullet(nr);
Assert.IsNotNull(shape.Text);
}
shape.TextParagraphs[(0)].IsBullet = (false);
Assert.IsNotNull(shape.Text);
shape.SetText("testtext");
Assert.AreEqual("testtext", shape.Text);
shape.SetText(new XSSFRichTextString());
//Assert.AreEqual("null", shape.Text);
Assert.AreEqual(String.Empty, shape.Text);
shape.AddNewTextParagraph();
shape.AddNewTextParagraph("test-other-text");
shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
shape.AddNewTextParagraph(new XSSFRichTextString());
//Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);
Assert.AreEqual("test-other-text\nrtstring\n", shape.Text);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.CLIP);
Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/TextHorizontalOverflow.OVERFLOW);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.CLIP);
Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/TextVerticalOverflow.OVERFLOW);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.Bottom;
Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.Top;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.STACKED);
Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.HORIZONTAL);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/12.32);
Assert.AreEqual(12.32, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/12.31);
Assert.AreEqual(12.31, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/13.31);
Assert.AreEqual(13.31, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/23.31);
Assert.AreEqual(23.31, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/-1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
Assert.IsTrue(shape.WordWrap);
shape.WordWrap = (/*setter*/false);
Assert.IsFalse(shape.WordWrap);
shape.WordWrap = (/*setter*/true);
Assert.IsTrue(shape.WordWrap);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/TextAutofit.NORMAL);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/TextAutofit.SHAPE);
Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
shape.TextAutofit = (/*setter*/TextAutofit.NONE);
Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);
Assert.AreEqual(5, shape.ShapeType);
shape.ShapeType = (/*setter*/23);
Assert.AreEqual(23, shape.ShapeType);
// TODO: should this be supported?
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
Assert.IsNotNull(shape.GetShapeProperties());
}
finally
{
wb.Close();
}
}
public void TestXSSFTextParagraph()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
XSSFTextBox shape = Drawing.CreateTextbox(new XSSFClientAnchor(0, 0, 0, 0, 2, 2, 3, 4)) as XSSFTextBox;
XSSFRichTextString rt = new XSSFRichTextString("Test String");
XSSFFont font = wb.CreateFont() as XSSFFont;
Color color = Color.FromArgb(0, 255, 255);
font.SetColor(new XSSFColor(color));
font.FontName = (/*setter*/ "Arial");
rt.ApplyFont(font);
shape.SetText(/*setter*/ rt);
Assert.IsNotNull(shape.GetCTShape());
Assert.IsNotNull(shape.GetEnumerator());
Assert.IsNotNull(XSSFSimpleShape.GetPrototype());
foreach (ListAutoNumber nr in Enum.GetValues(typeof(ListAutoNumber)))
{
shape.TextParagraphs[(0)].SetBullet(nr);
Assert.IsNotNull(shape.Text);
}
shape.TextParagraphs[(0)].SetBullet(false);
Assert.IsNotNull(shape.Text);
shape.SetText("testtext");
Assert.AreEqual("testtext", shape.Text);
shape.SetText(new XSSFRichTextString());
Assert.AreEqual("null", shape.Text);
shape.AddNewTextParagraph();
shape.AddNewTextParagraph("test-other-text");
shape.AddNewTextParagraph(new XSSFRichTextString("rtstring"));
shape.AddNewTextParagraph(new XSSFRichTextString());
Assert.AreEqual("null\n\ntest-other-text\nrtstring\nnull", shape.Text);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.CLIP);
Assert.AreEqual(TextHorizontalOverflow.CLIP, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = (/*setter*/ TextHorizontalOverflow.OVERFLOW);
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
shape.TextHorizontalOverflow = TextHorizontalOverflow.None;
Assert.AreEqual(TextHorizontalOverflow.OVERFLOW, shape.TextHorizontalOverflow);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.CLIP);
Assert.AreEqual(TextVerticalOverflow.CLIP, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = (/*setter*/ TextVerticalOverflow.OVERFLOW);
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
shape.TextVerticalOverflow = TextVerticalOverflow.None;
Assert.AreEqual(TextVerticalOverflow.OVERFLOW, shape.TextVerticalOverflow);
Assert.AreEqual((short)VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.Bottom;
Assert.AreEqual(VerticalAlignment.Bottom, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.Top;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
shape.VerticalAlignment = (short)VerticalAlignment.None;
Assert.AreEqual(VerticalAlignment.Top, shape.VerticalAlignment);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.STACKED);
Assert.AreEqual(TextDirection.STACKED, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.HORIZONTAL);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
shape.TextDirection = (/*setter*/ TextDirection.None);
Assert.AreEqual(TextDirection.HORIZONTAL, shape.TextDirection);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ 12.32);
Assert.AreEqual(12.32, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
shape.BottomInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.BottomInset, 0.01);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ 12.31);
Assert.AreEqual(12.31, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
shape.LeftInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.LeftInset, 0.01);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ 13.31);
Assert.AreEqual(13.31, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
shape.RightInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.RightInset, 0.01);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ 23.31);
Assert.AreEqual(23.31, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
shape.TopInset = (/*setter*/ -1);
Assert.AreEqual(3.6, shape.TopInset, 0.01);
Assert.IsTrue(shape.WordWrap);
shape.WordWrap = (/*setter*/ false);
Assert.IsFalse(shape.WordWrap);
shape.WordWrap = (/*setter*/ true);
Assert.IsTrue(shape.WordWrap);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NORMAL);
Assert.AreEqual(TextAutofit.NORMAL, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.SHAPE);
Assert.AreEqual(TextAutofit.SHAPE, shape.TextAutofit);
shape.TextAutofit = (/*setter*/ TextAutofit.NONE);
Assert.AreEqual(TextAutofit.NONE, shape.TextAutofit);
Assert.AreEqual(5, shape.ShapeType);
shape.ShapeType = (/*setter*/ 23);
Assert.AreEqual(23, shape.ShapeType);
// TODO: should this be supported?
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
// shape.ShapeType=(/*setter*/-1);
// Assert.AreEqual(-1, shape.ShapeType);
Assert.IsNotNull(shape.GetShapeProperties());
}
finally
{
wb.Close();
}
}
