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