public static List <Cnf05> ParseFromExcelNpoi(Stream stream, int excelVersion, string user)
{
List <Cnf05> cnf05List = new List <Cnf05>();
IWorkbook workbook = (excelVersion == 2003 ? (IWorkbook) new HSSFWorkbook(stream) : new XSSFWorkbook(stream));
ISheet sheet = workbook.GetSheetAt(0);
int startRowNumber = 1; // skip title
int endRowNumber = sheet.LastRowNum;
for (int i = startRowNumber; i <= endRowNumber; i++)
{
IRow excelRow = sheet.GetRow(i);
int startColumn = 0;
int endColumn = excelRow.LastCellNum;
Cnf05 cnf05 = new Cnf05();
for (int j = startColumn; j <= endColumn; j++)
{
ICell excelCell = excelRow.GetCell(j);
var data = excelCell == null ? null : excelCell.ToString();
if (j == 0)
{
cnf05.cnf0501_file = data;
}
else if (j == 1)
{
cnf05.cnf0502_field = data;
}
else if (j == 2)
{
cnf05.cnf0503_fieldname_tw = data;
}
else if (j == 3)
{
cnf05.cnf0504_fieldname_cn = data;
}
else if (j == 4)
{
cnf05.cnf0505_fieldname_en = data;
}
else if (j == 5)
{
cnf05.cnf0506_program = data;
}
else if (j == 6)
{
cnf05.remark = data;
}
}
cnf05.adddate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
cnf05.adduser = user;
cnf05List.Add(cnf05);
}
return(cnf05List);
}
public static List <Cnf05> ParseFromExcel(Stream stream, string user)
{
List <Cnf05> cnf05List = new List <Cnf05>();
using (ExcelPackage ep = new ExcelPackage(stream))
{
ExcelWorksheet sheet = ep.Workbook.Worksheets[1];
int startRowNumber = sheet.Dimension.Start.Row + 1; // skip title
int endRowNumber = sheet.Dimension.End.Row;
int startColumn = sheet.Dimension.Start.Column;
int endColumn = sheet.Dimension.End.Column;
for (int i = startRowNumber; i <= endRowNumber; i++)
{
Cnf05 cnf05 = new Cnf05();
for (int j = startColumn; j <= endColumn; j++)
{
var data = sheet.Cells[i, j].Text;
if (j == 1)
{
cnf05.cnf0501_file = data;
}
else if (j == 2)
{
cnf05.cnf0502_field = data;
}
else if (j == 3)
{
cnf05.cnf0503_fieldname_tw = data;
}
else if (j == 4)
{
cnf05.cnf0504_fieldname_cn = data;
}
else if (j == 5)
{
cnf05.cnf0505_fieldname_en = data;
}
else if (j == 6)
{
cnf05.cnf0506_program = data;
}
else if (j == 7)
{
cnf05.remark = data;
}
}
cnf05.adddate = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));
cnf05.adduser = user;
cnf05List.Add(cnf05);
}
}
return(cnf05List);
}
public static Cnf05 UpdateItem(Cnf05 cnf05)
{
if (cnf05 == null)
{
throw new ArgumentNullException("cnf05");
}
using (var conn = new SqlConnection {
ConnectionString = MyConnStringList.AzureGoodeasy
})
using (var sqlCmd = conn.CreateCommand())
{
conn.Open();
sqlCmd.CommandText = @"
UPDATE [dbo].[cnf05]
SET [cnf0501_file] = @cnf0501_file
,[cnf0502_field] = @cnf0502_field
,[cnf0503_fieldname_tw] = @cnf0503_fieldname_tw
,[cnf0504_fieldname_cn] = @cnf0504_fieldname_cn
,[cnf0505_fieldname_en] = @cnf0505_fieldname_en
,[cnf0506_program] = @cnf0506_program
,[remark] = @remark
,[moduser] = @moduser
,[moddate] = @moddate
WHERE id=@id
";
sqlCmd.Parameters.AddWithValue("@id", cnf05.id);
sqlCmd.Parameters.AddWithValue("@cnf0501_file", cnf05.cnf0501_file);
sqlCmd.Parameters.AddWithValue("@cnf0502_field", cnf05.cnf0502_field);
sqlCmd.Parameters.AddWithValue("@cnf0503_fieldname_tw", cnf05.cnf0503_fieldname_tw);
sqlCmd.Parameters.AddWithValue("@cnf0504_fieldname_cn", cnf05.cnf0504_fieldname_cn);
sqlCmd.Parameters.AddWithValue("@cnf0505_fieldname_en", cnf05.cnf0505_fieldname_en);
sqlCmd.Parameters.AddWithValue("@cnf0506_program", cnf05.cnf0506_program);
sqlCmd.Parameters.AddWithValueSafe("@remark", cnf05.remark);
sqlCmd.Parameters.AddWithValue("@moduser", cnf05.moduser);
sqlCmd.Parameters.AddWithValueSafe("@moddate", cnf05.moddate);
var count = sqlCmd.ExecuteNonQuery();
if (count < 1)
{
return(null);
}
}
return(cnf05);
}
public static IWorkbook ExportNpoi(string[] fieldName, List <Cnf05> cnf05List, int excelVersion)
{
IWorkbook workbook = null;
if (excelVersion == 2003)
{
workbook = new HSSFWorkbook();
}
else
{
workbook = new XSSFWorkbook();
}
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow headRow = sheet.CreateRow(0);
for (var i = 0; i < fieldName.Length; i++)
{
if (String.Equals("cnf0501_file", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("檔案代號");
}
else if (String.Equals("cnf0502_field", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("欄位名稱");
}
else if (String.Equals("cnf0503_fieldname_tw", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("中文說明-繁體");
}
else if (String.Equals("cnf0506_program", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("程式代號");
}
else if (String.Equals("adddate", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("新增日期");
}
else if (String.Equals("adduser", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("新增者");
}
else if (String.Equals("moddate", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("修改日期");
}
else if (String.Equals("moduser", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("修改者");
}
else if (String.Equals("cnf0504_fieldname_cn", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("中文說明-簡體");
}
else if (String.Equals("cnf0505_fieldname_en", fieldName[i]))
{
headRow.CreateCell(i).SetCellValue("英文說明");
}
}
for (int i = 0; i < cnf05List.Count; i++)
{
IRow excelRow = sheet.CreateRow(i + 1);
Cnf05 cnf05Item = cnf05List[i];
for (var j = 0; j < fieldName.Length; j++)
{
var val = Cnf05.GetPropValue(cnf05Item, fieldName[j]);
var excelCell = excelRow.CreateCell(j);
excelCell.SetCellValue(val != null ? val.ToString() : "");
if (val is DateTime)
{
excelCell.SetCellValue(((DateTime)val).ToString("yyyy/MM/dd"));
}
}
}
for (var i = 0; i < fieldName.Length; i++)
{
sheet.AutoSizeColumn(i);
}
return(workbook);
}
public static ExcelPackage Export(string[] fieldName, List <Cnf05> cnf05List)
{
var excel = new ExcelPackage();
var sheet = excel.Workbook.Worksheets.Add("Sheet1");
var title = 1;
for (var i = 0; i < fieldName.Length; i++)
{
if (String.Equals("cnf0501_file", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "檔案代號";
}
else if (String.Equals("cnf0502_field", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "欄位名稱";
}
else if (String.Equals("cnf0503_fieldname_tw", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "中文說明-繁體";
}
else if (String.Equals("cnf0506_program", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "程式代號";
}
else if (String.Equals("adddate", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "新增日期";
}
else if (String.Equals("adduser", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "新增者";
}
else if (String.Equals("moddate", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "修改日期";
}
else if (String.Equals("moduser", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "修改者";
}
else if (String.Equals("cnf0504_fieldname_cn", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "中文說明-簡體";
}
else if (String.Equals("cnf0505_fieldname_en", fieldName[i]))
{
sheet.Cells[title, i + 1].Value = "英文說明";
}
}
for (int i = 0; i < cnf05List.Count; i++)
{
Cnf05 cnf05Item = cnf05List[i];
for (var j = 0; j < fieldName.Length; j++)
{
sheet.Cells[i + 1 + title, j + 1].Value = Cnf05.GetPropValue(cnf05Item, fieldName[j]);
if (sheet.Cells[i + 1 + title, j + 1].Value is DateTime)
{
sheet.Cells[i + 1 + title, j + 1].Style.Numberformat.Format = "yyyy/MM/dd";
}
}
}
FitExcelColumnWidth(sheet);
return(excel);
}
public static List <Cnf05> Search(FilterOption filterOption)
{
List <Cnf05> cnf05List = new List <Cnf05>();
using (var conn = new SqlConnection {
ConnectionString = MyConnStringList.AzureGoodeasy
})
using (var sqlCmd = conn.CreateCommand())
{
conn.Open();
var keywordFilter = "";
var cnf0501FileFilter = "";
var cnf0506ProgramFilter = "";
var addDateFilter = "";
if (filterOption != null)
{
if (!String.IsNullOrEmpty(filterOption.keyword))
{
keywordFilter = String.Format(@" AND (
cnf0501_file like @Keyword
OR cnf0502_field like @Keyword
OR cnf0503_fieldname_tw like @Keyword
OR cnf0504_fieldname_cn like @Keyword
OR cnf0505_fieldname_en like @Keyword
OR cnf0506_program like @Keyword
OR remark like @Keyword
OR adduser like @Keyword
OR adddate like @Keyword
OR moduser like @Keyword
OR moddate like @Keyword
)");
sqlCmd.Parameters.AddWithValue("@Keyword", "%" + filterOption.keyword + "%");
}
if (!String.IsNullOrEmpty(filterOption.cnf0501_file_start))
{
cnf0501FileFilter = String.Format(@" AND (
[cnf0501_file] >= @cnf0501FileStart
AND [cnf0501_file] <= @cnf0501FileEnd
)");
sqlCmd.Parameters.AddWithValue("@cnf0501FileStart", filterOption.cnf0501_file_start);
sqlCmd.Parameters.AddWithValue("@cnf0501FileEnd",
String.IsNullOrEmpty(filterOption.cnf0501_file_end)
? filterOption.cnf0501_file_start
: filterOption.cnf0501_file_end);
}
if (!String.IsNullOrEmpty(filterOption.cnf0506_program_start))
{
cnf0506ProgramFilter = String.Format(@" AND (
[cnf0506_program] >= @cnf0506ProgramStart
AND [cnf0506_program] <= @cnf0506ProgramEnd
)");
sqlCmd.Parameters.AddWithValue("@cnf0506ProgramStart", filterOption.cnf0506_program_start);
sqlCmd.Parameters.AddWithValue("@cnf0506ProgramEnd",
String.IsNullOrEmpty(filterOption.cnf0506_program_end)
? filterOption.cnf0506_program_start
: filterOption.cnf0506_program_end);
}
if (!String.IsNullOrEmpty(filterOption.cnf0502_field_start))
{
cnf0506ProgramFilter = String.Format(@" AND (
[cnf0502_field] >= @Cnf0502FieldStart
AND [cnf0502_field] <= @Cnf0502FieldEnd
)");
sqlCmd.Parameters.AddWithValue("@Cnf0502FieldStart", filterOption.cnf0502_field_start);
sqlCmd.Parameters.AddWithValue("@Cnf0502FieldEnd",
String.IsNullOrEmpty(filterOption.cnf0502_field_end)
? filterOption.cnf0502_field_start
: filterOption.cnf0502_field_end);
}
if (filterOption.adddate_start != null)
{
addDateFilter = String.Format(@" AND (
[adddate]>= @adddateStart
AND [adddate] <= @adddateEnd
)");
sqlCmd.Parameters.AddWithValue("@adddateStart", filterOption.adddate_start);
sqlCmd.Parameters.AddWithValue("@adddateEnd", filterOption.adddate_end ?? filterOption.adddate_start);
}
}
sqlCmd.CommandText = String.Format(@"
SELECT [id]
,[status]
,[cnf0501_file]
,[cnf0502_field]
,[cnf0503_fieldname_tw]
,[cnf0504_fieldname_cn]
,[cnf0505_fieldname_en]
,[cnf0506_program]
,[remark]
,[adduser]
,[adddate]
,[moduser]
,[moddate]
FROM [dbo].[cnf05]
WHERE 1=1
{0}
{1}
{2}
{3}
", addDateFilter, cnf0501FileFilter, cnf0506ProgramFilter, keywordFilter);
using (var sqlReader = sqlCmd.ExecuteReader())
{
if (sqlReader.HasRows)
{
while (sqlReader.Read())
{
Cnf05 cnf05 = new Cnf05();
cnf05.id = Convert.ToInt32(sqlReader["id"]);
cnf05.status = Convert.ToString(sqlReader["status"]);
cnf05.cnf0501_file = Convert.ToString(sqlReader["cnf0501_file"]);
cnf05.cnf0502_field = Convert.ToString(sqlReader["cnf0502_field"]);
cnf05.cnf0503_fieldname_tw = Convert.ToString(sqlReader["cnf0503_fieldname_tw"]);
cnf05.cnf0504_fieldname_cn = Convert.ToString(sqlReader["cnf0504_fieldname_cn"]);
cnf05.cnf0505_fieldname_en = Convert.ToString(sqlReader["cnf0505_fieldname_en"]);
cnf05.cnf0506_program = Convert.ToString(sqlReader["cnf0506_program"]);
cnf05.remark = Convert.ToString(sqlReader["remark"]);
cnf05.adduser = Convert.ToString(sqlReader["adduser"]);
cnf05.adddate = Convert.ToDateTime(sqlReader["adddate"]);
cnf05.moduser = Convert.ToString(sqlReader["moduser"]);
cnf05.moddate = DBNull.Value == sqlReader["moddate"]
? (DateTime?)null
: Convert.ToDateTime(sqlReader["moddate"]);
cnf05List.Add(cnf05);
}
}
}
}
return(cnf05List);
}
public static Cnf05 AddItem(Cnf05 cnf05)
{
if (cnf05 == null)
{
throw new ArgumentNullException("cnf05");
}
if (String.IsNullOrEmpty(cnf05.cnf0501_file))
{
throw new Exception("檔案代號不可以是空白");
}
if (String.IsNullOrEmpty(cnf05.cnf0502_field))
{
throw new Exception("欄位名稱不可以是空白");
}
if (String.IsNullOrEmpty(cnf05.cnf0503_fieldname_tw))
{
throw new Exception("中文說明不可以是空白");
}
using (var conn = new SqlConnection {
ConnectionString = MyConnStringList.AzureGoodeasy
})
using (var sqlCmd = conn.CreateCommand())
{
conn.Open();
sqlCmd.CommandText = @"
INSERT INTO [dbo].[cnf05]
([cnf0501_file]
,[cnf0502_field]
,[cnf0503_fieldname_tw]
,[cnf0504_fieldname_cn]
,[cnf0505_fieldname_en]
,[cnf0506_program]
,[remark]
,[adduser]
,[adddate])
OUTPUT INSERTED.ID
VALUES
(@cnf0501_file
,@cnf0502_field
,@cnf0503_fieldname_tw
,@cnf0504_fieldname_cn
,@cnf0505_fieldname_en
,@cnf0506_program
,@remark
,@adduser
,@adddate)
";
sqlCmd.Parameters.AddWithValue("@cnf0501_file", cnf05.cnf0501_file);
sqlCmd.Parameters.AddWithValue("@cnf0502_field", cnf05.cnf0502_field);
sqlCmd.Parameters.AddWithValue("@cnf0503_fieldname_tw", cnf05.cnf0503_fieldname_tw);
sqlCmd.Parameters.AddWithValue("@cnf0504_fieldname_cn", cnf05.cnf0504_fieldname_cn);
sqlCmd.Parameters.AddWithValue("@cnf0505_fieldname_en", cnf05.cnf0505_fieldname_en);
sqlCmd.Parameters.AddWithValue("@cnf0506_program", cnf05.cnf0506_program);
sqlCmd.Parameters.AddWithValueSafe("@remark", cnf05.remark ?? "");
sqlCmd.Parameters.AddWithValue("@adduser", cnf05.adduser);
sqlCmd.Parameters.AddWithValueSafe("@adddate", cnf05.adddate);
int id = (int)sqlCmd.ExecuteScalar();
cnf05.id = id;
}
return(cnf05);
}
