private void button1_Click_1(object sender, EventArgs e)
{
button1.Text = "执行中..";
button1.Enabled = false;
bool check = true;
if (!File.Exists(xlsxFileOpenPath))
{
MessageBox.Show("找不到这个文件哦~小猪，看看文件路径对不对~");
button1.Text = "执行";
button1.Enabled = true;
return;
}
FileStream adcFileStream = new FileStream(xlsxFileOpenPath, FileMode.Open);
XSSFWorkbook sourceWorkbook = new XSSFWorkbook(adcFileStream);
XSSFSheet sourceSheet = (XSSFSheet)sourceWorkbook.GetSheet(sheetName);
if (sourceSheet == null)
{
MessageBox.Show("找不到这个表名哦！小猪，检查一下表名对不对：" + sheetName);
adcFileStream.Close();
sourceWorkbook.Close();
button1.Text = "执行";
button1.Enabled = true;
return;
}
firstColume = GetInter(firstCloumeTB.Text);
lastColume = GetInter(lastColumeTB.Text) + 1;
XSSFWorkbook destWorkbook = new XSSFWorkbook();
XSSFSheet destWorkSheet = (XSSFSheet)destWorkbook.CreateSheet(sheetName);
for (int i = 0; i < sourceSheet.PhysicalNumberOfRows; i++)
{
IRow row = sourceSheet.GetRow(i);
IRow dRow = destWorkSheet.CreateRow(i);
if (row != null)
{
for (int j = 0; j < lastColume; j++)
{
ICell sCell = row.GetCell(j);
ICell dCell = dRow.CreateCell(j);
if (sCell != null && dCell != null)
{
string cellStr = "";
try
{
cellStr = sCell.StringCellValue;
}
catch (InvalidOperationException exception)
{
cellStr = "该格异常！";
}
dCell.SetCellValue(cellStr);
if (j >= firstColume)
{
try
{
string value = dCell.StringCellValue;
MatchCollection mc = System.Text.RegularExpressions.Regex.Matches(value, @"\d{2}:\d{2}");
if (mc.Count > 2)
{
string result = string.Format("{0} \n{1}", mc[0], mc[mc.Count - 1]);
dCell.SetCellValue(result);
ICellStyle styeCellStyle = destWorkbook.CreateCellStyle();
styeCellStyle.FillForegroundColor = 57;
styeCellStyle.FillPattern = FillPattern.SolidForeground;
dCell.CellStyle = styeCellStyle;
}
}
catch (Exception ex)
{
check = false;
MessageBox.Show("执行异常：" + ex.Message);
}
}
}
}
}
}
FileStream sw = File.Create(xlsxFileOutputPath);
destWorkbook.Write(sw);
sw.Close();
adcFileStream.Close();
sourceWorkbook.Close();
destWorkbook.Close();
if (check)
{
DialogResult dr = MessageBox.Show("是否打开表格", "完成", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
if (dr == DialogResult.OK)
{
if (File.Exists(xlsxFileOutputPath))
{
System.Diagnostics.Process.Start(xlsxFileOutputPath);
}
}
}
else
{
MessageBox.Show("出错了！！");
}
button1.Text = "执行";
button1.Enabled = true;
}
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();
}
}
static void Main(string[] args)
{
Dictionary <string, int> dic = new Dictionary <string, int>();
FileStream infile = new FileStream(@"E:\C_Cpp_and_Csharp\DataAnalisis\passingevents.xlsx", FileMode.Open, FileAccess.Read);
XSSFWorkbook inworkbook = new XSSFWorkbook(infile);
ISheet insheet = inworkbook.GetSheet("passingevents");
for (int i = 1; i < 60000; i++)
{
IRow row = insheet.GetRow(i);
if (row != null)
{
var org = row.GetCell(2).ToString();
var des = row.GetCell(3).ToString();
if (org.Substring(0, 7) == "Huskies" && des.Substring(0, 7) == "Huskies")
{
var key = org.Substring(8) + "_" + des.Substring(8);
if (dic.ContainsKey(org.Substring(8)))
{
++dic[org.Substring(8)];
}
else
{
dic.Add(org.Substring(8), 1);
}
if (dic.ContainsKey(des.Substring(8)))
{
++dic[des.Substring(8)];
}
else
{
dic.Add(des.Substring(8), 1);
}
}
}
}
//foreach(var item in dic)
//{
// Console.WriteLine(item.Key + ": " + item.Value);
//}
XSSFWorkbook outwork = new XSSFWorkbook();
outwork.CreateSheet("1");
XSSFSheet outsheet = (XSSFSheet)outwork.GetSheet("1");
for (int i = 0; i < dic.Count; i++)
{
outsheet.CreateRow(i);
}
int k = 0;
foreach (var item in dic)
{
XSSFRow outsheetRow = (XSSFRow)outsheet.GetRow(k);
XSSFCell[] outxSSFCells = new XSSFCell[3];
for (int i = 0; i < 2; i++)
{
outxSSFCells[i] = (XSSFCell)outsheetRow.CreateCell(i);
}
outxSSFCells[0].SetCellValue(item.Key);
outxSSFCells[1].SetCellValue(item.Value);
++k;
}
FileStream outfile = new FileStream(@"E:\C_Cpp_and_Csharp\DataAnalisis\DataAnalisis\Everyone.xlsx", FileMode.Create);
outwork.Write(outfile);
outfile.Close();
outwork.Close();
infile.Close();
inworkbook.Close();
}
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 TestAddBulletParagraphs()
{
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, 10, 20));
String paraString1 = "A normal paragraph";
String paraString2 = "First bullet";
String paraString3 = "Second bullet (level 1)";
String paraString4 = "Third bullet";
String paraString5 = "Another normal paragraph";
String paraString6 = "First numbered bullet";
String paraString7 = "Second bullet (level 1)";
String paraString8 = "Third bullet (level 1)";
String paraString9 = "Fourth bullet (level 1)";
String paraString10 = "Fifth Bullet";
XSSFTextParagraph para = shape.AddNewTextParagraph(paraString1);
para = shape.AddNewTextParagraph(paraString2);
para.SetBullet(true);
para = shape.AddNewTextParagraph(paraString3);
para.SetBullet(true);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString4);
para.SetBullet(true);
para = shape.AddNewTextParagraph(paraString5);
para = shape.AddNewTextParagraph(paraString6);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD);
para = shape.AddNewTextParagraph(paraString7);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString8);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph("");
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString9);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD, 3);
para.Level = (1);
para = shape.AddNewTextParagraph(paraString10);
para.SetBullet(ListAutoNumber.ARABIC_PERIOD);
// Save and re-load it
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1);
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(12, paras.Count); // this should be 12 as XSSFSimpleShape Creates a default paragraph (no text), and then we Added to that
StringBuilder builder = new StringBuilder();
builder.Append(paraString1);
builder.Append("\n");
builder.Append("\u2022 ");
builder.Append(paraString2);
builder.Append("\n");
builder.Append("\t\u2022 ");
builder.Append(paraString3);
builder.Append("\n");
builder.Append("\u2022 ");
builder.Append(paraString4);
builder.Append("\n");
builder.Append(paraString5);
builder.Append("\n");
builder.Append("1. ");
builder.Append(paraString6);
builder.Append("\n");
builder.Append("\t3. ");
builder.Append(paraString7);
builder.Append("\n");
builder.Append("\t4. ");
builder.Append(paraString8);
builder.Append("\n");
builder.Append("\t"); // should be empty
builder.Append("\n");
builder.Append("\t5. ");
builder.Append(paraString9);
builder.Append("\n");
builder.Append("2. ");
builder.Append(paraString10);
Assert.AreEqual(builder.ToString(), sshape.Text);
checkRewrite(wb2);
wb2.Close();
}
public string descargarConsultaExcel(CONTRATO_SYS contrato, object[] filters)
{
var helperStyle = new Helpers.excelStyle();
try
{
var nombreArchivo = "Descarga " + filters[0].ToString() + " " + DateTime.Now.ToString("yyyyMMdd");
var rutaTemporal = @HttpContext.Current.Server.MapPath("~/Temp/Descargas/" + nombreArchivo + ".xlsx");
int total;
var book = new XSSFWorkbook();
string[] columns = { "Archivo", "Fecha Carga", "Usuario", "Nro Lineas", "Estado", "Moneda", "Importe" };
var sheet = book.CreateSheet(nombreArchivo);
var rowBook = sheet.CreateRow(1);
var headerStyle = helperStyle.setFontText(12, true, book);
var bodyStyle = helperStyle.setFontText(11, false, book);
ICell cellBook;
for (int i = 0; i < columns.Length; i++)
{
cellBook = rowBook.CreateCell(i + 1);
cellBook.SetCellValue(columns[i]);
cellBook.CellStyle = headerStyle;
}
var listSegDescarga = new nSegDescarga().listSegDescarga(contrato, filters, 0, 100000, "Estado ASC", out total);
for (int i = 0; i < listSegDescarga.Count; i++)
{
var rowBody = sheet.CreateRow(2 + i);
ICell cellNombre = rowBody.CreateCell(1);
cellNombre.SetCellValue(listSegDescarga[i].NombreArchivo);
cellNombre.CellStyle = bodyStyle;
ICell cellFecCarga = rowBody.CreateCell(2);
cellFecCarga.SetCellValue(listSegDescarga[i].FechaCarga.ToShortDateString());
cellFecCarga.CellStyle = bodyStyle;
ICell cellUsuario = rowBody.CreateCell(3);
cellUsuario.SetCellValue(listSegDescarga[i].Usuario);
cellUsuario.CellStyle = bodyStyle;
ICell cellNroLinea = rowBody.CreateCell(4);
cellNroLinea.SetCellValue(listSegDescarga[i].NroLineas);
cellNroLinea.CellStyle = bodyStyle;
ICell cellEstado = rowBody.CreateCell(5);
cellEstado.SetCellValue(listSegDescarga[i].Estado);
cellEstado.CellStyle = bodyStyle;
ICell cellMoneda = rowBody.CreateCell(6);
cellMoneda.SetCellValue(listSegDescarga[i].Moneda);
cellMoneda.CellStyle = bodyStyle;
ICell cellImporte = rowBody.CreateCell(7);
cellImporte.SetCellValue(listSegDescarga[i].Importe);
cellImporte.CellStyle = bodyStyle;
}
if (File.Exists(rutaTemporal))
{
File.Delete(rutaTemporal);
}
using (var file = new FileStream(rutaTemporal, FileMode.Create, FileAccess.ReadWrite))
{
book.Write(file);
file.Close();
book.Close();
}
return(rutaTemporal);
}
catch (Exception ex)
{
throw;
}
}
private void importExcel(string pathFile)
{
// Lấy stream file
var fs = new FileStream(pathFile, FileMode.Open);
// Khởi tạo workbook để đọc
var wb = new XSSFWorkbook(fs);
// Lấy sheet đầu tiên
var sheet = wb.GetSheetAt(0);
var proships = new List <DeliveryProship>();
// Lấy data từ dòng thứ 3 cho đến hết file
for (int index = 11; index < sheet.LastRowNum; index++)
{
var row = sheet.GetRow(index);
var item = new DeliveryProship();
// OrderID
if (!String.IsNullOrEmpty(row.GetCell(29).ToString()))
{
var strOrderID = Regex.Match(row.GetCell(29).ToString(), @"[0-9]+").Value;
if (!String.IsNullOrEmpty(strOrderID))
{
item.OrderID = Convert.ToInt32(strOrderID);
}
}
// NumberID
item.NumberID = row.GetCell(30).ToString();
// Customer
item.Customer = row.GetCell(5).ToString();
// Phone
item.Phone = row.GetCell(6).ToString().Replace(",", " |");
// Address
item.Address = row.GetCell(7).StringCellValue;
// Delivery Status
item.DeliveryStatus = row.GetCell(26).StringCellValue;
// Start Date
item.StartDate = DateTime.ParseExact(row.GetCell(3).ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
// Done Date
if (!String.IsNullOrEmpty(row.GetCell(4).ToString()))
{
item.DoneDate = DateTime.ParseExact(row.GetCell(4).ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture);
}
// Tiền thu hộ
item.COD = Convert.ToDecimal(row.GetCell(22).ToString());
// Tiền phí
item.Fee = Convert.ToDecimal(row.GetCell(20).ToString());
// Staff
item.Staff = String.Empty;
// Setting trạng thái review với order
item.Review = (int)DeliveryProshipReview.NoApprove;
item.OrderStatus = (int)OrderStatus.Exist;
proships.Add(item);
// Thưc thi review các order sau khi đoc 100 dòng execl
if (proships.Count == 100)
{
DeliveryProshipController.reviewOrder(proships);
proships.Clear();
}
}
// Thưc thi review các order sau khi đoc dòng excel còn lại
if (proships.Count > 0)
{
DeliveryProshipController.reviewOrder(proships);
proships.Clear();
}
wb.Close();
fs.Close();
}
static void Main(string[] args)
{
Dictionary <string, int> dic = new Dictionary <string, int>();
FileStream infile = new FileStream(@"E:\C_Cpp_and_Csharp\DataAnalisis\passingevents.xlsx", FileMode.Open, FileAccess.Read);
XSSFWorkbook inworkbook = new XSSFWorkbook(infile);
ISheet insheet = inworkbook.GetSheet("passingevents");
for (int i = 1; i < 60000; i++)
{
IRow row = insheet.GetRow(i);
IRow row2 = insheet.GetRow(i + 1);
if (row != null && row2 != null)
{
var rowstr = row.GetCell(3).ToString();
var row2str = row2.GetCell(3).ToString();
if (rowstr.Substring(0, 7) == "Huskies" && row2str.Substring(0, 8) == "Opponent")
{
var team = rowstr;
if (dic.ContainsKey(team))
{
dic[team]++;
}
else
{
dic.Add(team, 1);
}
}
}
}
XSSFWorkbook outwork = new XSSFWorkbook();
outwork.CreateSheet("1");
XSSFSheet outsheet = (XSSFSheet)outwork.GetSheet("1");
for (int i = 0; i < dic.Count; i++)
{
outsheet.CreateRow(i);
}
int k = 0;
foreach (var item in dic)
{
XSSFRow outsheetRow = (XSSFRow)outsheet.GetRow(k);
XSSFCell[] outxSSFCells = new XSSFCell[3];
for (int i = 0; i < 2; i++)
{
outxSSFCells[i] = (XSSFCell)outsheetRow.CreateCell(i);
}
outxSSFCells[0].SetCellValue(item.Key);
outxSSFCells[1].SetCellValue(item.Value);
++k;
}
FileStream outfile = new FileStream(@"E:\C_Cpp_and_Csharp\DataAnalisis\DataAnalisis\球员传球失误数.xlsx", FileMode.Create);
outwork.Write(outfile);
outfile.Close();
outwork.Close();
infile.Close();
inworkbook.Close();
}
static void Main(string[] args)
{
Dictionary <string, int> dic = new Dictionary <string, int>();
Dictionary <string, int> xs = new Dictionary <string, int>();
Dictionary <string, int> ys = new Dictionary <string, int>();
Dictionary <string, int> nums = new Dictionary <string, int>();
FileStream infile = new FileStream(@"E:\C_Cpp_and_Csharp\DataAnalisis\passingevents.xlsx", FileMode.Open, FileAccess.Read);
XSSFWorkbook inworkbook = new XSSFWorkbook(infile);
ISheet insheet = inworkbook.GetSheet("passingevents");
for (int m = 1; m <= 16; m++)
{
for (int i = 1; i < 60000; i++)
{
IRow row = insheet.GetRow(i);
if (row != null)
{
var num = row.GetCell(0).ToString();
var org = row.GetCell(2).ToString();
var des = row.GetCell(3).ToString();
for (int r = 0; r < 2; r++)
{
var xori = Convert.ToInt32(row.GetCell(7 + 2 * r));
var yori = Convert.ToInt32(row.GetCell(8 + 2 * r));
if (org.Substring(0, 7) == "Huskies")
{
var key = num + "#" + org + "#" + des;
if (dic.ContainsKey(key))
{
++dic[key];
}
else
{
dic.Add(key, 1);
}
}
}
}
}
}
//foreach(var item in dic)
//{
// Console.WriteLine(item.Key + ": " + item.Value);
//}
XSSFWorkbook outwork = new XSSFWorkbook();
outwork.CreateSheet("1");
XSSFSheet outsheet = (XSSFSheet)outwork.GetSheet("1");
for (int i = 0; i < dic.Count; i++)
{
outsheet.CreateRow(i);
}
int k = 0;
foreach (var item in dic)
{
string str;
XSSFRow outsheetRow = (XSSFRow)outsheet.GetRow(k);
XSSFCell[] outxSSFCells = new XSSFCell[4];
for (int i = 0; i < 4; i++)
{
outxSSFCells[i] = (XSSFCell)outsheetRow.CreateCell(i);
}
if (item.Key[2] == '#')
{
outxSSFCells[0].SetCellValue(item.Key.Substring(0, 2));
str = item.Key.Substring(3);
}
else
{
outxSSFCells[0].SetCellValue(item.Key.Substring(0, 1));
str = item.Key.Substring(2);
}
//Huskies_M1#Huskies_M12
if (str[11] == '#')
{
outxSSFCells[1].SetCellValue(str.Substring(0, 11));
outxSSFCells[2].SetCellValue(str.Substring(12));
outxSSFCells[3].SetCellValue(item.Value);
}
if (str[10] == '#')
{
outxSSFCells[1].SetCellValue(str.Substring(0, 10));
outxSSFCells[2].SetCellValue(str.Substring(11));
outxSSFCells[3].SetCellValue(item.Value);
}
++k;
}
FileStream outfile = new FileStream(@"E:\C_Cpp_and_Csharp\DataAnalisis\DataAnalisis\excel\Haskies每场队员互传统计.xlsx", FileMode.Create);
outwork.Write(outfile);
outfile.Close();
outwork.Close();
infile.Close();
inworkbook.Close();
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
private static MemoryStream Export(DataTable dtSource)
{
XSSFWorkbook workbook = new XSSFWorkbook();
//HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
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 列头及样式
{
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
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;
//设置列宽
//解决导出excel表中的某个单元格数据过大，显示单元格最大列宽255错误
int colWidth = (arrColWidth[column.Ordinal] + 1) * 256;
if (colWidth < 255 * 256)
{
sheet.SetColumnWidth(column.Ordinal, colWidth < 3000 ? 3000 : colWidth);
}
else
{
sheet.SetColumnWidth(column.Ordinal, 6000);
}
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
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++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
workbook.Close();
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
//写xlsx文件
public static void WriteXlsxFile(List <Model.Schedule> slist)
{
//HSSF可以读取xls格式的Excel文件
//IWorkbook workbook = new HSSFWorkbook();
//XSSF可以读取xlsx格式的Excel文件
IWorkbook workbook = new XSSFWorkbook();
var grouplist = slist.GroupBy(s => new { s.ClassName });
foreach (var item in grouplist)
{
List <Model.Schedule> list = item.ToList();
ISheet sheet = workbook.CreateSheet(item.Key.ClassName + "班");
//创建行
IRow row = sheet.CreateRow(0); //i表示了创建行的索引，从0开始
//创建单元格
row.CreateCell(0).SetCellValue("学校id");
row.CreateCell(1).SetCellValue("年级id");
row.CreateCell(2).SetCellValue("班级id");
row.CreateCell(3).SetCellValue("班级");
row.CreateCell(4).SetCellValue("老师id");
row.CreateCell(5).SetCellValue("周");
row.CreateCell(6).SetCellValue("节");
row.CreateCell(7).SetCellValue("课程");
row.CreateCell(8).SetCellValue("课程id");
row.CreateCell(9).SetCellValue("科目");
for (int i = 0; i < list.Count; i++)
{
row = sheet.CreateRow(i + 1); //i表示了创建行的索引，从0开始
//创建单元格
row.CreateCell(0).SetCellValue("30E0E3D1341049D39AD513E854836AC1 ");
row.CreateCell(1).SetCellValue("AE89E4170D774DD28765463D94B35E2D");
row.CreateCell(2).SetCellValue(GetClassId(item.Key.ClassName));
row.CreateCell(3).SetCellValue(list[i].ClassName);
row.CreateCell(4).SetCellValue(list[i].TeacherName);
row.CreateCell(5).SetCellValue(list[i].Week);
row.CreateCell(6).SetCellValue(list[i].Section);
if (list[i].CourseName.IndexOf('/') != -1)
{
string[] temp = list[i].CourseName.Split('/');
//row.CreateCell(7).SetCellValue(list[i].CourseName);
//row.CreateCell(8).SetCellValue((GetCourseId(temp[0]) == "" ? "" : GetCourseId(temp[0]))+"/"+(GetCourseId(temp[1]) == "" ? "" : GetCourseId(temp[1])));
row.CreateCell(7).SetCellValue(temp[0]);
row.CreateCell(8).SetCellValue(GetCourseId(temp[0]) == "" ? "" : GetCourseId(temp[0]));
}
else
{
row.CreateCell(7).SetCellValue(list[i].CourseName);
row.CreateCell(8).SetCellValue(GetCourseId(list[i].CourseName) == "" ? "" : GetCourseId(list[i].CourseName));
}
row.CreateCell(9).SetCellValue(list[i].SubjectType);
//for (int j = 0; j < 6; j++)
//{
// ICell cell = row.CreateCell(j); //同时这个函数还有第二个重载，可以指定单元格存放数据的类型
// cell.SetCellValue(i.ToString() + j.ToString());
//}
}
}
//Excel文件至少要有一个工作表sheet
//表格制作完成后，保存
//创建一个文件流对象
using (FileStream fs = File.Open(@"D:\asp.net\workspace\test_beta_2_dan.xlsx", FileMode.OpenOrCreate))
{
workbook.Write(fs);
//最后记得关闭对象
workbook.Close();
}
}
public async Task <ActionResult> DescargaDisponibilidad(int sloc = 3000)
{
string filename = "";
XSSFWorkbook wb = null;
try
{
string dataquery = "SELECT " +
" CONVERT(nvarchar, z.DateStamp, 103) +' ' + CONVERT(nvarchar, z.DateStamp, 108) AS DateStamp," +
"convert(nvarchar(5),z.[SLoc]) AS[SLoc], " +
"z.[Material] AS[Material], " +
" upper(isnull(m.[Description], z.MaterialDescription )) AS [MaterialDescription]," +
"convert(int,sum(TotalStock-OpenOrdersQty-PickQty)) as [AvailableQty] " +
" FROM ApexRep.[dbo].[ZINV] as z " +
" left join ApexRep.[dbo].Materials as M on m.Material = z.Material " +
"where sloc = " + sloc +
" group by z.DateStamp, SLoc,SlocDesc, z.Material, m.Description, z.MaterialDescription";
var disponibilidad = await db.Database.SqlQuery <DisponibilidadViewModel>(dataquery).ToListAsync();
if (disponibilidad == null)
{
return(new HttpStatusCodeResult(HttpStatusCode.NotFound));
}
var date = DateTime.Now;
string baseFilename = AppDomain.CurrentDomain.BaseDirectory + "/App_Data/Disponibilidad_Base.xlsx";
filename = $"{AppDomain.CurrentDomain.BaseDirectory}/UploadFolder/Disponibilidad_{date.Year}{date.Month}{date.Day}{date.Hour}{date.Minute}{date.Second}{date.Millisecond}.xlsx";
System.IO.File.Copy(baseFilename, filename, true);
wb = new XSSFWorkbook(filename);
var sheet = wb.GetSheet("DISPONIBILIDAD");
int rowNo = 6;
string nl = Environment.NewLine;
sheet.GetRow(3).Cells[1].SetCellValue("Ultima actualización: " + disponibilidad.FirstOrDefault().DateStamp);
foreach (var d in disponibilidad)
{
var row = sheet.CreateRow(rowNo);
row.CreateCell(1);
row.CreateCell(2);
row.CreateCell(3);
row.CreateCell(4);
row.Cells[0].SetCellValue(d.SLoc);
row.Cells[1].SetCellValue(d.Material);
row.Cells[2].SetCellValue(d.MaterialDescription);
row.Cells[3].SetCellValue(d.AvailableQty);
rowNo++;
}
var ms = new MemoryStream();
wb.Write(ms);
return(File(ms.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"Disponibilidad_{date.Year}{date.Month}{date.Day}{date.Hour}{date.Minute}{date.Second}{date.Millisecond}.xlsx"));
}
catch (Exception ex)
{
return(new HttpStatusCodeResult(HttpStatusCode.InternalServerError, ex.Message));
}
finally
{
if (wb != null)
{
wb.Close();
}
if (System.IO.File.Exists(filename))
{
System.IO.File.Delete(filename);
}
}
//return View();
}
private static void ConfigParse()
{
if (Directory.Exists(ConfigConst.ConfigResPath))
{
Directory.Delete(ConfigConst.ConfigResPath, true);
}
Directory.CreateDirectory(ConfigConst.ConfigResPath);
string[] excelFiles = Directory.GetFiles(ConfigConst.ExcelResPath, "*.xlsx");
for (int i = 0; i < excelFiles.Length; i++)
{
FileStream fileStream = new FileStream(excelFiles[i], FileMode.Open, FileAccess.Read);
IWorkbook workbook = new XSSFWorkbook(fileStream);
fileStream.Close();
ISheet sheet = workbook.GetSheetAt(0); //获取第一个工作表
IRow iRowType = sheet.GetRow(0);
IRow iRowName = sheet.GetRow(2);
int colNum = iRowType.LastCellNum;
string[] dataType = new string[colNum];
string[] dataName = new string[colNum];
for (int col = 0; col < colNum; col++)
{
dataType[col] = iRowType.GetCell(col).ToString();
dataName[col] = iRowName.GetCell(col).ToString();
}
string tableData = "{0} = {{\n{1}}}";
string rowKey = " {0}_{1} = {{\n{2} }},\n";
string rowDataStr = string.Empty;
for (int row = 3; row < sheet.LastRowNum + 1; row++) //对工作表每一行
{
IRow iRow = sheet.GetRow(row); //row读入第i行数据
if (iRow != null)
{
string rowData = string.Empty;
string dataFormat = string.Empty;
for (int col = 0; col < colNum; col++) //对工作表每一列
{
var obj = iRow.GetCell(col);
string value = obj != null?obj.ToString() : string.Empty;
switch (dataType[col])
{
case "int":
{
if (string.IsNullOrEmpty(value))
{
value = "0";
}
dataFormat = " {0} = {1},\n";
break;
}
case "float":
{
if (string.IsNullOrEmpty(value))
{
value = "0.0";
}
dataFormat = " {0} = {1:0.0000},\n";
break;
}
case "string":
{
if (string.IsNullOrEmpty(value))
{
value = "";
}
dataFormat = " {0} = '{1}',\n";
break;
}
case "table":
{
if (string.IsNullOrEmpty(value))
{
value = "{}";
}
dataFormat = " {0} = {1},\n";
break;
}
}
rowData += String.Format(dataFormat, dataName[col], value);
}
rowDataStr += string.Format(rowKey, dataName[0], iRow.GetCell(0), rowData);
}
}
string luaPath = ConfigConst.ConfigResPath + sheet.SheetName + ".lua";
string luaContent = string.Format(tableData, sheet.SheetName, rowDataStr).Trim();
File.WriteAllText(luaPath, luaContent);
workbook.Close();
}
AssetDatabase.Refresh();
}
public void CreateExcel()
{
IWorkbook workbook = new XSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet(SheetName);
ICellStyle cellStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
var RowIter = 0;
var ColIter = 0;
foreach (KeyValuePair <string, double[]> entry
in dicArrData.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value))
{
// Header Line
var row = sheet.CreateRow(RowIter);
ColIter = 0;
row.CreateCell(ColIter).SetCellValue(entry.Key);
RowIter++;
// Data Lines
row = sheet.CreateRow(RowIter);
ColIter = 0;
foreach (var val in entry.Value)
{
DateTime t = ParseStringToDateTime(dicListDate[entry.Key][ColIter]);
NPOI.SS.UserModel.ICell cell = row.CreateCell(ColIter);
cell.SetCellValue(t);
cell.CellStyle = cellStyle;
ColIter++;
}
RowIter++;
row = sheet.CreateRow(RowIter);
ColIter = 0;
foreach (var val in entry.Value)
{
row.CreateCell(ColIter).SetCellValue(val);
ColIter++;
}
RowIter++;
}
// Save Workbook
FileInfo FI = new FileInfo(FilePath);
FI.Directory.Create(); // If the directory already exists, this method does nothing.
FileStream file = new FileStream(FilePath, FileMode.Create);
workbook.Write(file);
file.Close();
RowIterStartToUse = RowIter;
}
catch
{
throw;
}
finally
{
workbook.Close();
}
}
public void EmailSentToAllEmployee()
{
DateTime d = DateTime.Now;
if (d.Day == 1)
{
SendAttendanceReportToAdminHRManager();
d = d.AddMonths(-1);
int year = d.Year;
int month = d.Month;
string monthName = new DateTime(2000, month, 1).ToString("MMM", CultureInfo.InvariantCulture);
int TotalDays = DateTime.DaysInMonth(year, month);
var rootPath = @"C:\Temp\";
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath);
}
if (!Directory.Exists(rootPath + year + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\");
}
if (!Directory.Exists(rootPath + year + "\\" + monthName + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\" + monthName + "\\");
}
var results = _dbContext.Person.Include(x => x.Location).Include(x => x.Role).Where(x => x.Role.Name != "Admin" && x.Location.IsActive == true)
.Select(p => new
{
p.Id,
p.FullName,
p.EmployeeCode,
p.EmailAddress
}).ToList();
int SrId = 0;
foreach (var p in results)
{
string attendanceReportPath = @"C:\Temp\" + year + "\\" + monthName + "\\" + p.EmployeeCode + "AttendanceReport.xlsx";
if (File.Exists(attendanceReportPath))
{
File.Delete(attendanceReportPath);
}
string InputOne = year.ToString();
char c = '0';
string InputTwo = month.ToString().PadLeft(2, c);
List <EmployeeAttendanceData> data1 = new List <EmployeeAttendanceData>();
List <EmployeeAttendanceData> data = Data(SrId, "Month", p.EmployeeCode, InputOne, InputTwo, data1);
SrId = SrId + data.Count() + 4;
var memory = new MemoryStream();
using (var sw = new FileStream(attendanceReportPath, FileMode.Create, FileAccess.Write))
{
int i = 1;
byte[] rgb1 = new byte[3] {
255, 255, 204
};
byte[] rgb2 = new byte[3] {
214, 245, 214
};
byte[] rgb3 = new byte[3] {
255, 214, 204
};
byte[] rgb4 = new byte[3] {
255, 240, 179
};
IWorkbook workbook;
workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Attendance Report");
IRow row = sheet.CreateRow(0);
XSSFCellStyle headerStyle = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.BorderRight = BorderStyle.Medium;
headerStyle.SetFillForegroundColor(new XSSFColor(rgb1));
headerStyle.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle presentStyle = (XSSFCellStyle)workbook.CreateCellStyle();
presentStyle.BorderBottom = BorderStyle.Medium;
presentStyle.BorderLeft = BorderStyle.Medium;
presentStyle.BorderRight = BorderStyle.Medium;
presentStyle.SetFillForegroundColor(new XSSFColor(rgb2));
presentStyle.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle absentStyle = (XSSFCellStyle)workbook.CreateCellStyle();
absentStyle.BorderBottom = BorderStyle.Medium;
absentStyle.BorderLeft = BorderStyle.Medium;
absentStyle.BorderRight = BorderStyle.Medium;
absentStyle.SetFillForegroundColor(new XSSFColor(rgb3));
absentStyle.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
dateStyle.BorderBottom = BorderStyle.Medium;
dateStyle.BorderLeft = BorderStyle.Medium;
dateStyle.BorderRight = BorderStyle.Medium;
dateStyle.SetFillForegroundColor(new XSSFColor(rgb4));
dateStyle.FillPattern = FillPattern.SolidForeground;
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Employee Name:-");
row.CreateCell(1).SetCellValue(p.FullName);
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Employee Code:-");
row.CreateCell(1).SetCellValue(p.EmployeeCode);
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Monthly Attendance Report:-" + monthName + "/" + year);
row = sheet.CreateRow(i++);
row = sheet.CreateRow(i++);
ICell cell0 = row.CreateCell(0);
cell0.CellStyle = headerStyle;
cell0.SetCellValue("DATE");
ICell cell1 = row.CreateCell(1);
cell1.SetCellValue("STATUS");
cell1.CellStyle = headerStyle;
ICell cell2 = row.CreateCell(2);
cell2.CellStyle = headerStyle;
cell2.SetCellValue("TIME IN");
ICell cell3 = row.CreateCell(3);
cell3.CellStyle = headerStyle;
cell3.SetCellValue("TIME OUT");
ICell cell4 = row.CreateCell(4);
cell4.CellStyle = headerStyle;
cell4.SetCellValue("TOTAL HOURS");
sheet.SetColumnWidth(0, 4000);
sheet.SetColumnWidth(1, 4000);
sheet.SetColumnWidth(2, 4000);
sheet.SetColumnWidth(3, 4000);
sheet.SetColumnWidth(4, 4000);
foreach (var attendance in data)
{
row = sheet.CreateRow(i);
DateTime.Today.Add(attendance.TimeOut.GetValueOrDefault()).ToString("hh:mm tt");
if (attendance.Status == "Present")
{
ICell cell00 = row.CreateCell(0);
cell00.CellStyle = presentStyle;
cell00.SetCellValue(Convert.ToDateTime(attendance.DateIn).ToString("dd/MM/yyyy").ToString());
ICell cell01 = row.CreateCell(1);
cell01.SetCellValue(attendance.Status);
cell01.CellStyle = presentStyle;
ICell cell02 = row.CreateCell(2);
cell02.CellStyle = presentStyle;
cell02.SetCellValue(attendance.TimeIn == null ? "" : DateTime.Today.Add(attendance.TimeIn.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell03 = row.CreateCell(3);
cell03.CellStyle = presentStyle;
cell03.SetCellValue(attendance.TimeOut == null ? "" : DateTime.Today.Add(attendance.TimeOut.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell04 = row.CreateCell(4);
cell04.CellStyle = presentStyle;
cell04.SetCellValue(attendance.TotalHours == null ? "" : attendance.TotalHours.ToString());
}
else
{
ICell cell00 = row.CreateCell(0);
cell00.CellStyle = absentStyle;
cell00.SetCellValue(Convert.ToDateTime(attendance.DateIn).ToString("dd/MM/yyyy").ToString());
ICell cell01 = row.CreateCell(1);
cell01.SetCellValue(attendance.Status);
cell01.CellStyle = absentStyle;
ICell cell02 = row.CreateCell(2);
cell02.CellStyle = absentStyle;
cell02.SetCellValue(attendance.TimeIn == null ? "" : DateTime.Today.Add(attendance.TimeIn.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell03 = row.CreateCell(3);
cell03.CellStyle = absentStyle;
cell03.SetCellValue(attendance.TimeOut == null ? "" : DateTime.Today.Add(attendance.TimeOut.GetValueOrDefault()).ToString("hh:mm tt"));
ICell cell04 = row.CreateCell(4);
cell04.CellStyle = absentStyle;
cell04.SetCellValue(attendance.TotalHours == null ? "" : attendance.TotalHours.ToString());
}
i++;
}
row = sheet.CreateRow(i++);
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("Total No of Days: -");
row.CreateCell(1).SetCellValue(TotalDays.ToString());
row = sheet.CreateRow(i++);
row.CreateCell(0).SetCellValue("No of Days Present:-");
row.CreateCell(1).SetCellValue(data.Where(x => x.Status == "Present").Count().ToString());
workbook.Write(sw);
workbook.Close();
}
data = null;
using (var stream = new FileStream(attendanceReportPath, FileMode.Open))
{
stream.CopyToAsync(memory);
}
memory.Position = 0;
string To = p.EmailAddress;
string subject = "Monthly Attendance Report";
string body = "Dear " + p.FullName + "\n" +
"Kindly find monthly attendance report in attachment.\n" +
"Your Code Number: " + p.EmployeeCode + "\n" +
"User Name: " + p.EmailAddress;
new EmailManager(_configuration, _repository).SendEmail(subject, body, To, attendanceReportPath);
}
}
}
public void TestReferencesToOtherWorkbooks()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("ref2-56737.xlsx");
XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet;
// References to a .xlsx file
IRow rXSLX = s.GetRow(2);
ICell cXSLX_cell = rXSLX.GetCell(4);
ICell cXSLX_sNR = rXSLX.GetCell(6);
ICell cXSLX_gNR = rXSLX.GetCell(8);
Assert.AreEqual("[1]Uses!$A$1", cXSLX_cell.CellFormula);
Assert.AreEqual("[1]Defines!NR_To_A1", cXSLX_sNR.CellFormula);
Assert.AreEqual("[1]!NR_Global_B2", cXSLX_gNR.CellFormula);
Assert.AreEqual("Hello!", cXSLX_cell.StringCellValue);
Assert.AreEqual("Test A1", cXSLX_sNR.StringCellValue);
Assert.AreEqual(142.0, cXSLX_gNR.NumericCellValue, 0);
// References to a .xls file
IRow rXSL = s.GetRow(4);
ICell cXSL_cell = rXSL.GetCell(4);
ICell cXSL_sNR = rXSL.GetCell(6);
ICell cXSL_gNR = rXSL.GetCell(8);
Assert.AreEqual("[2]Uses!$C$1", cXSL_cell.CellFormula);
Assert.AreEqual("[2]Defines!NR_To_A1", cXSL_sNR.CellFormula);
Assert.AreEqual("[2]!NR_Global_B2", cXSL_gNR.CellFormula);
Assert.AreEqual("Hello!", cXSL_cell.StringCellValue);
Assert.AreEqual("Test A1", cXSL_sNR.StringCellValue);
Assert.AreEqual(142.0, cXSL_gNR.NumericCellValue, 0);
// Try to Evaluate without references, won't work
// (At least, not unit we fix bug #56752 that is1)
try
{
evaluator.Evaluate(cXSL_cell);
Assert.Fail("Without a fix for #56752, shouldn't be able to Evaluate a " +
"reference to a non-provided linked workbook");
}
catch (Exception)
{
}
// Setup the environment
Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>();
evaluators.Add("ref2-56737.xlsx", evaluator);
evaluators.Add("56737.xlsx",
_testDataProvider.OpenSampleWorkbook("56737.xlsx").GetCreationHelper().CreateFormulaEvaluator());
evaluators.Add("56737.xls",
HSSFTestDataSamples.OpenSampleWorkbook("56737.xls").GetCreationHelper().CreateFormulaEvaluator());
evaluator.SetupReferencedWorkbooks(evaluators);
// Try Evaluating all of them, ensure we don't blow up
foreach (IRow r in s)
{
foreach (ICell c in r)
{
// TODO Fix and enable
evaluator.Evaluate(c);
}
}
// And evaluate the other way too
evaluator.EvaluateAll();
// Static evaluator won't work, as no references passed in
try
{
XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.Fail("Static method lacks references, shouldn't work");
}
catch (Exception)
{
// expected here
}
// Evaluate specific cells and check results
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSLX_cell).FormatAsString());
Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSLX_sNR).FormatAsString());
//Assert.AreEqual("142.0", evaluator.Evaluate(cXSLX_gNR).FormatAsString());
Assert.AreEqual("142", evaluator.Evaluate(cXSLX_gNR).FormatAsString());
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell).FormatAsString());
Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSL_sNR).FormatAsString());
//Assert.AreEqual("142.0", evaluator.Evaluate(cXSL_gNR).FormatAsString());
Assert.AreEqual("142", evaluator.Evaluate(cXSL_gNR).FormatAsString());
// Add another formula referencing these workbooks
ICell cXSL_cell2 = rXSL.CreateCell(40);
cXSL_cell2.CellFormula = (/*setter*/ "[56737.xls]Uses!$C$1");
// TODO Shouldn't it become [2] like the others?
Assert.AreEqual("[56737.xls]Uses!$C$1", cXSL_cell2.CellFormula);
Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell2).FormatAsString());
// Now add a formula that refers to yet another (different) workbook
// Won't work without the workbook being linked
ICell cXSLX_nw_cell = rXSLX.CreateCell(42);
try
{
cXSLX_nw_cell.CellFormula = (/*setter*/ "[alt.xlsx]Sheet1!$A$1");
Assert.Fail("New workbook not linked, shouldn't be able to Add");
}
catch (Exception) { }
// Link and re-try
IWorkbook alt = new XSSFWorkbook();
try
{
alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook");
// TODO Implement the rest of this, see bug #57184
/*
* wb.linkExternalWorkbook("alt.xlsx", alt);
*
* cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1");
* // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ?
* Assert.AreEqual("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula());
*
* // Evaluate it, without a link to that workbook
* try {
* evaluator.evaluate(cXSLX_nw_cell);
* fail("No cached value and no link to workbook, shouldn't evaluate");
* } catch(Exception e) {}
*
* // Add a link, check it does
* evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator());
* evaluator.setupReferencedWorkbooks(evaluators);
*
* evaluator.evaluate(cXSLX_nw_cell);
* Assert.AreEqual("In another workbook", cXSLX_nw_cell.getStringCellValue());
*/
}
finally
{
alt.Close();
}
wb.Close();
}
/// <summary>
/// 创建多语言总表文件
/// </summary>
public void CreateAutoGenerateLanguageFile()
{
try
{
int nextRow = 0;
List <string> typeList = new List <string>()
{
"int", "#", "string"
};
List <string> nameList = new List <string>()
{
"id", "#", "lang"
};
List <string> flagList = new List <string>()
{
"C", "#", "C"
};
// 创建工作簿
XSSFWorkbook workbook = new XSSFWorkbook();
// 创建Sheet页
ISheet sheet = workbook.CreateSheet($"t_{StrAutoGenerateLanguageExcelName}");
// 创建第一行
IRow firstRow = sheet.CreateRow(nextRow);
for (int i = 0; i < typeList.Count; i++)
{
string value = typeList[i];
ICell cell = firstRow.CreateCell(i);
cell.SetCellValue(value);
}
// 创建第二行
IRow secondRow = sheet.CreateRow(++nextRow);
for (int i = 0; i < nameList.Count; i++)
{
string value = nameList[i];
ICell cell = secondRow.CreateCell(i);
cell.SetCellValue(value);
}
// 创建第三行
IRow thirdRow = sheet.CreateRow(++nextRow);
for (int i = 0; i < flagList.Count; i++)
{
string value = flagList[i];
ICell cell = thirdRow.CreateCell(i);
cell.SetCellValue(value);
}
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //左对齐
// 创建数据行
foreach (var pair in _cacheLanguage)
{
int hashCode = pair.Key;
LanguageWrapper wrapper = pair.Value;
IRow row = sheet.CreateRow(++nextRow);
int cellNum = 0;
// id
ICell cell1 = row.CreateCell(cellNum);
cell1.SetCellValue(hashCode);
cell1.CellStyle = style;
// source
ICell cell2 = row.CreateCell(++cellNum);
cell2.SetCellValue(wrapper.Source);
cell2.CellStyle = style;
// language
ICell cell3 = row.CreateCell(++cellNum);
cell3.SetCellValue(wrapper.Content);
cell3.CellStyle = style;
}
// 设置格式
sheet.SetColumnWidth(0, 15 * 256); //设置列宽为30个字符
sheet.SetColumnWidth(1, 30 * 256); //设置列宽为30个字符
sheet.SetColumnWidth(2, 30 * 256); //设置列宽为30个字符
// 保存Excel文件
string filePath = Path.Combine(ExportConfig.Instance.ExcelPath, StrAutoGenerateLanguageExcelName + ".xlsx");
using (FileStream file = new FileStream(filePath, FileMode.Create))
{
workbook.Write(file);
file.Close();
}
// 销毁句柄
workbook.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 把DataTable的数据写入到指定的excel文件中
/// </summary>
/// <param name="path">目标文件excel的路径</param>
/// <param name="dt">要写入的数据</param>
/// <param name="sheetName">excel表中的sheet的名称，可以根据情况自己起</param>
/// <param name="IsWriteColumnName">是否写入DataTable的列名称</param>
/// <returns>返回写入的行数</returns>
public static int DataTableToExcel(string path, DataTable dt, string sheetName, bool IsWriteColumnName)
{
//数据验证
if (!File.Exists(path))
{
//excel文件的路径不存在
throw new ArgumentException("excel文件的路径不存在或者excel文件没有创建好");
}
if (dt == null)
{
throw new ArgumentException("要写入的DataTable不能为空");
}
if (sheetName == null && sheetName.Length == 0)
{
throw new ArgumentException("excel中的sheet名称不能为空或者不能为空字符串");
}
////根据Excel文件的后缀名创建对应的workbook
IWorkbook workbook = null;
//if (path.IndexOf(".xlsx") > 0)
//{ //2007版本的excel
// workbook = new XSSFWorkbook();
//}
//else if (path.IndexOf(".xls") > 0) //2003版本的excel
//{
// workbook = new HSSFWorkbook();
//}
//else
//{
// return -1; //都不匹配或者传入的文件根本就不是excel文件，直接返回
//}
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(file);
}
//excel表的sheet名
//ISheet sheet = workbook.CreateSheet(sheetName);
ISheet sheet = workbook.GetSheet(sheetName);
if (sheet == null)
{
return(-1); //无法创建sheet，则直接返回
}
//eg: java code; http://poi.apache.org/components/spreadsheet/quick-guide.html#Validation
//XSSFName name = workbook.createName();
//name.setNameName("data");
//name.setRefersToFormula("'Data Validation'!$B$1:$F$1");
//XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
//XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper.createFormulaListConstraint("data");
//CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
//XSSFDataValidation validation = (XSSFDataValidation)dvHelper.createValidation(dvConstraint, addressList);
//validation.setSuppressDropDownArrow(true);
//validation.setShowErrorBox(true);
//sheet.addValidationData(validation);
//P1,P2,P3,P4 数据验证
//sheet.AddConstraint(workbook, "Severity_Level", "P1,P2,P3,P4", 3, true);
////
//sheet.AddConstraint(workbook, "Feedback_Source", "电话,邮箱,微信,QQ,SKYPE,Ticket System", 4, true);
////
//sheet.AddConstraint(workbook, "Category", "$AC$6:$AK$6", 7);
//sheet.AddConstraint(workbook, "Sub_Category", "INDIRECT(H3)", 8);
//IName namedRangeSub = workbook.CreateName();
//namedRange.NameName = "sub";
//namedRange.RefersToFormula = "INDIRECT(E4)";
//XSSFDataValidationConstraint dvConstraintSub = (XSSFDataValidationConstraint)dvHelper.CreateFormulaListConstraint("sub");
//CellRangeAddressList addressListSub = new CellRangeAddressList(1, 5, 5, 5);
//XSSFDataValidation validationSub = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraintSub, addressListSub);
//validation.SuppressDropDownArrow = true;
//validation.ShowErrorBox = true;
//sheet.AddValidationData(validationSub);
ICellStyle cs_Title = workbook.CreateCellStyle();
cs_Title.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //水平居中
cs_Title.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //垂直居中
cs_Title.WrapText = true; //自动换行
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow newRow = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
var data = (dt.Rows[i][j].ToString());
if (data.Length > 32767)//excel cell length limit
{
data = data.Substring(0, 32000);
}
newRow.CreateCell(j).SetCellValue(data);
newRow.Cells[j].CellStyle = cs_Title;
}
Console.WriteLine(i + "/" + dt.Rows.Count);
}
//FileStream fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
//用这种 上面方式 excel有可能打不开
FileStream fs = File.Create(path);
workbook.Write(fs);
fs.Close();
workbook.Close();
return(1);
}
private bool checkExcelStucture(string pathFile)
{
// Lấy stream file
var fs = new FileStream(pathFile, FileMode.Open);
// Khởi tạo workbook để đọc
var wb = new XSSFWorkbook(fs);
// Lấy sheet đầu tiên
var sheet = wb.GetSheetAt(0);
#region Kiểm tra dòng 1 header
var row = sheet.GetRow(9);
// AD10: Mã Shop
if (row.GetCell(29).StringCellValue != "Mã Shop")
{
return(false);
}
// AE10: Mã Hệ Thống
if (row.GetCell(30).StringCellValue != "Mã Hệ Thống")
{
return(false);
}
// D10: Ngày Tạo ĐH
if (row.GetCell(3).StringCellValue != "Ngày Tạo ĐH")
{
return(false);
}
// E10: Ngày Hoàn Thành
if (row.GetCell(4).StringCellValue != "Ngày Hoàn Thành")
{
return(false);
}
// W10: Thu Hộ
if (row.GetCell(22).StringCellValue != "Thu Hộ")
{
return(false);
}
// U10: Tổng Cộng
if (row.GetCell(20).StringCellValue != "Tổng Cộng")
{
return(false);
}
// AA10: Trạng thái
if (row.GetCell(26).StringCellValue != "Trạng thái đơn hàng")
{
return(false);
}
#endregion
#region Kiểm tra dòng 2 header
row = sheet.GetRow(10);
// F11: Họ tên
if (row.GetCell(5).StringCellValue != "Họ Tên")
{
return(false);
}
// G11: SĐT
if (row.GetCell(6).StringCellValue != "SĐT")
{
return(false);
}
// H11: Địa chỉ
if (row.GetCell(7).StringCellValue != "Địa Chỉ")
{
return(false);
}
#endregion
wb.Close();
fs.Close();
return(true);
}
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;
XSSFTextParagraph para = shape.AddNewTextParagraph();
para.AddNewTextRun().Text = ("Line 1");
List <XSSFTextRun> Runs = para.TextRuns;
Assert.AreEqual(1, Runs.Count);
XSSFTextRun run = Runs[0];
Assert.AreEqual("Line 1", run.Text);
//Assert.IsNotNull(run.ParentParagraph);
//Assert.IsNotNull(run.XmlObject);
Assert.IsNotNull(run.GetRPr());
Assert.AreEqual(Color.FromArgb(0, 0, 0), run.FontColor);
Color color = Color.FromArgb(0, 255, 255);
run.FontColor = (/*setter*/ color);
Assert.AreEqual(color, run.FontColor);
Assert.AreEqual(11.0, run.FontSize, 0.01);
run.FontSize = (/*setter*/ 12.32);
Assert.AreEqual(12.32, run.FontSize, 0.01);
run.FontSize = (/*setter*/ -1.0);
Assert.AreEqual(11.0, run.FontSize, 0.01);
run.FontSize = (/*setter*/ -1.0);
Assert.AreEqual(11.0, run.FontSize, 0.01);
try
{
run.FontSize = (/*setter*/ 0.9);
Assert.Fail("Should fail");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.Contains("0.9"));
}
Assert.AreEqual(11.0, run.FontSize, 0.01);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 12.31);
Assert.AreEqual(12.31, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 0.0);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
run.CharacterSpacing = (/*setter*/ 0.0);
Assert.AreEqual(0.0, run.CharacterSpacing, 0.01);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, (byte)1, false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", unchecked ((byte)-1), (byte)1, false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, unchecked ((byte)-1), false);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily("Arial", (byte)1, (byte)1, true);
Assert.AreEqual("Arial", run.FontFamily);
run.SetFontFamily(null, (byte)1, (byte)1, false);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFontFamily(null, (byte)1, (byte)1, false);
Assert.AreEqual("Calibri", run.FontFamily);
run.SetFont("Arial");
Assert.AreEqual("Arial", run.FontFamily);
Assert.AreEqual((byte)0, run.PitchAndFamily);
run.SetFont(null);
Assert.AreEqual((byte)0, run.PitchAndFamily);
Assert.IsFalse(run.IsStrikethrough);
run.IsStrikethrough = (/*setter*/ true);
Assert.IsTrue(run.IsStrikethrough);
run.IsStrikethrough = (/*setter*/ false);
Assert.IsFalse(run.IsStrikethrough);
Assert.IsFalse(run.IsSuperscript);
run.IsSuperscript = (/*setter*/ true);
Assert.IsTrue(run.IsSuperscript);
run.IsSuperscript = (/*setter*/ false);
Assert.IsFalse(run.IsSuperscript);
Assert.IsFalse(run.IsSubscript);
run.IsSubscript = (/*setter*/ true);
Assert.IsTrue(run.IsSubscript);
run.IsSubscript = (/*setter*/ false);
Assert.IsFalse(run.IsSubscript);
Assert.AreEqual(TextCap.NONE, run.TextCap);
Assert.IsFalse(run.IsBold);
run.IsBold = (/*setter*/ true);
Assert.IsTrue(run.IsBold);
run.IsBold = (/*setter*/ false);
Assert.IsFalse(run.IsBold);
Assert.IsFalse(run.IsItalic);
run.IsItalic = (/*setter*/ true);
Assert.IsTrue(run.IsItalic);
run.IsItalic = (/*setter*/ false);
Assert.IsFalse(run.IsItalic);
Assert.IsFalse(run.IsUnderline);
run.IsUnderline = (/*setter*/ true);
Assert.IsTrue(run.IsUnderline);
run.IsUnderline = (/*setter*/ false);
Assert.IsFalse(run.IsUnderline);
Assert.IsNotNull(run.ToString());
}
finally
{
wb.Close();
}
}
private List <T> CreateExel(List <T> list, ISheet sheet = null, HSSFWorkbook hssfWorkbook = null, XSSFWorkbook xssWorkbook = null)
{
IRow columnRow = sheet.GetRow(0); // 第一行为字段名
Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>();
for (int j = 0; j < columnRow.LastCellNum; j++)
{
ICell cell = columnRow.GetCell(j);
PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString());
if (propertyInfo != null)
{
mapPropertyInfoDict.Add(j, propertyInfo);
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
T entity = new T();
for (int j = row.FirstCellNum; j <= columnRow.LastCellNum; j++)
{
if (mapPropertyInfoDict.ContainsKey(j))
{
if (row.GetCell(j) != null)
{
PropertyInfo propertyInfo = mapPropertyInfoDict[j];
switch (propertyInfo.PropertyType.ToString())
{
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime());
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int16":
case "System.Nullable`1[System.Int16]":
mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int32":
case "System.Nullable`1[System.Int32]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal());
break;
default:
case "System.String":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString());
break;
}
}
}
}
list.Add(entity);
}
hssfWorkbook?.Close();
xssWorkbook?.Close();
return(list);
}
public static void writeExcel(List <Product> products)
{
IWorkbook wb = new XSSFWorkbook();
ISheet ws = wb.CreateSheet("Products");
ws.SetColumnWidth(0, 6000);
IRow header = ws.CreateRow(0);
header.CreateCell(0).SetCellValue("Name");
header.CreateCell(1).SetCellValue("Price");
header.CreateCell(2).SetCellValue("Old Price");
header.CreateCell(3).SetCellValue("Difference");
header.CreateCell(4).SetCellValue("Category");
header.CreateCell(5).SetCellValue("Picture");
header.CreateCell(6).SetCellValue("ID");
header.CreateCell(7).SetCellValue("URL");
int rowcount = 1;
foreach (Product product in products)
{
IRow ProductRow = ws.CreateRow(rowcount);
ProductRow.CreateCell(0).SetCellValue(product.name);
ProductRow.CreateCell(1).SetCellValue(product.price);
ProductRow.CreateCell(2).SetCellValue(product.xprice);
ProductRow.CreateCell(3).SetCellValue(product.dif);
ProductRow.CreateCell(4).SetCellValue(product.category);
ProductRow.CreateCell(5);
ProductRow.CreateCell(6).SetCellValue(product.ID);
ProductRow.CreateCell(7).SetCellValue(product.URL);
ProductRow.Height = 1500;
if (File.Exists(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png"))
{
byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png");
int pictureIndex = wb.AddPicture(data, PictureType.PNG);
IDrawing patriarch = ws.CreateDrawingPatriarch();
IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor();
anchor.Col1 = 5;
anchor.Row1 = rowcount;
anchor.AnchorType = AnchorType.MoveAndResize;
IPicture picture = patriarch.CreatePicture(anchor, pictureIndex);
picture.Resize(1);
//byte[] data = File.ReadAllBytes(@"C:\Users\email\Desktop\Hardware Hub\images\" + product.ID + ".png");
//int pictureIndex = wb.AddPicture(data, PictureType.PNG);
//ICreationHelper helper = wb.GetCreationHelper();
//IDrawing drawing = ws.CreateDrawingPatriarch();
//IClientAnchor anchor = helper.CreateClientAnchor();
//anchor.Col1 = 5;//0 index based column
//anchor.Row1 = ProductRow.RowNum;//0 index based row
//IPicture picture = drawing.CreatePicture(anchor, pictureIndex);
//picture.Resize();
}
rowcount++;
}
IRow row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("Stop");
rowcount++;
row = ws.CreateRow(rowcount);
rowcount++;
row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue("Skipped products");
rowcount++;
foreach (String url in Program.skippedURLs)
{
row = ws.CreateRow(rowcount);
row.CreateCell(0).SetCellValue(url);
rowcount++;
}
Stream stream = new FileStream(ProductsExcelPath, FileMode.Create);
//Stream stream = new FileStream(@"C:\Users\email\Desktop\Hardware Hub\products.xlsx", FileMode.Open);
wb.Write(stream);
wb.Close();
stream.Close();
}
public void TestReadTextBoxParagraphs()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("WithTextBox.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
//the sheet has one relationship and it is XSSFDrawing
List <POIXMLDocumentPart.RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
POIXMLDocumentPart.RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
//sheet.CreateDrawingPatriarch() should return the same instance of XSSFDrawing
Assert.AreSame(drawing, sheet.CreateDrawingPatriarch());
String drawingId = rp.Relationship.Id;
//there should be a relation to this Drawing in the worksheet
Assert.IsTrue(sheet.GetCTWorksheet().IsSetDrawing());
Assert.AreEqual(drawingId, sheet.GetCTWorksheet().drawing.id);
List <XSSFShape> shapes = drawing.GetShapes();
Assert.AreEqual(1, shapes.Count);
Assert.IsTrue(shapes[0] is XSSFSimpleShape);
XSSFSimpleShape textbox = (XSSFSimpleShape)shapes[0];
List <XSSFTextParagraph> paras = textbox.TextParagraphs;
Assert.AreEqual(3, paras.Count);
Assert.AreEqual("Line 2", paras[1].Text); // check content of second paragraph
Assert.AreEqual("Line 1\nLine 2\nLine 3", textbox.Text); // check content of entire textbox
// check attributes of paragraphs
Assert.AreEqual(TextAlign.LEFT, paras[0].TextAlign);
Assert.AreEqual(TextAlign.CENTER, paras[1].TextAlign);
Assert.AreEqual(TextAlign.RIGHT, paras[2].TextAlign);
Color clr = paras[0].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 255, 0, 0 },
new int[] { clr.R, clr.G, clr.B }));
clr = paras[1].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 255, 0 },
new int[] { clr.R, clr.G, clr.B }));
clr = paras[2].TextRuns[0].FontColor;
Assert.IsTrue(Arrays.Equals(
new int[] { 0, 0, 255 },
new int[] { clr.R, clr.G, clr.B }));
checkRewrite(wb);
wb.Close();
}
/// <summary>
/// Crea un archivo excel acuerdo a los filtros especificados
/// </summary>
/// <param name="cab"></param>
/// <param name="det"></param>
/// <param name="filterParam"></param>
/// <returns></returns>
public string getDescargarConsulta(HistorialCargaArchivo_LinCab cab, NOMINA nomina, HistorialCargaArchivo_LinDet det, object[] filterParam)
{
var helperStyle = new Helpers.excelStyle();
try
{
var nombreArchivo = "Archivo " + filterParam[0].ToString() + " " + DateTime.Now.ToString("yyyyMMdd");
var rutaTemporal = @HttpContext.Current.Server.MapPath("~/Temp/Descargas/" + nombreArchivo + ".xlsx");
int total;
var tipoLinea = filterParam[0].ToString() == "NOMINA" ? "*" : "D";
//new Utils.DeleteFile().deleteFile(HttpContext.Current.Server.MapPath(@"~/Utils/xlsxs/"));
XSSFWorkbook book = new XSSFWorkbook();
var contratoSis = new nContratoSis().listContratoByID(new CONTRATO_SYS()
{
IDE_CONTRATO = cab.IDE_CONTRATO
});
var reglaArchivo = new ReglaArchivo()
{
Archivo = filterParam[0].ToString(), TipoLinea = tipoLinea, NUM_CONT_LIC = Convert.ToInt32(contratoSis.NRO_CONTRATO), vigente = 1
};
var listReglaArchivo = new nReglaArchivo().getListReglaArchivo(reglaArchivo, 0, 1000, "IdReglaArchivo ASC", out total);
if (reglaArchivo.Archivo.Equals("0"))
{
listReglaArchivo = listReglaArchivo.GroupBy(x => new { x.NombreCampo, x.TituloColumna })
.Select(y => new ReglaArchivo()
{
NombreCampo = y.Key.NombreCampo, TituloColumna = y.Key.TituloColumna
}).ToList();
}
//crear el libro
var sheet = book.CreateSheet(nombreArchivo);
var rowCabecera = sheet.CreateRow(1);
var headerStyle = helperStyle.setFontText(12, true, book);
var bodyStyle = helperStyle.setFontText(11, false, book);
//construir cabecera
ICell cellCabecera;
for (int i = 0; i < listReglaArchivo.Count; i++)
{
cellCabecera = rowCabecera.CreateCell(i + 1);
cellCabecera.SetCellValue(listReglaArchivo[i].TituloColumna);
cellCabecera.CellStyle = headerStyle;
}
//consultar datos segun los filtros especificados
if (filterParam[0].ToString() == "NOMINA")
{
var listNomina = new nNomina().listNominaConsulta(nomina, filterParam, 0, 100000, out total);
for (int i = 0; i < listNomina.Count; i++)
{
IRow rowBody = sheet.CreateRow(i + 2);
ICell cellBody;
for (int c = 0; c < listReglaArchivo.Count; c++)
{
cellBody = rowBody.CreateCell(c + 1);
var property = listNomina[i].GetType().GetProperty(listReglaArchivo[c].NombreCampo.ToString().Trim(), BindingFlags.Public | BindingFlags.Instance);
cellBody.SetCellValue(property.GetValue(listNomina[i], null) == null ? "" : property.GetValue(listNomina[i], null).ToString());
cellBody.CellStyle = bodyStyle;
}
}
}
else
{
var listHistoriaLinDet = new dPagoCargado().listArchivoCargado(cab, det, filterParam, 0, 100000, "TipoLinea ASC", out total);
for (int i = 0; i < listHistoriaLinDet.Count; i++)
{
IRow rowBody = sheet.CreateRow(i + 2);
ICell cellBody;
for (int c = 0; c < listReglaArchivo.Count; c++)
{
cellBody = rowBody.CreateCell(c + 1);
var property = listHistoriaLinDet[i].GetType().GetProperty(listReglaArchivo[c].NombreCampo.ToString().Trim(), BindingFlags.Public | BindingFlags.Instance);
cellBody.SetCellValue(property.GetValue(listHistoriaLinDet[i], null) == null ? "" : property.GetValue(listHistoriaLinDet[i], null).ToString());
cellBody.CellStyle = bodyStyle;
}
}
}
if (File.Exists(rutaTemporal))
{
File.Delete(rutaTemporal);
}
//guardar el archivo creado en memoria
using (var file = new FileStream(rutaTemporal, FileMode.Create, FileAccess.ReadWrite))
{
book.Write(file);
file.Close();
book.Close();
}
return(rutaTemporal);
}
catch (System.Exception ex)
{
throw;
}
}
public void TestNew()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb1.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.RelationPart> rels = sheet.RelationParts;
Assert.AreEqual(1, rels.Count);
POIXMLDocumentPart.RelationPart rp = rels[0];
Assert.IsTrue(rp.DocumentPart is XSSFDrawing);
XSSFDrawing drawing = (XSSFDrawing)rp.DocumentPart;
String drawingId = rp.Relationship.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 = 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, wb1.CreateFont());
rt.ApplyFont(5, 6, wb1.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
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
wb1.Close();
sheet = wb2.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\""));
checkRewrite(wb2);
wb2.Close();
}
public void ExportXls()
{
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter += "Excel file (*.xlsx)|.xlsx | all file (*.*)|*.*";
DialogResult result = dialog.ShowDialog();
if (result != DialogResult.OK)
{
return;
}
FileStream fs = new FileStream(dialog.FileName, FileMode.Create);
XSSFWorkbook workBook = new XSSFWorkbook();
workBook.CreateSheet("Sheet1");
XSSFSheet sheet = (XSSFSheet)workBook.GetSheet("Sheet1");
#region fontStyle
ICellStyle style = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
#endregion
sheet.CreateRow(0);
XSSFRow row = (XSSFRow)sheet.GetRow(0);
row.Height = 17 * 20;
XSSFCell cell = (XSSFCell)row.CreateCell(0);
cell.CellStyle = style;
int count = 1;
foreach (int markingName in this.m_MinimalCriticalMarkingNames)
{
cell = (XSSFCell)row.CreateCell(count++);
cell.CellStyle = style;
cell.SetCellValue(markingName);
}
foreach (int operatorMarking in this.m_Reachability.GetOperationPlaceNames())
{
cell = (XSSFCell)row.CreateCell(count++);
cell.CellStyle = style;
cell.SetCellValue("l" + operatorMarking.ToString());
}
cell = (XSSFCell)row.CreateCell(count++);
cell.CellStyle = style;
cell.SetCellValue("Belta");
foreach (int t in this.m_Reachability.CriticalTransitions)
{
cell = (XSSFCell)row.CreateCell(count++);
cell.CellStyle = style;
cell.SetCellValue("Omega" + t.ToString());
}
int rowCount = 1;
foreach (int markingName in this.m_MinimalCriticalMarkingNames)
{
row = (XSSFRow)sheet.CreateRow(rowCount++);
row.Height = 17 * 20;
cell = (XSSFCell)row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(markingName);
for (int i = 1; i < count; i++)
{
cell = (XSSFCell)row.CreateCell(i);
cell.CellStyle = style;
}
}
workBook.Write(fs);
fs.Close();
workBook.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 SendAttendanceReportToAdminHRManager()
{
var year = DateTime.Now.ToString("yyyy");
DateTime d = DateTime.Now;
d = d.AddMonths(-1);
int month = d.Month;
string monthName = new DateTime(2000, month, 1).ToString("MMM", CultureInfo.InvariantCulture);
var rootPath = @"C:\Temp\AttendanceReportToAdmin\";
if (!Directory.Exists(rootPath))
{
Directory.CreateDirectory(rootPath);
}
if (!Directory.Exists(rootPath + year + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\");
}
if (!Directory.Exists(rootPath + year + "\\" + monthName + "\\"))
{
Directory.CreateDirectory(rootPath + year + "\\" + monthName + "\\");
}
int locationId = 0;
var SearchFor = "Month";
char c = '0';
var InputOne = DateTime.Now.AddMonths(-1).Month.ToString().PadLeft(2, c);
var InputTwo = DateTime.Now.Year.ToString();
bool status = true;
IList <AttendanceData> attendanceData = new List <AttendanceData>();
Attendance_Report Model = new Attendance_Report();
Model.sP_GetAttendanceCountReports = new List <SP_GetAttendanceCountReport>();
var SP_locationId = new SqlParameter("@locationId", locationId);
var SP_SelectType = new SqlParameter("@SelectType", SearchFor);
var SP_InputOne = new SqlParameter("@InputOne", InputOne);
var SP_InputTwo = new SqlParameter("@InputTwo", InputTwo);
var SP_Status = new SqlParameter("@Status", status);
string usp = "LMS.usp_GetAttendanceCountReport @locationId, @SelectType, @InputOne, @InputTwo,@Status";
Model.sP_GetAttendanceCountReports = _dbContext._sp_GetAttendanceCountReport.FromSql(usp, SP_locationId, SP_SelectType, SP_InputOne, SP_InputTwo, SP_Status).ToList();
string attendanceReportPath = @"C:\Temp\AttendanceReportToAdmin\" + year + "\\" + monthName + "\\" + "AttendanceReport.xlsx";
if (File.Exists(attendanceReportPath))
{
File.Delete(attendanceReportPath);
}
var memory = new MemoryStream();
using (var sw = new FileStream(attendanceReportPath, FileMode.Create, FileAccess.Write))
{
int i = 1;
IWorkbook workbook;
ICell cell;
workbook = new XSSFWorkbook();
ICellStyle headerStyle = workbook.CreateCellStyle();
ISheet sheet = workbook.CreateSheet("Attendance Report");
IRow row = sheet.CreateRow(0);
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Aadyam Consultant llp.");
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Employee Management System");
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
cell.SetCellValue("Attendance Report:-" + monthName + "/" + year);
row = sheet.CreateRow(i++);
row = sheet.CreateRow(i++);
cell = row.CreateCell(0);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Employee Code");
cell = row.CreateCell(1);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Employee Name");
cell = row.CreateCell(2);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Working Days");
cell = row.CreateCell(3);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Leave Without Approval");
cell = row.CreateCell(4);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Leave With Approval");
cell = row.CreateCell(5);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Total Leaves");
cell = row.CreateCell(6);
headerStyle.BorderBottom = BorderStyle.Medium;
headerStyle.BorderTop = BorderStyle.Medium;
headerStyle.BorderLeft = BorderStyle.Medium;
headerStyle.BorderRight = BorderStyle.Medium;
headerStyle.FillForegroundColor = IndexedColors.LightYellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
cell.CellStyle = headerStyle;
cell.SetCellValue("Present Days");
byte[] rgb1 = new byte[3] {
242, 255, 204
};
byte[] rgb2 = new byte[3] {
230, 255, 204
};
byte[] rgb3 = new byte[3] {
230, 255, 255
};
byte[] rgb4 = new byte[3] {
255, 214, 204
};
byte[] rgb5 = new byte[3] {
214, 245, 214
};
byte[] rgb6 = new byte[3] {
255, 238, 204
};
byte[] rgb7 = new byte[3] {
217, 255, 179
};
XSSFCellStyle headerStyle1 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle1.BorderBottom = BorderStyle.Medium;
headerStyle1.BorderLeft = BorderStyle.Medium;
headerStyle1.SetFillForegroundColor(new XSSFColor(rgb1));
headerStyle1.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle2 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle2.BorderBottom = BorderStyle.Medium;
headerStyle2.BorderLeft = BorderStyle.Medium;
headerStyle2.SetFillForegroundColor(new XSSFColor(rgb2));
headerStyle2.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle3 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle3.BorderBottom = BorderStyle.Medium;
headerStyle3.BorderLeft = BorderStyle.Medium;
headerStyle3.SetVerticalAlignment(3);
headerStyle3.SetFillForegroundColor(new XSSFColor(rgb3));
headerStyle3.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle4 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle4.BorderBottom = BorderStyle.Medium;
headerStyle4.BorderLeft = BorderStyle.Medium;
headerStyle4.SetVerticalAlignment(3);
headerStyle4.SetFillForegroundColor(new XSSFColor(rgb4));
headerStyle4.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle5 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle5.BorderBottom = BorderStyle.Medium;
headerStyle5.BorderLeft = BorderStyle.Medium;
headerStyle5.SetFillForegroundColor(new XSSFColor(rgb5));
headerStyle5.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle6 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle6.BorderBottom = BorderStyle.Medium;
headerStyle6.BorderLeft = BorderStyle.Medium;
headerStyle6.SetFillForegroundColor(new XSSFColor(rgb6));
headerStyle6.FillPattern = FillPattern.SolidForeground;
XSSFCellStyle headerStyle7 = (XSSFCellStyle)workbook.CreateCellStyle();
headerStyle7.BorderBottom = BorderStyle.Medium;
headerStyle7.BorderLeft = BorderStyle.Medium;
headerStyle7.BorderRight = BorderStyle.Medium;
headerStyle7.SetFillForegroundColor(new XSSFColor(rgb7));
headerStyle7.FillPattern = FillPattern.SolidForeground;
sheet.SetColumnWidth(0, 4000);
sheet.SetColumnWidth(1, 7000);
sheet.SetColumnWidth(2, 4000);
sheet.SetColumnWidth(3, 5600);
sheet.SetColumnWidth(4, 5000);
sheet.SetColumnWidth(5, 3000);
sheet.SetColumnWidth(6, 3200);
foreach (var attendance in Model.sP_GetAttendanceCountReports)
{
row = sheet.CreateRow(i);
cell = row.CreateCell(0);
cell.CellStyle = headerStyle1;
cell.SetCellValue(attendance.EmployeeCode);
cell = row.CreateCell(1);
cell.CellStyle = headerStyle2;
cell.SetCellValue(attendance.EmployeeName);
cell = row.CreateCell(2);
cell.CellStyle = headerStyle3;
cell.SetCellValue(Convert.ToInt32(attendance.WorkingDay));
cell = row.CreateCell(3);
cell.CellStyle = headerStyle4;
cell.SetCellValue(Convert.ToInt32(attendance.AbsentDay));
cell = row.CreateCell(4);
cell.CellStyle = headerStyle5;
cell.SetCellValue(Convert.ToInt32(attendance.NoLeave));
cell = row.CreateCell(5);
cell.CellStyle = headerStyle6;
cell.SetCellValue(Convert.ToInt32(attendance.NoLeave) + Convert.ToInt32(attendance.AbsentDay));
cell = row.CreateCell(6);
cell.CellStyle = headerStyle7;
cell.SetCellValue(Convert.ToInt32(attendance.PresentDays));
i++;
}
workbook.Write(sw);
workbook.Close();
}
var persons = _repository.Employee.getAdminHrManager()
.Select(p => new
{
EmailAddress = p.EmailAddress
}).ToList();
foreach (var p in persons)
{
string To = p.EmailAddress;
string subject = "EMS Monthly Attendance Report";
string body = "Dear Sir/Maa'm \n" +
"Kindly find monthly attendance report in attachment";
new EmailManager(_configuration, _repository).SendEmail(subject, body, To, attendanceReportPath);
}
}
/// <summary>
/// Excel导入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public List <T> ImportFromExcel(string filePath)
{
string absoluteFilePath = GlobalContext.HostingEnvironment.ContentRootPath + filePath.Replace(Path.AltDirectorySeparatorChar, Path.DirectorySeparatorChar);
List <T> list = new List <T>();
HSSFWorkbook hssfWorkbook = null;
XSSFWorkbook xssWorkbook = null;
ISheet sheet = null;
using (FileStream file = new FileStream(absoluteFilePath, FileMode.Open, FileAccess.Read))
{
switch (Path.GetExtension(filePath))
{
case ".xls":
hssfWorkbook = new HSSFWorkbook(file);
sheet = hssfWorkbook.GetSheetAt(0);
break;
case ".xlsx":
xssWorkbook = new XSSFWorkbook(file);
sheet = xssWorkbook.GetSheetAt(0);
break;
default:
throw new Exception("不支持的文件格式");
}
}
IRow columnRow = sheet.GetRow(1); // 第二行为字段名
Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>();
for (int j = 0; j < columnRow.LastCellNum; j++)
{
ICell cell = columnRow.GetCell(j);
PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString());
if (propertyInfo != null)
{
mapPropertyInfoDict.Add(j, propertyInfo);
}
}
for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
T entity = new T();
for (int j = row.FirstCellNum; j < columnRow.LastCellNum; j++)
{
if (mapPropertyInfoDict.ContainsKey(j))
{
if (row.GetCell(j) != null)
{
PropertyInfo propertyInfo = mapPropertyInfoDict[j];
switch (propertyInfo.PropertyType.ToString())
{
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime());
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int16":
case "System.Nullable`1[System.Int16]":
mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int32":
case "System.Nullable`1[System.Int32]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal());
break;
default:
case "System.String":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString());
break;
}
}
}
}
list.Add(entity);
}
hssfWorkbook?.Close();
xssWorkbook?.Close();
return(list);
}
public void SaveLoadNew()
{
XSSFWorkbook wb1 = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = wb1.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = wb1.CreateSheet("sheet1");
ISheet sheet2 = wb1.CreateSheet("sheet2");
wb1.CreateSheet("sheet3");
IRichTextString rts = wb1.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, wb1.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, wb1.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, wb1.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.FullName);
wb1.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
wb1.Close();
// Load back the XSSFWorkbook
XSSFWorkbook wb2 = new XSSFWorkbook(pkg);
Assert.AreEqual(3, wb2.NumberOfSheets);
Assert.IsNotNull(wb2.GetSheetAt(0));
Assert.IsNotNull(wb2.GetSheetAt(1));
Assert.IsNotNull(wb2.GetSheetAt(2));
Assert.IsNotNull(wb2.GetSharedStringSource());
Assert.IsNotNull(wb2.GetStylesSource());
Assert.AreEqual(0, wb2.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, wb2.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, wb2.GetSheetAt(2).LastRowNum);
sheet1 = wb2.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
pkg.Close();
Assert.AreEqual(0, Directory.GetFiles(AppDomain.CurrentDomain.BaseDirectory, "*.tmp").Length, "At Last: There are no temporary files.");
}
