public void TestAutoSizeColumn() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("43902.xls"); String sheetName = "my sheet"; HSSFSheet sheet = (HSSFSheet)wb.GetSheet(sheetName); // Can't use literal numbers for column sizes, as // will come out with different values on different // machines based on the fonts available. // So, we use ranges, which are pretty large, but // thankfully don't overlap! int minWithRow1And2 = 6400; int maxWithRow1And2 = 7800; int minWithRow1Only = 2750; int maxWithRow1Only = 3300; // autoSize the first column and check its size before the merged region (1,0,1,1) is set: // it has to be based on the 2nd row width sheet.AutoSizeColumn(0); Assert.IsTrue(sheet.GetColumnWidth(0) >= minWithRow1And2, "Column autosized with only one row: wrong width"); Assert.IsTrue(sheet.GetColumnWidth(0) <= maxWithRow1And2, "Column autosized with only one row: wrong width"); //Create a region over the 2nd row and auto size the first column sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 1)); sheet.AutoSizeColumn(0); HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb); // Check that the autoSized column width has ignored the 2nd row // because it is included in a merged region (Excel like behavior) NPOI.SS.UserModel.ISheet sheet2 = wb2.GetSheet(sheetName); Assert.IsTrue(sheet2.GetColumnWidth(0) >= minWithRow1Only); Assert.IsTrue(sheet2.GetColumnWidth(0) <= maxWithRow1Only); // Remove the 2nd row merged region and Check that the 2nd row value is used to the AutoSizeColumn width sheet2.RemoveMergedRegion(1); sheet2.AutoSizeColumn(0); HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2); NPOI.SS.UserModel.ISheet sheet3 = wb3.GetSheet(sheetName); Assert.IsTrue(sheet3.GetColumnWidth(0) >= minWithRow1And2); Assert.IsTrue(sheet3.GetColumnWidth(0) <= maxWithRow1And2); }
public static void ExportCommonGridtoExcel(System.Windows.Forms.DataGridView grid, string sheetName, out string fileName) { fileName = ""; if (grid == null || grid.DataSource == null || grid.Rows.Count == 0 || grid.Columns.Count == 0) { return; } System.Windows.Forms.SaveFileDialog sfDialog = new System.Windows.Forms.SaveFileDialog(); sfDialog.Filter = "Excel files(*.xls)|*.xls"; sfDialog.FilterIndex = 1; sfDialog.DefaultExt = ".xls"; //sfDialog.InitialDirectory = Environment.CurrentDirectory; sfDialog.RestoreDirectory = true; if (sfDialog.ShowDialog() != System.Windows.Forms.DialogResult.OK) { return; } try { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(sheetName) ? "Sheet1" : sheetName); int colCount = 0; NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { NPOI.SS.UserModel.ICell cell = headerRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); cell.SetCellValue(grid.Columns[i].HeaderText); } } int rowCount = 1; for (int i = 0; i < grid.Rows.Count; i++) { colCount = 0; if (grid.Rows[i].Visible) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowCount++); for (int j = 0; j < grid.Columns.Count; j++) { if (grid.Columns[j].Visible) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); if (grid.Rows[i].Cells[j] != null && grid.Rows[i].Cells[j].Value != null) { cell.SetCellValue(grid.Rows[i].Cells[j].Value.ToString()); } else { cell.SetCellValue(string.Empty); } } } } } for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { sheet.AutoSizeColumn(i); } } workbook.Write(ms); FileStream file = new FileStream(sfDialog.FileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); fileName = sfDialog.FileName; } catch (Exception) { throw; } }
private string GenerateExcelReport(int report, string savePath) { /// excel writer row index int rowIndex = 0; try { NPOI.XSSF.UserModel.XSSFWorkbook workbook = new XSSFWorkbook(); NPOI.SS.UserModel.ISheet wkst = workbook.CreateSheet(); workbook.SetActiveSheet(0); // sets up basic styles for Excel NPOI.SS.UserModel.ICellStyle HeaderCellStyle = workbook.CreateCellStyle(); HeaderCellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeaderCellStyle.VerticalAlignment = VerticalAlignment.Center; HeaderCellStyle.Alignment = HorizontalAlignment.Center; HeaderCellStyle.FillPattern = FillPattern.SolidForeground; HeaderCellStyle.FillForegroundColor = IndexedColors.LightCornflowerBlue.Index; IFont headerFont = workbook.CreateFont(); headerFont.Boldweight = (short)FontBoldWeight.Bold; HeaderCellStyle.SetFont(headerFont); NPOI.SS.UserModel.ICellStyle DataCellStyle = workbook.CreateCellStyle(); DataCellStyle.VerticalAlignment = VerticalAlignment.Top; DataCellStyle.WrapText = true; ArrayList query_for_status = new ArrayList(); string query_columns = string.Empty, ignore_columns = "id,cohort_id,status_timestamp,published", column_headers = string.Empty; switch (report) { case 0: query_columns = "id,cohort_id,cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp"; query_for_status.Add("pending"); query_for_status.Add("rejected"); query_for_status.Add("inprogress"); ignore_columns = "id,cohort_id"; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Received,CEDCD Form Reviewed,Date CEDCD Form Published"; break; case 1: query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status,status_timestamp"; query_for_status.Add("pending"); query_for_status.Add("rejected"); query_for_status.Add("inprogress"); ignore_columns = string.Empty; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Current Status,Current Status Date"; break; case 2: query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp"; query_for_status.Add("published"); ignore_columns = string.Empty; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Published"; break; case 3: query_columns = "cohort_acronym,cohort_name,pi_name_1,pi_institution_1,pi_email_1,status_timestamp [Date CEDCD Form was Unpublished]"; query_for_status.Add("unpublished"); ignore_columns = string.Empty; column_headers = "Cohort Abbreviation,Cohort,Cohort PI,Institution,Cohort Contact Email,Date CEDCD Form Unpublished"; break; case 4: query_columns = "*"; query_for_status.Add("published"); query_for_status.Add("pending"); break; default: query_columns = "*"; query_for_status.Add("published"); break; } DataTable dt_records = CECWebSrv.GetCohortsWithStatusesWithColumns(UserToken, (string[])query_for_status.ToArray(typeof(string)), query_columns); /// write header to excel /// NPOI.SS.UserModel.IRow dataRow = wkst.CreateRow(rowIndex++); //dataRow.CreateCell(0).SetCellValue(String.Format("Cohort Data Export Generated from the CEDCD Website ({0})", Request.Url.Authority)); //dataRow = wkst.CreateRow(rowIndex++); //dataRow.CreateCell(0).SetCellValue("Export Date:"); //dataRow.CreateCell(1).SetCellValue(DateTime.Now.ToString("MM/dd/yyyy")); //rowIndex += 2; int colPos = 0; ///-------------------------------------------------------- /// column headers /// NPOI.SS.UserModel.IRow headerRow = wkst.CreateRow(rowIndex++); if (column_headers != string.Empty) { foreach (string s in column_headers.Split(',')) { ICell c = headerRow.GetCell(colPos++, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(s); c.CellStyle = HeaderCellStyle; } } else { using (CECHarmPublicService ps = new CECHarmPublicService()) { for (int _c = 0; _c < dt_records.Columns.Count; _c++) { if (ignore_columns.Contains(dt_records.Columns[_c].ColumnName)) { continue; } ICell c = headerRow.GetCell(colPos); if (c == null) { c = headerRow.CreateCell(colPos); } string column_name = ps.GetCohortWebFieldLabelByColumnName(UserToken, dt_records.Columns[_c].ColumnName); if (String.IsNullOrWhiteSpace(column_name)) { column_name = dt_records.Columns[_c].ColumnName; } c.SetCellValue(column_name); c.CellStyle = HeaderCellStyle; colPos++; } } } if (report != 0 && dt_records.Rows.Count >= 1) { /// data rows for (int _i = 0; _i < dt_records.Rows.Count; _i++) { colPos = 0; /// create data row object then step through each cell to populate the excel row dataRow = wkst.CreateRow(rowIndex++); for (int _p = 0; _p < dt_records.Columns.Count; _p++) { if (ignore_columns.Contains(dt_records.Columns[_p].ColumnName)) { continue; } /// get first cell and check for null, if null create cell ICell c = dataRow.GetCell(colPos); if (c == null) { c = dataRow.CreateCell(colPos); } string cellVal = dt_records.Rows[_i][_p].ToString(); if (helper.IsStringEmptyWhiteSpace(cellVal) || cellVal == " " || cellVal == "-1") { cellVal = "N/P"; c.SetCellValue(cellVal); } else if (dt_records.Columns[_p].DataType == typeof(DateTime)) { c.SetCellValue(DateTime.Parse(cellVal).ToString("MM/dd/yyyy")); } else if (dt_records.Columns[_p].ColumnName == "status") { switch (cellVal) { case "inprogress": c.SetCellValue("Draft In Progress"); break; case "pending": c.SetCellValue("Under NCI Review"); break; case "rejected": c.SetCellValue("Returned to Cohort"); break; default: c.SetCellValue(cellVal); break; } } else { c.SetCellValue(cellVal); } c.CellStyle = DataCellStyle; colPos++; } } } else if (report == 0 && dt_records.Rows.Count >= 1) { foreach (DataRow dr in dt_records.Rows) { dataRow = wkst.CreateRow(rowIndex++); // cohort acronym ICell c = dataRow.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["cohort_acronym"].ToString()); c.CellStyle = DataCellStyle; // cohort name c = dataRow.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["cohort_name"].ToString()); c.CellStyle = DataCellStyle; // pi name c = dataRow.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["pi_name_1"].ToString()); c.CellStyle = DataCellStyle; // pi institution c = dataRow.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["pi_institution_1"].ToString()); c.CellStyle = DataCellStyle; // pi email c = dataRow.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(dr["pi_email_1"].ToString()); c.CellStyle = DataCellStyle; // the more complicated stuff... if (dr["status"].ToString() == "pending") { c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy")); c.CellStyle = DataCellStyle; c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue("No"); c.CellStyle = DataCellStyle; using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false)) { if (dt_temp.Rows.Count > 0) { c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy")); c.CellStyle = DataCellStyle; } } } else if (dr["status"].ToString() == "rejected" || dr["status"].ToString() == "inprogress") { c = dataRow.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK); if (dr["status"].ToString() == "rejected") { using (DataTable dt_temp = (new CECHarmPublicService()).AuditLog_GetActivities(UserToken, "submitted", (int)dr["id"])) { if (dt_temp.Rows.Count == 0) { c.SetCellValue(" "); } else { c.SetCellValue(((DateTime)dt_temp.Rows[0]["create_date"]).ToString("MM/dd/yyyy")); } } } else { c.SetCellValue(((DateTime)dr["status_timestamp"]).ToString("MM/dd/yyyy")); } c.CellStyle = DataCellStyle; c = dataRow.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue("Pending Revisions"); c.CellStyle = DataCellStyle; using (DataTable dt_temp = CECWebSrv.GetCohortRecordById(UserToken, (int)dr["cohort_id"], false)) { if (dt_temp.Rows.Count > 0) { c = dataRow.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(((DateTime)dt_temp.Rows[0]["status_timestamp"]).ToString("MM/dd/yyyy")); c.CellStyle = DataCellStyle; } } } } } else { ICell c = wkst.CreateRow(rowIndex++).GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); c.SetCellValue(String.Format("Nothing to report")); } for (int _ic = 0; _ic <= headerRow.PhysicalNumberOfCells; _ic++) { wkst.AutoSizeColumn(_ic); } /// write output FileStream fs = new FileStream(savePath, FileMode.Create); workbook.Write(fs); fs.Close(); return(savePath); } catch (Exception ex) { throw ex; } }
public static void ExportCommonGridtoExcel(System.Windows.Forms.DataGridView grid, string fileName) { if (grid == null || grid.DataSource == null || grid.Rows.Count == 0 || grid.Columns.Count == 0 || string.IsNullOrEmpty(fileName)) { return; } try { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1"); int colCount = 0; NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { NPOI.SS.UserModel.ICell cell = headerRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); cell.SetCellValue(grid.Columns[i].HeaderText); } } int rowCount = 1; for (int i = 0; i < grid.Rows.Count; i++) { colCount = 0; if (grid.Rows[i].Visible) { NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowCount++); for (int j = 0; j < grid.Columns.Count; j++) { if (grid.Columns[j].Visible) { NPOI.SS.UserModel.ICell cell = dataRow.CreateCell(colCount++, NPOI.SS.UserModel.CellType.String); if (grid.Rows[i].Cells[j] != null && grid.Rows[i].Cells[j].Value != null) { cell.SetCellValue(grid.Rows[i].Cells[j].Value.ToString()); } else { cell.SetCellValue(string.Empty); } } } } } for (int i = 0; i < grid.Columns.Count; i++) { if (grid.Columns[i].Visible) { sheet.AutoSizeColumn(i); } } workbook.Write(ms); FileStream file = new FileStream(fileName, FileMode.Create); workbook.Write(file); file.Close(); workbook = null; ms.Close(); ms.Dispose(); } catch (Exception) { throw; } }
public string DownLoadExcel() { var data = new List <Users>(); //List<CounterMan> data = Newtonsoft.Json.JsonConvert.DeserializeObject<List<CounterMan>>(jsonData); //2、创建Excel文件的对象 NPOI.HSSF.UserModel.HSSFWorkbook excel = new NPOI.HSSF.UserModel.HSSFWorkbook(); //添加一个sheet NPOI.SS.UserModel.ISheet sheet = excel.CreateSheet("Sheet1"); //给sheet1添加标题行 NPOI.SS.UserModel.IRow head = sheet.CreateRow(0); head.CreateCell(0).SetCellValue("姓名"); head.CreateCell(1).SetCellValue("手机号"); head.CreateCell(2).SetCellValue("角色"); head.CreateCell(3).SetCellValue("所属区县"); head.CreateCell(4).SetCellValue("所属网格"); head.CreateCell(5).SetCellValue("岗位"); ICellStyle HeadercellStyle = excel.CreateCellStyle(); HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //字体 NPOI.SS.UserModel.IFont headerfont = excel.CreateFont(); headerfont.Boldweight = (short)FontBoldWeight.Bold; HeadercellStyle.SetFont(headerfont); //将数据逐步写入sheet1各个行 for (int i = 0; i < data.Count; i++) { NPOI.SS.UserModel.IRow row = sheet.CreateRow(i + 1); row.CreateCell(0).SetCellValue(data[i].Name); row.CreateCell(1).SetCellValue(data[i].Mobile); row.CreateCell(2).SetCellValue(data[i].Roles.RoleName); row.CreateCell(3).SetCellValue(data[i].Areas); row.CreateCell(4).SetCellValue(data[i].Grids); row.CreateCell(5).SetCellValue(data[i].Post); sheet.AutoSizeColumn(i);//自适应行 } // 写入到客户端 System.IO.MemoryStream ms = new System.IO.MemoryStream(); excel.Write(ms); //ms.Seek(0, SeekOrigin.Begin); ms.Flush(); var fileName = "Excel_" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls"; //將生成的文件保存到服務器的臨時目錄里 var path = HttpContext.Current.Server.MapPath("~/UpFiles/"); string fullPath = Path.Combine(path, fileName); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } using (FileStream fs = new FileStream(fullPath, FileMode.OpenOrCreate, FileAccess.Write)) { byte[] data1 = ms.ToArray(); fs.Write(data1, 0, data1.Length); fs.Flush(); data1 = null; } var errorMessage = "you can return the errors in here!"; //返回生成的文件名 //return Newtonsoft.Json.JsonConvert.SerializeObject(new { fileName = fileName, errorMessage = errorMessage }); return(fileName); }
public void Export(Stream ms, DataTable dt, string postfix) { //var fileName = m_Handler.GetFileName(); NPOI.SS.UserModel.IWorkbook book = null; if (postfix == ".xls") { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else if (postfix == ".xlsx") { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } else { throw new FinanceException(FinanceResult.INCORRECT_STATE, "无效的文件名"); } m_Handler.Encode(ref dt); //WriteExcel(ref book, dt); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Sheet1"); // 添加表头 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); ICellStyle cellStyle = book.CreateCellStyle(); var font = book.CreateFont(); font.FontHeightInPoints = 14; font.IsBold = true; cellStyle.SetFont(font); int index = 0; //string caption = m_Handler.GetCaption(); //if (string.IsNullOrEmpty(caption)) //{ foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.CellStyle = cellStyle; cell.SetCellValue(item.Caption); index++; } //} //else //{ // NPOI.SS.UserModel.ICell cell = row.CreateCell(index); // cell.SetCellType(NPOI.SS.UserModel.CellType.String); // cell.CellStyle = cellStyle; // cell.SetCellValue(caption); //} // 添加数据 for (int i = 0; i < dt.Rows.Count; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (DataColumn item in dt.Columns) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(dt.Rows[i][item].ToString()); index++; } } for (int i = 0; i < dt.Columns.Count; i++) { sheet.AutoSizeColumn(i); } //// 写入 //MemoryStream ms = new MemoryStream(); book.Write(ms); book = null; //using (FileStream fs = new FileStream("E:\\Temp\\test.xls", FileMode.Create, FileAccess.Write)) //{ // byte[] data = ms.ToArray(); // fs.Write(data, 0, data.Length); // fs.Flush(); //} //ms.Close(); //ms.Dispose(); }
/// <summary> /// 输出数据表格 /// </summary> /// <param name="workbook">工作文档</param> /// <param name="normalStyle">普通样式(用于表格内容)</param> /// <param name="boldStyle">粗体样式(用于表格头部)</param> /// <param name="table">表格数据</param> public static void writeSheet(NPOI.XSSF.UserModel.XSSFWorkbook workbook, NPOI.SS.UserModel.ICellStyle normalStyle, NPOI.SS.UserModel.ICellStyle boldStyle, DataTable table) { //创建Sheet页 NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(table.TableName); //行号 int rowIndex = 0; //是否需要输出表头 bool isNeedCreateHeader = true; //输出数据到Excel foreach (DataRow rowData in table.Rows) { //忽略空数据行 if (rowData.ItemArray == null || rowData.ItemArray.Length != table.Columns.Count) { continue; } //列号 int colIndex = 0; //Excel行 NPOI.SS.UserModel.IRow row = null; //是否需要输入表头 if (isNeedCreateHeader) { isNeedCreateHeader = false; //创建行 row = sheet.CreateRow(rowIndex); //输出列名到Excel colIndex = 0; foreach (DataColumn kvp in table.Columns) { //列名 //创建列 NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex); //设置样式 cell.CellStyle = boldStyle; //设置数据 cell.SetCellValue(kvp.ColumnName); colIndex++; } rowIndex++; } //创建行 row = sheet.CreateRow(rowIndex); //输出列值到Excel colIndex = 0; foreach (object val in rowData.ItemArray) { //列值 //创建列 NPOI.SS.UserModel.ICell cell = row.CreateCell(colIndex); //设置样式 cell.CellStyle = normalStyle; //设置数据 //判断是否为空 if (val != null) { //不为空 //判断是否为RTF内容 if (val.GetType().Name.Equals(typeof(NPOI.XSSF.UserModel.XSSFRichTextString).Name)) { //RTF内容 cell.SetCellValue((NPOI.XSSF.UserModel.XSSFRichTextString)val); } else { //文本内容 cell.SetCellValue(val.ToString()); } } else { //为空 cell.SetCellValue(string.Empty); } colIndex++; } rowIndex++; } //Excel列宽自动适应 if (table.Rows.Count >= 1 && sheet.GetRow(0) != null) { for (int k = 0; k < sheet.GetRow(0).Cells.Count; k++) { sheet.AutoSizeColumn(k); } } }
//FIXME перенести в отдельные методы public static MemoryStream LoadFromDatabase() { SqlCommand cmd; var con = new SqlConnection(ConfigurationManager.ConnectionStrings["dipl"].ConnectionString); con.Open(); //TODO: Проблема: не отображаются остатки если у нового товара есть приход, но нет расхода string query = @"SELECT prod.name, prod.id_kontr, (Select(Select SUM(count) From operation as operPrih JOIN TTN as ttnPrih ON operPrih.id_ttn = ttnPrih.id Where id_product = prod.id and ttnPrih.id_type = 7) - case when (Select SUM(count) From operation as operRash JOIN TTN as ttnRash ON operRash.id_ttn = ttnRash.id Where id_product = prod.id and ttnRash.id_type = 8) is null then 0 else (Select SUM(count) From operation as operRash JOIN TTN as ttnRash ON operRash.id_ttn = ttnRash.id Where id_product = prod.id and ttnRash.id_type = 8 ) end ) as остатки FROM dbo.operation as oper Join dbo.products as prod ON prod.id = oper.id_product JOIN dbo.TTN ttn ON ttn.id = oper.id_ttn join dbo.kontragents as kontr on ttn.id_kontr = kontr.id Where kontr.type_kontr_id = 2 group by prod.id, prod.name, prod.id_kontr"; cmd = new SqlCommand(query, con); //помещаем данные из SQL-запроса в переменную reader var reader = cmd.ExecuteReader(); HSSFWorkbook workbook = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("таблица"); IRow headerRow = sheet.CreateRow(0); string[] columnsHeader = { "Поставщик", "Наименование товара", "Остатки" }; #region стиль для первой строки(цвет) ICellStyle boldStyle = workbook.CreateCellStyle(); boldStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LemonChiffon.Index; boldStyle.BorderBottom = BorderStyle.Medium; boldStyle.FillPattern = FillPattern.SolidForeground; #endregion //заполняем первую строку(Заголовки) for (int i = 0; i < columnsHeader.Length; i++) { headerRow.CreateCell(i).SetCellValue(columnsHeader[i]); headerRow.Cells[i].CellStyle = boldStyle; sheet.AutoSizeColumn(i); } #region поля(границы) для ячеек ICellStyle styleBorder = workbook.CreateCellStyle(); styleBorder.BorderRight = BorderStyle.Thin; styleBorder.BorderLeft = BorderStyle.Thin; styleBorder.BorderTop = BorderStyle.Thin; styleBorder.BorderBottom = BorderStyle.Thin; #endregion int rowIndex = 1; string name; while (reader.Read()) { IRow row = workbook.GetSheet("таблица").CreateRow(rowIndex); for (int i = 0; i < reader.FieldCount; i++) { if (i == 1) { int id_kontr = (int)reader[i]; name = db.kontragents.Where(x => x.id == id_kontr).Select(x => x.name).FirstOrDefault(); row.CreateCell(i).SetCellValue(name); } else { row.CreateCell(i).SetCellValue(reader[i].ToString()); } row.Cells[i].CellStyle = styleBorder; } rowIndex++; } MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Position = 0; ms.Flush(); con.Close(); return(ms); }
private void CreateExcelFile(string FileName) { //create try { NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("Result"); NPOI.SS.UserModel.IRow row0 = sheet.CreateRow(0); int i = 0; if (lcfg == null) { row0.CreateCell(i++).SetCellValue("日期:"); row0.CreateCell(i++).SetCellValue("仪器名称:"); row0.CreateCell(i++).SetCellValue("仪器编号:"); row0.CreateCell(i++).SetCellValue("实验室名称:"); row0.CreateCell(i++).SetCellValue("油样名称:"); row0.CreateCell(i++).SetCellValue("油样号:"); row0.CreateCell(i++).SetCellValue("实验员:"); row0.CreateCell(i++).SetCellValue("分析方法:"); row0.CreateCell(i++).SetCellValue("一号弹:测试时间:"); row0.CreateCell(i++).SetCellValue("最大压力:"); row0.CreateCell(i++).SetCellValue("二号弹:测试时间:"); row0.CreateCell(i++).SetCellValue("最大压力:"); row0.CreateCell(i++).SetCellValue("三号弹:测试时间:"); row0.CreateCell(i++).SetCellValue("最大压力:"); row0.CreateCell(i++).SetCellValue("四号弹:测试时间:"); row0.CreateCell(i++).SetCellValue("最大压力:"); } else { row0.CreateCell(i++).SetCellValue("日期:"); row0.CreateCell(i++).SetCellValue("仪器名称:"); row0.CreateCell(i++).SetCellValue("仪器编号:"); row0.CreateCell(i++).SetCellValue("实验室名称:"); row0.CreateCell(i++).SetCellValue("油样名称:"); row0.CreateCell(i++).SetCellValue("油样号:"); row0.CreateCell(i++).SetCellValue("实验员:"); row0.CreateCell(i++).SetCellValue("分析方法:"); row0.CreateCell(i++).SetCellValue(lcfg.label_1_out); row0.CreateCell(i++).SetCellValue(lcfg.label_2_out); row0.CreateCell(i++).SetCellValue(lcfg.label_3_out); row0.CreateCell(i++).SetCellValue(lcfg.label_4_out); row0.CreateCell(i++).SetCellValue(lcfg.label_5_out); row0.CreateCell(i++).SetCellValue(lcfg.label_6_out); row0.CreateCell(i++).SetCellValue(lcfg.label_7_out); row0.CreateCell(i++).SetCellValue(lcfg.label_8_out); } NPOI.SS.UserModel.IRow row1 = sheet.CreateRow(1); i = 0; row1.CreateCell(i++).SetCellValue(DateTime.Now.ToString()); row1.CreateCell(i++).SetCellValue(textBoxDevName.Text); row1.CreateCell(i++).SetCellValue(textBoxDevID.Text); row1.CreateCell(i++).SetCellValue(textBoxLabName.Text); row1.CreateCell(i++).SetCellValue(textBoxOilName.Text); row1.CreateCell(i++).SetCellValue(textBoxOilID.Text); row1.CreateCell(i++).SetCellValue(textBoxUserName.Text); row1.CreateCell(i++).SetCellValue(textBoxAnalysis.Text); row1.CreateCell(i++).SetCellValue(regTextBox1.Text); row1.CreateCell(i++).SetCellValue(regTextBox2.Text); row1.CreateCell(i++).SetCellValue(regTextBox3.Text); row1.CreateCell(i++).SetCellValue(regTextBox4.Text); row1.CreateCell(i++).SetCellValue(regTextBox5.Text); row1.CreateCell(i++).SetCellValue(regTextBox6.Text); row1.CreateCell(i++).SetCellValue(regTextBox7.Text); row1.CreateCell(i++).SetCellValue(regTextBox8.Text); for (int j = 0; j < i; j++) { sheet.AutoSizeColumn(j); } //worksheet.Cells[1, 1] = "仪器名称:"; //worksheet.Cells[2, 1] = "仪器编号:"; //worksheet.Cells[3, 1] = "实验室名称:"; //worksheet.Cells[4, 1] = "油样名称:"; //worksheet.Cells[5, 1] = "油样号:"; //worksheet.Cells[6, 1] = "实验员:"; //worksheet.Cells[7, 1] = "分析方法:"; //worksheet.Cells[8, 1] = "日期:"; //worksheet.Cells[1, 2] = textBoxDevName.Text; //worksheet.Cells[2, 2] = textBoxDevID.Text; //worksheet.Cells[3, 2] = textBoxLabName.Text; //worksheet.Cells[4, 2] = textBoxOilName.Text; //worksheet.Cells[5, 2] = textBoxOilID.Text; //worksheet.Cells[6, 2] = textBoxUserName.Text; //worksheet.Cells[7, 2] = textBoxAnalysis.Text; //worksheet.Cells[8, 2] = DateTime.Now.ToString(); //worksheet.Cells[9, 1] = "一号弹:"; //worksheet.Cells[10, 1] = "二号弹:"; //worksheet.Cells[11, 1] = "三号弹:"; //worksheet.Cells[12, 1] = "四号弹:"; //worksheet.Cells[9, 2] = "测试时间:"; //worksheet.Cells[10, 2] = "测试时间:"; //worksheet.Cells[11, 2] = "测试时间:"; //worksheet.Cells[12, 2] = "测试时间:"; //worksheet.Cells[9, 3] = regTextBox1.Text; //worksheet.Cells[10, 3] = regTextBox3.Text; //worksheet.Cells[11, 3] = regTextBox5.Text; //worksheet.Cells[12, 3] = regTextBox7.Text; //worksheet.Cells[9, 4] = "最大压力:"; //worksheet.Cells[10, 4] = "最大压力:"; //worksheet.Cells[11, 4] = "最大压力:"; //worksheet.Cells[12, 4] = "最大压力:"; //worksheet.Cells[9, 5] = regTextBox2.Text; //worksheet.Cells[10, 5] = regTextBox4.Text; //worksheet.Cells[11, 5] = regTextBox6.Text; //worksheet.Cells[12, 5] = regTextBox8.Text; //((Excel.Range)worksheet.Columns["A:E", System.Type.Missing]).AutoFit(); //worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); //workBook.Close(false, Type.Missing, Type.Missing); //app.Quit(); using (System.IO.MemoryStream ms = new System.IO.MemoryStream()) { book.Write(ms); using (FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } book = null; } } catch (Exception ex) { LogClass.GetInstance().WriteExceptionLog(ex); //MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }