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."); }