public Dictionary <string, object> ExportYearTax(Dictionary <string, object> d)
{
Dictionary <string, object> r = new Dictionary <string, object>();//导入结果
try
{
DataSet ds = db.getYearTax(d);
if (ds != null && ds.Tables.Count > 0)
{
string str = d["S_OrgName"].ToString() + d["S_WorkDate"].ToString().Substring(0, 4) + "年";
if (d["S_Department"] != null && !string.IsNullOrEmpty(d["S_Department"].ToString()))
{
str = str + d["S_Department"].ToString();
}
string title = str + "应交地税税费报表";
List <string> col = new List <string>()
{
"D_SL", "total", "totalJS", "totalDJJE"
};
r["item"] = ExcelTools.ExportByTemplet(ds.Tables[0], title, d["S_OrgName"].ToString(), "年度税务报表模板", 4, 4, col);
r["code"] = 2000;
r["message"] = "";
//r["item"] = "\\Files\\export\\" + fileName;
}
else
{
r["message"] = "暂无导出数据";
r["code"] = -1;
}
}
catch (Exception ex)
{
r["code"] = -1;
r["message"] = ex.Message;
}
//r["item"] = filePath;
return(r);
}
static void runTest()
{
ExcelTools objx = new ExcelTools();
string _inFile = "/Users/robsonaugustolazzarinalviani/projects/files/out/112_salesout-week_kobo_20180507093048_569695af00eb813a6e.xlsx";
string _outFile = "/Users/robsonaugustolazzarinalviani/projects/files/out/112_salesout-week_kobo_20180507093048_569695af00eb813a6e.xlsx.csv";
converterToCsvSettings testSettings = new converterToCsvSettings();
testSettings.Sheets = new string[] { "Sheet1" };
testSettings.SkipHidden = true;
objx.excel2csv(_inFile, _outFile, testSettings);
//string _inFile = "/Users/robsonaugustolazzarinalviani/projects/files/in/112_salesout-week_kobo_20180507093048_569695af00eb813a6e.csv";
//string _outFile = "/Users/robsonaugustolazzarinalviani/projects/files/out/112_salesout-week_kobo_20180507093048_569695af00eb813a6e.xlsx";
//objx.csv2excel(_inFile, _outFile);
Console.WriteLine(File.Exists(_outFile) ? "ok" : "error");
Environment.Exit(1);
}
//导入
private void btnImport_Click(object sender, EventArgs e)
{
// 保存对话框
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Execl files (*.xls)|*.xls";
openFileDialog.Title = "选择Excel文件";
openFileDialog.RestoreDirectory = true;
string strErrorMsg = string.Empty;
if (openFileDialog.ShowDialog() == DialogResult.OK)
{
string FileName = openFileDialog.FileName;
DataTable dtData = ExcelTools.ExportToDatatable(FileName, false);
if (dtData.Rows.Count <= 5)
{
SysMessage.InformationMsg("EXCEL文件没有数据！");
return;
}
for (int i = 5; i < dtData.Rows.Count; i++)
{
DataRow row = dtData.Rows[i];
DataRow newRow = dtDetails.NewRow();
newRow["序号"] = row[0];
newRow["产品编号"] = row[1];
newRow["商品编码"] = row[2];
newRow["商品名称"] = row[3];
newRow["商品规格"] = row[4];
newRow["单价"] = row[5];
newRow["币种"] = rowHead["币种"];
newRow["计量单位"] = row[6];
newRow["法定单位"] = row[7];
newRow["换算因子"] = row[8];
dtDetails.Rows.Add(newRow);
}
}
}
public static List <achievement> Read(string filePath)
{
List <achievement> rnt = new List <achievement>();
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
StreamReader sr = new StreamReader(stream);
sr.ReadLine(); //注释
sr.ReadLine(); //类型
sr.ReadLine(); //名称
string lineData = sr.ReadLine();
while (lineData != null)
{
var p = new achievement();
string[] splits = lineData.Split('\t');
p.id = ExcelTools.GetDataCell <int>(splits, 0);
p.icon = ExcelTools.GetDataCell <string>(splits, 1);
p.lv = ExcelTools.GetDataCell <int>(splits, 2);
p.taskname = ExcelTools.GetDataCell <string>(splits, 3);
p.describe = ExcelTools.GetDataCell <string>(splits, 4);
p.type = ExcelTools.GetDataCell <int>(splits, 5);
p.condition = ExcelTools.GetDataCell <string>(splits, 6);
p.gold = ExcelTools.GetDataCell <int>(splits, 7);
p.silver = ExcelTools.GetDataCell <int>(splits, 8);
p.vigour = ExcelTools.GetDataCell <int>(splits, 9);
p.Stamina = ExcelTools.GetDataCell <int>(splits, 10);
p.exp = ExcelTools.GetDataCell <int>(splits, 11);
p.rewards = ExcelTools.GetDataCell <string>(splits, 12);
p.chainid = ExcelTools.GetDataCell <int>(splits, 13);
p.chainseq = ExcelTools.GetDataCell <int>(splits, 14);
rnt.Add(p);
lineData = sr.ReadLine();
}
return(rnt);
}
public Transaction ParseFromLine(string line)
{
try
{
List <string> unparsed;
Transaction iTransaction = new Transaction();
ParseTools.GetParsed <Transaction>(iTransaction, _header, line, _separator, out unparsed);
string[] valueItems = line.SplitAndTrim(_separator);
string dateValue = valueItems[_transactionDateIndex];
int dateValueInt;
if (int.TryParse(dateValue, out dateValueInt))
{
iTransaction.TransactionDate = ExcelTools.FromExcelSerialDate(dateValueInt);
}
return(iTransaction);
}
catch (Exception)
{
}
return(null);
}
private void cmdExportProjectTableToExcel_Click(object sender, System.EventArgs e)
{
try
{
if (_IsLoading.Value)
{
return;
}
using (var locker = new BoolLocker(ref _IsLoading))
{
if (dgvUnusedTable.RowCount >= 1)
{
//Ajout de la liste dans une liste d'object
var dataList = new List <object>();
foreach (var item in (List <TableView>)dgvUnusedTable.DataSource)
{
dataList.Add(item);
}
var excelService = new ExcelTools(new System.Threading.CancellationTokenSource());
var excelList = new List <Library.Excel.Object.ExcelSheet <object> >();
excelList.Add(new Library.Excel.Object.ExcelSheet <object>()
{
DataList = dataList, Lang = "FR"
});
var path = excelService.SendListToNewExcelFile(excelList);
Process.Start(path);
}
}
}
catch (Exception ex)
{
ex.ShowInMessageBox();
}
}
private void fileSubmitButton_Click(object sender, EventArgs e)
// TODO: make a public event handler for messages meant for the user,
// and replace any references to the user message box (errorLabel)
// by firing off an event instead.
{
Spreadsheet spreadsheet;
FormData formData;
// Make sure this can only be ran once (for neatness I guess)
Button goButton = ((Button)sender);
goButton.Enabled = false;
// Make sure the progress bar is at 0 to start with.
ResetProgressBar();
// Get textbox value with the .xlsx file path
string filePath = filePathTextBox.Text;
// Check that the file exists.
if (!File.Exists(filePath))
{
UserMessageBox.Text = UserMessageBox.Text + "Could not find the file specified.\r\n";
goButton.Enabled = true;
return;
}
/// Read xlsx file into a FormData object
try
{
spreadsheet = ExcelTools.ReadExcelFile(filePath);
}
catch (FileFormatException)
{
string tempMessage = "The selected file is either corrupt, or not a valid Microsoft Excel document.\r\n";
UserMessageBox.Text += tempMessage;
DebugTools.Log(tempMessage);
goButton.Enabled = true;
return;
}
formData = ExcelTools.SpreadsheetToFormData(spreadsheet);
/// Submit the form with data read, saving the reference
var reference = WebFormTools.PostFormDataAsync(formData);
/// Wait for the form submission to finish, updating the loading bar in the meantime,
/// but let the user know if something happens during submission / reference retreival.
while (!reference.IsCompleted)
{
if (reference.IsCanceled)
{
string tempMessage = "Form submission cancelled.\r\n";
UserMessageBox.Text += tempMessage;
DebugTools.Log(tempMessage);
goButton.Enabled = true;
return;
}
if (reference.IsFaulted)
{
string tempMessage = "Form submission failed due to an unknown error.\r\n";
UserMessageBox.Text += tempMessage;
DebugTools.Log(tempMessage);
goButton.Enabled = true;
return;
}
// Update the progress bar.
UpdateProgressBar();
// Make sure that the form doesn't hang while waiting.
Refresh();
Application.DoEvents();
}
// Change the progress bar to 100% and change the go button to show that we are done
FillProgressBar();
goButton.Enabled = true;
int errorCode = 0;
string mockReference = "MockReference - 12345678";
foreach (int attempt in new[] { 1, 2 })
{
try
{
if (attempt == 1)
{
errorCode = ExcelTools.AppendToExcelFile(reference.GetAwaiter().GetResult(), filePath);
break; // no need to try again for attempt no. 2
}
else
{
errorCode = ExcelTools.AppendToExcelFile(mockReference, filePath);
break;
}
}
catch (InvalidReferenceException error)
{
string errorMessage = $"{error.Message}\r\nFor demo purposes a mock reference was used: {mockReference}\r\n";
UserMessageBox.Text += errorMessage;
DebugTools.Log(errorMessage);
}
catch (Exception error)
{
string tempMessage = $"An error has occured while saving the submission reference. Additional Info:\r\n{error.Message}\r\n";
UserMessageBox.Text += tempMessage;
DebugTools.Log(tempMessage);
}
}
switch (errorCode)
{
case 0:
break;
case 1:
string errorMessage = "File specified was empty.\r\n";
UserMessageBox.Text += errorMessage;
DebugTools.Log(errorMessage);
goButton.Enabled = true;
return;
case 2:
errorMessage = "Failed to get write permissions to the file.\r\n";
UserMessageBox.Text += errorMessage;
DebugTools.Log(errorMessage);
goButton.Enabled = true;
break;
}
goButton.Enabled = true;
ExcelTools.DisplatResults(filePath, this);
}
private static bool ReadExcel()
{
ExcelPackage package = TableExcelLoader.Load("ExampleData");
if (null == package)
{
return(false);
}
ExcelWorksheet sheet = package.Workbook.Worksheets["ExampleData"];
if (null == sheet)
{
return(false);
}
int defaultKey = new int();
for (int index = 1; index <= sheet.Dimension.Rows; ++index)
{
var tableData = new ExampleData();
int innerIndex = 1;
{
tableData.ID = sheet.Cells[index, innerIndex++].GetValue <int>();
tableData.Name = ExcelTools.GetCellString(sheet.Cells[index, innerIndex++]);
tableData.FloatValue = sheet.Cells[index, innerIndex++].GetValue <float>();
try
{
tableData.EnumValue = (TestEnum)Enum.Parse(typeof(TestEnum), ExcelTools.GetCellString(sheet.Cells[index, innerIndex++]));
}
catch (System.Exception ex)
{
Debug.LogException(ex);
}
int length_FixedList_4 = 5;
int count_FixedList_4 = sheet.Cells[index, innerIndex++].GetValue <int>();
tableData.FixedList = new int[count_FixedList_4];
for (int index_FixedList_4 = 0; index_FixedList_4 < length_FixedList_4; ++index_FixedList_4)
{
if (index_FixedList_4 >= count_FixedList_4)
{
break;
}
tableData.FixedList[index_FixedList_4] = sheet.Cells[index, innerIndex++].GetValue <int>();
}
int length_AutoList_4 = 5;
int count_AutoList_4 = sheet.Cells[index, innerIndex++].GetValue <int>();
tableData.AutoList = new int[count_AutoList_4];
for (int index_AutoList_4 = 0; index_AutoList_4 < length_AutoList_4; ++index_AutoList_4)
{
if (index_AutoList_4 >= count_AutoList_4)
{
break;
}
tableData.AutoList[index_AutoList_4] = sheet.Cells[index, innerIndex++].GetValue <int>();
}
ExampleInnerData obj_InnerData_4 = new ExampleInnerData();
{
obj_InnerData_4.ID = sheet.Cells[index, innerIndex++].GetValue <int>();
int length_AutoList_5 = 5;
int count_AutoList_5 = sheet.Cells[index, innerIndex++].GetValue <int>();
obj_InnerData_4.AutoList = new ExampleInnerInnerData[count_AutoList_5];
for (int index_AutoList_5 = 0; index_AutoList_5 < length_AutoList_5; ++index_AutoList_5)
{
if (index_AutoList_5 >= count_AutoList_5)
{
break;
}
{
obj_InnerData_4.AutoList[index_AutoList_5].ID = sheet.Cells[index, innerIndex++].GetValue <int>();
try
{
obj_InnerData_4.AutoList[index_AutoList_5].EnumValue = (TestEnum)Enum.Parse(typeof(TestEnum), ExcelTools.GetCellString(sheet.Cells[index, innerIndex++]));
}
catch (System.Exception ex)
{
Debug.LogException(ex);
}
}
}
}
tableData.InnerData = obj_InnerData_4;
int length_InnerDataList_4 = 5;
int count_InnerDataList_4 = sheet.Cells[index, innerIndex++].GetValue <int>();
tableData.InnerDataList = new ExampleInnerData[count_InnerDataList_4];
for (int index_InnerDataList_4 = 0; index_InnerDataList_4 < length_InnerDataList_4; ++index_InnerDataList_4)
{
if (index_InnerDataList_4 >= count_InnerDataList_4)
{
break;
}
{
tableData.InnerDataList[index_InnerDataList_4].ID = sheet.Cells[index, innerIndex++].GetValue <int>();
int length_AutoList_6 = 5;
int count_AutoList_6 = sheet.Cells[index, innerIndex++].GetValue <int>();
tableData.InnerDataList[index_InnerDataList_4].AutoList = new ExampleInnerInnerData[count_AutoList_6];
for (int index_AutoList_6 = 0; index_AutoList_6 < length_AutoList_6; ++index_AutoList_6)
{
if (index_AutoList_6 >= count_AutoList_6)
{
break;
}
{
tableData.InnerDataList[index_InnerDataList_4].AutoList[index_AutoList_6].ID = sheet.Cells[index, innerIndex++].GetValue <int>();
try
{
tableData.InnerDataList[index_InnerDataList_4].AutoList[index_AutoList_6].EnumValue = (TestEnum)Enum.Parse(typeof(TestEnum), ExcelTools.GetCellString(sheet.Cells[index, innerIndex++]));
}
catch (System.Exception ex)
{
Debug.LogException(ex);
}
}
}
}
}
}
if (tableData.ID == defaultKey)
{
continue;
}
var existKey = m_data.Find(innerItem => innerItem.ID == tableData.ID);
if (null != existKey)
{
Debug.LogWarning(string.Format("Already has the key {0}, replace the old data.", tableData.ID));
m_data.Remove(existKey);
}
m_data.Add(tableData);
}
return(true);
}
//企业工程BOM导出
private void btnBOMExport_Click(object sender, EventArgs e)
{
if (this.myDataGridViewDetails.CurrentRow == null)
{
return;
}
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\企业工程BOM导入.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\企业工程BOM导入{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
DataTable dtExcel = (DataTable)this.myDataGridViewDetails.DataSource;
DataTable dtTemp = null;
DataTable dtTemp1 = null;
DataTable dtTemp2 = null;
string codevalue = string.Empty;
IDataAccess dataAccess = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_Manufacture);
dataAccess.Open();
int n = 2;
foreach (DataRow row in dtExcel.Rows)
{
if (row["订单号码"] != DBNull.Value && row["订单号码"].ToString() != "")
{
dtTemp = dataAccess.GetTable(string.Format(@"SELECT D.订单号码,DM.订单id, DM.订单明细表id, DM.产品id, DM.配件id
FROM 报关订单表 D LEFT OUTER JOIN 报关订单明细表 DM ON D.订单id = DM.订单id
where D.订单号码= '{0}' AND DM.成品项号={1} AND DM.版本号={2}",
row["订单号码"], row["成品项号"] == DBNull.Value ? 0 :StringTools.intParse(row["成品项号"].ToString()),
row["归并前成品序号"] == DBNull.Value ? 0 : StringTools.intParse(row["归并前成品序号"].ToString())), null);
if (dtTemp.Rows.Count > 0)
{
dtTemp1 = dataAccess.GetTable(string.Format("装箱单BOM明细查询 '{0}',{1},{2},{3},{4}", row["手册编号"], dtTemp.Rows[0]["订单id"],
dtTemp.Rows[0]["订单明细表id"], dtTemp.Rows[0]["产品id"] == DBNull.Value ? 0 : StringTools.intParse(dtTemp.Rows[0]["产品id"].ToString()),
dtTemp.Rows[0]["配件id"] == DBNull.Value ? 0 : StringTools.intParse(dtTemp.Rows[0]["配件id"].ToString())), null);
dtTemp2 = dataAccess.GetTable(string.Format(@"SELECT Q.产品编号 FROM 归并后成品清单 H
LEFT OUTER JOIN 归并前成品清单 Q ON H.归并后成品id = Q.归并后成品id
where H.电子帐册号='{0}' AND H.产品编号='{1}' and Q.序号={2}", row["手册编号"], row["成品项号"],
row["归并前成品序号"] == DBNull.Value ? 0 : StringTools.intParse(row["归并前成品序号"].ToString())), null);
if (dtTemp2.Rows.Count > 0)
{
codevalue = dtTemp2.Rows[0]["产品编号"].ToString();
}
else
{
codevalue = "";
}
foreach (DataRow rowTemp1 in dtTemp1.Rows)
{
ea.SetValue(string.Format("A{0}", n), codevalue);
ea.SetValue(string.Format("B{0}", n), rowTemp1["料号"].ToString());
ea.SetValue(string.Format("C{0}", n), rowTemp1["单耗"].ToString());
ea.SetValue(string.Format("D{0}", n), rowTemp1["损耗率"] == DBNull.Value ? "0" : (StringTools.decimalParse(rowTemp1["损耗率"].ToString()) * 100).ToString());
ea.SetValue(string.Format("E{0}", n), row["归并前成品序号"] == DBNull.Value ? "" : StringTools.intParse(row["归并前成品序号"].ToString()).ToString());
ea.SetValue(string.Format("F{0}", n), row["内部版本号"] == DBNull.Value ? "" : StringTools.intParse(row["内部版本号"].ToString()).ToString());
n++;
}
}
}
}
dataAccess.Close();
ea.Visible = true;
ea.Dispose();
}
public override void tool1_ExportExcel_Click(object sender, EventArgs e)
{
base.tool1_ExportExcel_Click(sender, e);
if (this.myDataGridViewHead.CurrentRow == null)
{
return;
}
if (SysMessage.YesNoMsg("数据是否导入EXCEL文件？") == System.Windows.Forms.DialogResult.No)
{
return;
}
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\清单明细表.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\清单明细表{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
ea.SetValue("A8", "INVOICE NO.:" + myDataGridViewHead.CurrentRow.Cells["装箱单号"].Value.ToString());
ea.SetValue("C8", "CustNo：" + myDataGridViewHead.CurrentRow.Cells["客户代码"].Value.ToString());
ea.SetValue("F8", "DATE：" + Convert.ToDateTime(myDataGridViewHead.CurrentRow.Cells["出货日期"].Value).ToString("yyyy-MM-dd"));
IDataAccess dataAccess = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_Uniquegrade);
dataAccess.Open();
DataTable dtExcel = dataAccess.GetTable(string.Format("select * from 装箱单明细表 where 订单id={0} order by 成品项号", myDataGridViewHead.CurrentRow.Cells["订单id"].Value), null);
dataAccess.Close();
ea.SetValue("E8", dtExcel.Rows.Count > 0 ? dtExcel.Rows[0]["手册编号"].ToString() : "");
int iExcelModelBeginIndex = 10;
int iExcelModelEndIndex = 48;
string 成品名称及商编 = string.Empty;
int iIndex = 0;
for (int iRow = 0; iRow < dtExcel.Rows.Count; iRow++)
{
DataRow row = dtExcel.Rows[iRow];
iIndex = iExcelModelBeginIndex + iRow;
if (iIndex > iExcelModelEndIndex - 1)
{
ea.InsertRows(iIndex);
}
ea.SetValue(string.Format("A{0}", iIndex), row["箱号"] == DBNull.Value ? "" : row["箱号"].ToString());
ea.SetValue(string.Format("B{0}", iIndex), row["客人型号"] == DBNull.Value ? "" : row["客人型号"].ToString());
ea.SetValue(string.Format("C{0}", iIndex), row["归并前成品序号"] == DBNull.Value ? "" : row["归并前成品序号"].ToString());
ea.SetValue(string.Format("D{0}", iIndex), row["成品项号"] == DBNull.Value ? "" : row["成品项号"].ToString());
成品名称及商编 = row["成品名称及商编"] == DBNull.Value ? "" : row["成品名称及商编"].ToString().Trim();
if (成品名称及商编.Length > 0)
{
string[] arr = 成品名称及商编.Split('/');
if (arr.Length > 0)
{
成品名称及商编 = arr[0];
}
}
ea.SetValue(string.Format("E{0}", iIndex), 成品名称及商编);
ea.SetValue(string.Format("F{0}", iIndex), row["成品规格型号"] == DBNull.Value ? "" : row["成品规格型号"].ToString());
ea.SetValue(string.Format("G{0}", iIndex), row["数量"] == DBNull.Value ? "" : row["数量"].ToString());
ea.SetValue(string.Format("H{0}", iIndex), row["单位"] == DBNull.Value ? "" : row["单位"].ToString());
}
//ea.Save(saveFileDialog.FileName);
ea.Visible = true;
ea.Dispose();
}
public override void btnExportExcel_Click(object sender, EventArgs e)
{
base.btnExportExcel_Click(sender, e);
if (this.dataGridViewHead.CurrentRow == null)
{
return;
}
//// 保存对话框
//SaveFileDialog saveFileDialog = new SaveFileDialog();
//saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
//saveFileDialog.FilterIndex = 0;
//saveFileDialog.RestoreDirectory = true;
//saveFileDialog.CreatePrompt = false;
//saveFileDialog.Title = "保存为Excel文件";
////导出的文件名默认为：“订单明细” +客户编码+ [“_” +订单号]（只有多订单的订货类别使用）
//// + “[”+日期(年月日时分秒,如：20141021163144)+“]”，
//// 例如成都人为备量仓导出的Excel文件应为：订单明细201099[20141021163144].xls
//saveFileDialog.FileName = "报关材料明细表";
//if (saveFileDialog.ShowDialog() != DialogResult.OK) return;
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\报关材料明细表.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\报关材料明细表{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
bool isExportDetails = false;
if (SysMessage.YesNoMsg("是否导出单耗明细") == System.Windows.Forms.DialogResult.Yes)
{
isExportDetails = true;
}
//string fn = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\报关材料明细表.xls");
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
ea.SetValue("A2", "订单号码:" + dataGridViewHead.CurrentRow.Cells["订单号码"].Value.ToString());
ea.SetValue("C2", "客户代码：" + dataGridViewHead.CurrentRow.Cells["客户代码"].Value.ToString());
ea.SetValue("F2", "客户名称：" + dataGridViewHead.CurrentRow.Cells["客户名称"].Value.ToString());
ea.SetValue("A3", "出货日期：" + Convert.ToDateTime(dataGridViewHead.CurrentRow.Cells["出货日期"].Value).ToString("yyyy-MM-dd"));
ea.SetValue("C3", "录入日期：" + Convert.ToDateTime(dataGridViewHead.CurrentRow.Cells["录入日期"].Value).ToString("yyyy-MM-dd"));
//int iExcelModelBeginIndex = 5;
//int iExcelModelEndIndex = 30;
string 成品名称及商编 = string.Empty;
int iIndex = 5;
DataTable dtExcel = (DataTable)dataGridViewDetails.DataSource;
foreach (DataRow row in dtExcel.Rows)
{
//iIndex = iExcelModelBeginIndex + iRow;
//if (iIndex > iExcelModelEndIndex - 1)
// ea.InsertRows(iIndex);
ea.SetValue(string.Format("A{0}", iIndex), row["客人型号"] == DBNull.Value ? "" : row["客人型号"].ToString());
ea.SetValue(string.Format("B{0}", iIndex), row["优丽型号"] == DBNull.Value ? "" : row["优丽型号"].ToString());
ea.SetValue(string.Format("G{0}", iIndex), row["订单数量"] == DBNull.Value ? "" : (row["订单数量"].ToString() + " "));
ea.SetValue(string.Format("H{0}", iIndex), row["单位"] == DBNull.Value ? "" : row["单位"].ToString());
iIndex++;
if (isExportDetails)
{
IDataAccess dataAccess = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_Manufacture);
dataAccess.Open();
DataTable dtExcelDetails = dataAccess.GetTable(string.Format("报关预先订单单耗 @订单id={0},@订单明细表id={1}", dataGridViewHead.CurrentRow.Cells["订单id"].Value, row["订单明细表id"]), null);
dataAccess.Close();
ea.SetValue(string.Format("A{0}", iIndex), "材料明细：");
iIndex++;
foreach (DataRow rowD in dtExcelDetails.Rows)
{
ea.SetValue(string.Format("A{0}", iIndex), rowD["料号"] == DBNull.Value ? "" : rowD["料号"].ToString());
ea.SetValue(string.Format("B{0}", iIndex), rowD["料件名"] == DBNull.Value ? "" : rowD["料件名"].ToString());
ea.SetValue(string.Format("C{0}", iIndex), rowD["商品名称"] == DBNull.Value ? "" : rowD["商品名称"].ToString());
decimal d用量 = StringTools.decimalParse(rowD["用量"] == DBNull.Value ? "" : rowD["用量"].ToString());
string str用量 = d用量.ToString("F5");
string str用量单位 = rowD["用量单位"] == DBNull.Value ? "" : rowD["用量单位"].ToString();
ea.SetValue(string.Format("D{0}", iIndex), str用量 + str用量单位);
decimal d订单数量 = StringTools.decimalParse(row["订单数量"] == DBNull.Value ? "" : row["订单数量"].ToString());
ea.SetValue(string.Format("E{0}", iIndex), (d用量 * d订单数量).ToString("F5") + str用量单位);
decimal d单耗 = StringTools.decimalParse(rowD["单耗"] == DBNull.Value ? "" : Convert.ToDecimal(rowD["单耗"]).ToString("F5"));
ea.SetValue(string.Format("F{0}", iIndex), d单耗.ToString());
ea.SetValue(string.Format("G{0}", iIndex), (d单耗 * d订单数量).ToString("F5"));
ea.SetValue(string.Format("H{0}", iIndex), rowD["单位"] == DBNull.Value ? "" : rowD["单位"].ToString());
iIndex++;
}
}
//单元格底部增加一条粗线
if (isExportDetails)
{
iIndex++;
}
}
//ea.Save(saveFileDialog.FileName);
ea.Visible = true;
ea.Dispose();
}
//基本数据导出
private void btnBaseDataExport_Click(object sender, EventArgs e)
{
if (SysMessage.YesNoMsg("数据是否导入EXCEL文件？") == System.Windows.Forms.DialogResult.No)
{
return;
}
long n = 0;
string 计量单位Char, 法定单位Char, 币种Char;
IDataAccess dataAccess = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_Manufacture);
dataAccess.Open();
string strSQL = string.Format("SELECT '1' AS 类型,H.主料,H.四位大类序号,H.序号, H.商品编码, H.商品名称, H.商品规格, H.计量单位, H.币种,H.单价, H.法定单位, H.换算因子, Q.产品编号,Q.商品规格 AS 归并前商品规格, '1' AS 商品类型,H.法定单位 AS 企业单位, Q.单价 AS 归并前单价 FROM 归并后料件清单 H left outer JOIN 归并前料件清单 Q ON H.归并后料件id = Q.归并后料件id where H.电子帐册号={0} order by H.序号,Q.序号", StringTools.SqlQ(gstrManualNo));
DataTable dtData = dataAccess.GetTable(strSQL, null);
dataAccess.Close();
if (dtData.Rows.Count > 0)
{
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\基础数据导入.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\基础数据导入{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
IDataAccess dataGWT = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_GWT);
DataTable dtUnit = null;
DataTable dtCurr = null;
n = 3;
foreach (DataRow row in dtData.Rows)
{
#region 计量单位
if (row["计量单位"] == DBNull.Value && row["计量单位"].ToString().Trim() == "")
{
计量单位Char = "";
}
else
{
dataGWT.Open();
dtUnit = dataGWT.GetTable(string.Format("SELECT * FROM unit where ut_name={0}", StringTools.SqlQ(row["计量单位"].ToString())), null);
dataGWT.Close();
if (dtUnit.Rows.Count > 0)
{
计量单位Char = dtUnit.Rows[0]["Unit"].ToString();
}
else
{
计量单位Char = row["计量单位"].ToString().Trim();
}
}
#endregion
#region 法定单位
if (row["法定单位"] == DBNull.Value && row["法定单位"].ToString().Trim() == "")
{
法定单位Char = "";
}
else
{
dataGWT.Open();
dtUnit = dataGWT.GetTable(string.Format("SELECT * FROM unit where ut_name={0}", StringTools.SqlQ(row["法定单位"].ToString())), null);
dataGWT.Close();
if (dtUnit.Rows.Count > 0)
{
法定单位Char = dtUnit.Rows[0]["Unit"].ToString();
}
else
{
法定单位Char = row["法定单位"].ToString().Trim();
}
}
#endregion
#region 币种
if (row["币种"] == DBNull.Value && row["币种"].ToString().Trim() == "")
{
币种Char = "";
}
else
{
dataGWT.Open();
dtCurr = dataGWT.GetTable(string.Format("SELECT * FROM curr where cr_name={0} or cr_ab={0}", StringTools.SqlQ(row["币种"].ToString())), null);
dataGWT.Close();
if (dtUnit.Rows.Count > 0)
{
币种Char = dtCurr.Rows[0]["curr"].ToString();
}
else
{
币种Char = row["法定单位"].ToString().Trim();
}
}
#endregion
#region 设置单元格的值
ea.SetValue(string.Format("A{0}", n), row["类型"].ToString());
ea.SetValue(string.Format("B{0}", n), row["序号"].ToString());
ea.SetValue(string.Format("C{0}", n), row["商品编码"].ToString());
ea.SetValue(string.Format("E{0}", n), row["商品名称"].ToString());
ea.SetValue(string.Format("F{0}", n), row["商品规格"].ToString());
ea.SetValue(string.Format("G{0}", n), 计量单位Char);
ea.SetValue(string.Format("H{0}", n), 币种Char);
ea.SetValue(string.Format("I{0}", n), row["单价"].ToString());
ea.SetValue(string.Format("K{0}", n), 法定单位Char);
ea.SetValue(string.Format("Q{0}", n), row["四位大类序号"].ToString());
ea.SetValue(string.Format("R{0}", n), row["换算因子"].ToString());
ea.SetValue(string.Format("V{0}", n), row["产品编号"].ToString());
ea.SetValue(string.Format("W{0}", n), row["商品名称"].ToString());
ea.SetValue(string.Format("X{0}", n), row["归并前商品规格"].ToString());
ea.SetValue(string.Format("Y{0}", n), row["商品类型"].ToString());
ea.SetValue(string.Format("Z{0}", n), 法定单位Char);
ea.SetValue(string.Format("AB{0}", n), row["归并前单价"].ToString());
ea.SetValue(string.Format("AC{0}", n), 币种Char);
ea.SetValue(string.Format("AF{0}", n), row["换算因子"].ToString());
ea.SetValue(string.Format("AP{0}", n), row["换算因子"].ToString());
ea.SetValue(string.Format("AS{0}", n), row["归并前单价"].ToString());
ea.SetValue(string.Format("AT{0}", n), row["主料"].ToString() == "主料" ? "1" : "2");
#endregion
n++;
}
ea.Visible = true;
ea.Dispose();
}
}
public Dictionary <string, object> ExportMonthTax(Dictionary <string, object> d)
{
Dictionary <string, object> r = new Dictionary <string, object>();//导入结果
try
{
string filePath = System.IO.Directory.GetCurrentDirectory() + "\\ExcelModel\\Templates\\月度税务报表模板.xls";//原始文件
//Init(filePath);//初始化数据
DataSet ds = db.getMonthTax(d);
if (ds != null && ds.Tables.Count > 0)
{
string str = d["S_OrgName"].ToString() + d["S_WorkDate"].ToString().Substring(0, 7);
if (d["S_Department"] != null && !string.IsNullOrEmpty(d["S_Department"].ToString()))
{
str = str + d["S_Department"].ToString();
}
string title = str + "应交地税税费报表";
//Sheet.GetRow(0).GetCell(0).SetCellValue(title);
//Sheet.GetRow(1).GetCell(0).SetCellValue("制表时间："+DateTime.Now.ToString("yyyy-MM-dd"));
//IRow mySourceRow = Sheet.GetRow(4);
//InsertRow(Sheet, 5, ds.Tables[0].Rows.Count - 1, mySourceRow);
//for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
//{
// Sheet.GetRow(4 + i).GetCell(0).SetCellValue(ds.Tables[0].Rows[i]["D_SL"].ToString());
// Sheet.GetRow(4 + i).GetCell(1).SetCellValue(ds.Tables[0].Rows[i]["total2"].ToString());
// Sheet.GetRow(4 + i).GetCell(2).SetCellValue(ds.Tables[0].Rows[i]["totalJS2"].ToString());
// Sheet.GetRow(4 + i).GetCell(3).SetCellValue(ds.Tables[0].Rows[i]["totalDJJE2"].ToString());
// Sheet.GetRow(4 + i).GetCell(4).SetCellValue(ds.Tables[0].Rows[i]["total"].ToString());
// Sheet.GetRow(4 + i).GetCell(5).SetCellValue(ds.Tables[0].Rows[i]["totalJS"].ToString());
// Sheet.GetRow(4 + i).GetCell(6).SetCellValue(ds.Tables[0].Rows[i]["totalDJJE"].ToString());
//}
////结尾
//Sheet.ForceFormulaRecalculation = true;
////导出文件
//string fileName = title+".xls";
//string path = System.IO.Directory.GetCurrentDirectory() + "\\Files\\export\\" + fileName;
//if (System.IO.File.Exists(path))
//{
// System.IO.File.Delete(path);
//}
//FileStream Reportfile = new FileStream(path, FileMode.Create);
//Workbook.Write(Reportfile);
//Reportfile.Close();
List <string> col = new List <string>()
{
"D_SL", "total2", "totalJS2", "totalDJJE2", "total", "totalJS", "totalDJJE"
};
r["item"] = ExcelTools.ExportByTemplet(ds.Tables[0], title, d["S_OrgName"].ToString(), "月度税务报表模板", 4, 6, col);
r["code"] = 2000;
r["message"] = "";
//r["item"] = "\\Files\\export\\" + fileName;
}
else
{
r["message"] = "暂无导出数据";
r["code"] = -1;
}
}
catch (Exception ex)
{
r["code"] = -1;
r["message"] = ex.Message;
}
//r["item"] = filePath;
return(r);
}
public FileResult ProfitAndLossToExcel(String id, DateTime?fecha1, DateTime?fecha2, bool onlyContributions = false, int houseID = 0)
{
House house = null;
ApplicationUser owner;
//Re-Create the report
if (houseID == 0) //If house is not specified, just
{
owner = db.Users.Find(id);
}
else //If house is specified, the owner is find from it
{
house = db.Houses.Find(houseID);
owner = house.ApplicationUser;
}
ApplicationUser.ProfitAndLossReport profitAndLossReport = owner.generateProfitAndLossReport(fecha1.Value, fecha2.Value, houseID);
//Pre-processed to be exported
List <VMProfitAndLossRow> vmProfitList = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.profitReport.ToList());
DataTable dtProfit = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmProfitList);
List <VMProfitAndLossRow> vmLossList = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.lossReport.ToList());
DataTable dtLoss = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmLossList);
List <VMProfitAndLossRow> vmContribs = VMProfitAndLossRow.listToVMMovements(profitAndLossReport.contributionReport.ToList());
DataTable dtContribs = ExcelTools.listToDatatable <VMProfitAndLossRow>(vmContribs);
List <TableToExportExcel> datatables = new List <TableToExportExcel>();
if (house != null) //If house was specified, house data is added to the Excel File
{
//Getting house data to exportr
VMHouse vmHouse = house.getVM();
var houseList = new List <VMHouse>();
houseList.Add(vmHouse);
DataTable dtHOuse = ExcelTools.listToDatatable <VMHouse>(houseList);
datatables.Add(new TableToExportExcel(dtHOuse, "House"));
}
if (onlyContributions)
{
datatables.Add(new TableToExportExcel(dtContribs, "Contributions"));
}
else
{
datatables.Add(new TableToExportExcel(dtProfit, "Income"));
datatables.Add(new TableToExportExcel(dtLoss, "Loss"));
}
//Exported
ExcelPackage package = ExcelTools.exportToExcel(datatables, onlyContributions? "Contributions" : "Profit and Loss");
var reportTotals = profitAndLossReport.totals;
ExcelWorksheet worksheet = package.Workbook.Worksheets.ElementAt(0);
int startRow = worksheet.Dimension.End.Row + 2;
worksheet.Cells["A" + (startRow)].Value = "Totals";
worksheet.Cells["A" + (startRow)].Style.Font.Size = 20;
startRow++;
int counter = startRow;
foreach (var item in reportTotals)
{
if (onlyContributions && item.concept == ProfitAndLossTotalConcepts.TotalContribution ||
!onlyContributions && (item.concept != ProfitAndLossTotalConcepts.TotalContribution))
{
worksheet.Cells["A" + counter].Value = item.conceptName;
worksheet.Cells["B" + counter].Value = item.total;
counter++;
}
}
// format cells - add borders
using (ExcelRange r = worksheet.Cells[startRow, 1, counter - 1, 2])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
worksheet.InsertRow(1, 2);
worksheet.Cells["A1"].Value = "From";
worksheet.Cells["B1"].Value = fecha1.Value.ToString("dd/MMM/yyyy");
worksheet.Cells["C1"].Value = "To";
worksheet.Cells["D1"].Value = fecha2.Value.ToString("dd/MMM/yyyy");
worksheet.Cells["A1"].Style.Font.Bold = true;
worksheet.Cells["C1"].Style.Font.Bold = true;
if (house == null) //House not specified, owner name is added in the header of the file
{
worksheet.Cells["E1"].Value = "Owner";
worksheet.Cells["E1"].Style.Font.Bold = true;
worksheet.Cells["F1"].Value = owner.OwnerName;
}
byte[] bytesFile = package.GetAsByteArray();
return(File(bytesFile, ExcelTools.EXCEL_MIME_TYPE, profitAndLossReport.ToString(onlyContributions) + ExcelTools.EXCEL_FORMAT));
}
public void SendGetFile()
{
var liste = new List <FakeClass>();
liste.Add(new FakeClass()
{
Tata = "efze1", Toto = "efe", Date = DateTime.Now, Decimal = 1.89m, Int = 10
});
liste.Add(new FakeClass()
{
Tata = "efze2", Toto = "efe"
});
liste.Add(new FakeClass()
{
Tata = "efzef3", Toto = "efe", Date = DateTime.Now, Decimal = 1.89m, Int = 10
});
liste.Add(new FakeClass()
{
Tata = "efzef4", Toto = "efe"
});
liste.Add(new FakeClass()
{
Toto = "efe"
});
liste.Add(new FakeClass()
{
Toto = "efe"
});
var liste2 = new List <FakeClass2>();
liste2.Add(new FakeClass2()
{
Tata = "efze1", Toto = "efe"
});
liste2.Add(new FakeClass2()
{
Tata = "julien", Toto = "efe"
});
liste2.Add(new FakeClass2()
{
Tata = "efzef3", Toto = "efe"
});
liste2.Add(new FakeClass2()
{
Tata = "efzef4", Toto = "efe"
});
liste2.Add(new FakeClass2()
{
Toto = "efe"
});
liste2.Add(new FakeClass2()
{
Toto = "efe"
});
var excel = new ExcelTools(new CancellationTokenSource());
//écriture du fichier
var excelList = new List <Library.Excel.Object.ExcelSheet <object> >();
var listeObject = new List <object>();
foreach (var item in liste)
{
listeObject.Add(item);
}
var listeObject2 = new List <object>();
foreach (var item in liste2)
{
listeObject2.Add(item);
}
excelList.Add(new Library.Excel.Object.ExcelSheet <object>()
{
DataList = listeObject, SheetName = "1"
});
excelList.Add(new Library.Excel.Object.ExcelSheet <object>()
{
DataList = listeObject2, SheetName = "2"
});
var fullFilePath = excel.SendListToNewExcelFile(excelList);
var columnIndexList = new List <int>()
{
1, 2, 3, 4, 5
};
//Lecture du fichier
var dataList = excel.GetListFromExcelFile(fullFilePath, columnIndexList, 1, true, 1);
Assert.AreEqual(7, dataList.Count);
var dataList2 = excel.GetListFromExcelFile(@"E:\Développements\Logiciels\Library\_UnitTestData\test.xlsx", columnIndexList, 1, true, 1);
Assert.AreEqual(4, dataList2.Count);
}
public override void tool1_Print_Click(object sender, EventArgs e)
{
base.tool1_Print_Click(sender, e);
int custid = 0;
DataGridViewRow dgv = null;
if (this.myDataGridViewHead.CurrentRowNew != null)
{
if (this.myDataGridViewHead.Rows[this.myDataGridViewHead.CurrentRowNew.Index].Cells["custid"].Value != DBNull.Value)
{
custid = (int)this.myDataGridViewHead.Rows[this.myDataGridViewHead.CurrentRowNew.Index].Cells["custid"].Value;
dgv = this.myDataGridViewHead.Rows[this.myDataGridViewHead.CurrentRowNew.Index];
}
else
{
return;
}
}
else
{
return;
}
string sql = string.Empty;
DataTable rs = null;
DataTable rs1 = null;
DataTable rs2 = null;
string st3 = string.Empty;
IDataAccess dataAccess = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_Uniquegrade);
dataAccess.Open();
sql = string.Format("select * from customer where custid={0}", custid);
rs = dataAccess.GetTable(sql, null);
dataAccess.Close();
List <string> listMark = new List <string>();
if (dgv.Cells["mark1"].Value != DBNull.Value && dgv.Cells["mark1"].ToString().Trim() != "")
{
listMark.Add(dgv.Cells["mark1"].Value.ToString());
}
if (dgv.Cells["mark2"].Value != DBNull.Value && dgv.Cells["mark2"].ToString().Trim() != "")
{
listMark.Add(dgv.Cells["mark2"].Value.ToString());
}
if (dgv.Cells["mark3"].Value != DBNull.Value && dgv.Cells["mark3"].ToString().Trim() != "")
{
listMark.Add(dgv.Cells["mark3"].Value.ToString());
}
if (dgv.Cells["mark4"].Value != DBNull.Value && dgv.Cells["mark4"].ToString().Trim() != "")
{
listMark.Add(dgv.Cells["mark4"].Value.ToString());
}
if (dgv.Cells["mark5"].Value != DBNull.Value && dgv.Cells["mark5"].ToString().Trim() != "")
{
listMark.Add(dgv.Cells["mark5"].Value.ToString());
}
if (dgv.Cells["mark6"].Value != DBNull.Value && dgv.Cells["mark6"].ToString().Trim() != "")
{
listMark.Add(dgv.Cells["mark6"].Value.ToString());
}
if (dgv.Cells["mark7"].Value != DBNull.Value && dgv.Cells["mark7"].ToString().Trim() != "")
{
listMark.Add(dgv.Cells["mark7"].Value.ToString());
}
int pid = Convert.ToInt32(dgv.Cells["pid"].Value);
sql = string.Format("SELECT SUM(Quantity) AS Quantity, Unit FROM PackingDetail1 where pid = {0} GROUP BY Unit", pid); //单位数量汇总
dataAccess.Open();
rs2 = dataAccess.GetTable(sql);
sql = string.Format("SELECT count(id) AS totalbox FROM dbo.PackingDetail1 WHERE (PackageNo IS NOT NULL) and pid={0}", pid); //总数量汇总
rs1 = dataAccess.GetTable(sql);
//sql = string.Format("SELECT SUM(Quantity*unitprice) AS Totalprice FROM PackingDetail1 where pid={0}", pid); //总金额汇总
//DataTable rs5 = dataAccess.GetTable(sql);
//decimal totalPrice = 0;
//if (rs5.Rows.Count > 0)
// totalPrice = Convert.ToDecimal(rs5.Rows[0]["Totalprice"]);
dataAccess.Close();
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\出口装箱单明细.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\出口装箱单明细{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
//string fn = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\制造单清单明细表.xls");
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
DataRow rowCust = rs.Rows[0];
ea.SetValue("C9", " " + dgv.Cells["INVOICENO"].Value.ToString());
ea.SetValue("I9", " " + Convert.ToDateTime(dgv.Cells["ExportDate"].Value).ToString("yyyy-MM-dd"));
ea.SetValue("C12", " " + rowCust["e_name"].ToString());
ea.SetValue("F14", " " + dgv.Cells["PackingFrom"].Value.ToString());
ea.SetValue("C15", " " + dgv.Cells["PackingTo"].Value.ToString());
int iMark = 11;
for (int i = 0; i < listMark.Count; i++)
{
ea.SetValue(string.Format("H{0}", iMark + i), listMark[i]);
}
int iExcelModelBeginIndex = 19;
DataTable dtDetail = (DataTable)this.myDataGridViewDetails.DataSource;
int iIndex = iExcelModelBeginIndex;
int iCount = dtDetail.Rows.Count;
decimal nw = 0;
decimal gw = 0;
for (int iRow = 0; iRow < dtDetail.Rows.Count; iRow++)
{
DataRow row = dtDetail.Rows[iRow];
//if (iCount > 1 && iRow < iCount - 1)
//{
ea.InsertRows(iIndex + 1);
ea.SetMerge(string.Format("A{0}:B{0}", iIndex));
ea.SetHorisontalAlignment(string.Format("A{0}:B{0}", iIndex), 3); //居中
//ea.SetBorderStyle(string.Format("A{0}:B{0}", iIndex), 1);
ea.SetMerge(string.Format("C{0}:F{0}", iIndex));
ea.SetHorisontalAlignment(string.Format("C{0}:F{0}", iIndex), 1); //左对齐
ea.SetHorisontalAlignment(string.Format("G{0}", iIndex), 4); //右对齐
ea.SetMerge(string.Format("H{0}:I{0}", iIndex));
ea.SetHorisontalAlignment(string.Format("H{0}:I{0}", iIndex), 4);
//ea.SetBorderStyle(string.Format("H{0}:I{0}", iIndex), 4);
ea.SetHorisontalAlignment(string.Format("J{0}", iIndex), 4);
//}
ea.SetValue(string.Format("A{0}", iIndex), row["PackageNo"] == DBNull.Value ? "" : row["PackageNo"].ToString());
ea.SetValue(string.Format("C{0}", iIndex), row["品名规格型号"] == DBNull.Value ? "" : row["品名规格型号"].ToString());
ea.SetValue(string.Format("G{0}", iIndex), string.Format("{0}{1}", row["QUANTITY"] == DBNull.Value ? "" : Convert.ToDecimal(row["QUANTITY"]).ToString("F2"), row["UNIT"].ToString().Trim()));
ea.SetValue(string.Format("H{0}", iIndex), string.Format("{0}{1}", row["NW"] == DBNull.Value ? "" : Convert.ToDecimal(row["NW"]).ToString("F2"), row["UNIT1"].ToString().Trim()));
ea.SetValue(string.Format("J{0}", iIndex), string.Format("{0}{1}", row["GW"] == DBNull.Value ? "" : Convert.ToDecimal(row["GW"]).ToString("F2"), row["UNIT2"].ToString().Trim()));
nw += (row["NW"] == DBNull.Value ? 0 : Convert.ToDecimal(row["NW"]));
gw += (row["GW"] == DBNull.Value ? 0 : Convert.ToDecimal(row["GW"]));
ea.SetRowHeight(string.Format("A{0}", iIndex), 20);
iIndex++;
}
ea.SetRowHeight(string.Format("A{0}", iIndex), 2);
ea.SetRowHeight(string.Format("A{0}", iIndex + 1), 2);
iIndex = iIndex + 2;
//ea.SetValue(string.Format("C{0}", iIndex), "TOTAL:");
//ea.SetHorisontalAlignment(string.Format("C{0}", iIndex), 4); //右对齐
//Font font = new Font("楷体", 12, FontStyle.Bold);
//ea.SetFont(string.Format("C{0}", iIndex), font);
ea.SetValue(string.Format("D{0}", iIndex), rs1.Rows[0]["totalbox"].ToString());
//ea.SetHorisontalAlignment(string.Format("D{0}", iIndex), 3); //居中
//ea.SetFont(string.Format("D{0}", iIndex), font);
//ea.SetValue(string.Format("E{0}", iIndex), "CARTONS");
//ea.SetHorisontalAlignment(string.Format("E{0}", iIndex), 1); //左对齐
////Font font = new Font("楷体", 12, FontStyle.Bold);
//ea.SetFont(string.Format("E{0}",iIndex), font);
if (nw > 0)
{
ea.SetMerge(string.Format("H{0}:I{0}", iIndex));
ea.SetValue(string.Format("H{0}", iIndex), nw.ToString("F2") + "KGS");
ea.SetHorisontalAlignment(string.Format("H{0}", iIndex), 4); //右对齐
}
if (gw > 0)
{
ea.SetValue(string.Format("J{0}", iIndex), gw.ToString("F2") + "KGS");
ea.SetHorisontalAlignment(string.Format("J{0}", iIndex), 4); //右对齐
}
for (int i = 0; i < rs2.Rows.Count; i++)
{
DataRow row = rs2.Rows[i];
ea.SetValue(string.Format("G{0}", iIndex), string.Format("{0}{1}", row["QUANTITY"] == DBNull.Value ? "" : Convert.ToDecimal(row["QUANTITY"]).ToString("F2"), row["UNIT"].ToString().Trim()));
ea.SetHorisontalAlignment(string.Format("G{0}", iIndex), 4);
ea.SetRowHeight(string.Format("A{0}", iIndex), 20);
iIndex++;
}
ea.Visible = true;
ea.Dispose();
}
private static void Run(Options opts)
{
if (opts.Command == "import")
{
VerifyImportArgs(opts);
ExcelReader er = new ExcelReader(opts.ExcelFile);
using (DssWriter w = new DssWriter(opts.DssFile))
{
if (opts.Sheets.ToList <string>().Count == 0)
{
for (int i = 0; i < er.WorksheetCount; i++)
{
var t = er.CheckType(i);
if (t is RecordType.RegularTimeSeries || t is RecordType.IrregularTimeSeries)
{
w.Write(er.Read(i) as TimeSeries);
}
else if (t is RecordType.PairedData)
{
w.Write(er.Read(i) as PairedData);
}
}
}
else
{
foreach (var sheet in opts.Sheets)
{
var t = er.CheckType(sheet);
if (t is RecordType.RegularTimeSeries || t is RecordType.IrregularTimeSeries)
{
w.Write(er.Read(sheet) as TimeSeries);
}
else if (t is RecordType.PairedData)
{
w.Write(er.Read(sheet) as PairedData);
}
}
}
}
}
else if (opts.Command == "export")
{
VerifyExportArgs(opts);
using (DssReader r = new DssReader(opts.DssFile))
{
object record;
ExcelWriter ew = new ExcelWriter(opts.ExcelFile);
if (opts.Sheets.ToList().Count == 0)
{
for (int i = 0; i < opts.Paths.ToList <string>().Count; i++)
{
string sheet = "dssvue_import_" + ExcelTools.RandomString(5);
DssPath p = new DssPath(opts.Paths.ElementAt(i));
var type = r.GetRecordType(p);
if (type is RecordType.RegularTimeSeries || type is RecordType.IrregularTimeSeries)
{
record = r.GetTimeSeries(p);
ew.Write(record as TimeSeries, sheet);
}
else if (type is RecordType.PairedData)
{
record = r.GetPairedData(p.FullPath);
ew.Write(record as PairedData, sheet);
}
}
}
else
{
for (int i = 0; i < opts.Sheets.ToList().Count; i++)
{
DssPath p = new DssPath(opts.Paths.ElementAt(i));
var type = r.GetRecordType(p);
if (type is RecordType.RegularTimeSeries || type is RecordType.IrregularTimeSeries)
{
record = r.GetTimeSeries(p);
ew.Write(record as TimeSeries, opts.Sheets.ElementAt(i));
}
else if (type is RecordType.PairedData)
{
record = r.GetPairedData(p.FullPath);
ew.Write(record as PairedData, opts.Sheets.ElementAt(i));
}
}
}
}
}
}
private static void Init()
{
//获取当前实例
instance=EditorWindow.GetWindow<ExcelTools>();
//初始化
pathRoot=Application.dataPath;
//注意这里需要对路径进行处理
//目的是去除Assets这部分字符以获取项目目录
//我表示Windows的/符号一直没有搞懂
pathRoot=pathRoot.Substring(0,pathRoot.LastIndexOf("/"));
excelList=new List<string>();
scrollPos=new Vector2(instance.position.x,instance.position.y+75);
}
/// <summary>
/// 使用空白模版导出EXCEL
/// </summary>
/// <param name="dtExcel">需要导出的数据集</param>
/// <param name="strTitle">EXCEL的主标题</param>
public static void ExportIntoExcel(DataTable dtExcel, string strTitle)
{
//// 保存对话框
//SaveFileDialog saveFileDialog = new SaveFileDialog();
//saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
//saveFileDialog.FilterIndex = 0;
//saveFileDialog.RestoreDirectory = true;
//saveFileDialog.CreatePrompt = false;
//saveFileDialog.Title = "保存为Excel文件";
////导出的文件名默认为：“订单明细” +客户编码+ [“_” +订单号]（只有多订单的订货类别使用）
//// + “[”+日期(年月日时分秒,如：20141021163144)+“]”，
//// 例如成都人为备量仓导出的Excel文件应为：订单明细201099[20141021163144].xls
//saveFileDialog.FileName = strTitle;
//if (saveFileDialog.ShowDialog() != DialogResult.OK) return;
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\空白模版.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\{0}{1}.xls", strTitle, DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
//string fn = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\空白模版.xls");
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
int iColCount = dtExcel.Columns.Count;
int iIndex = 1;
#region 设置EXCEL标题行
if (strTitle.Length > 0)
{
string strMaxLetter = getColLetter(iColCount); //计算最大列的字母
string range = string.Format("A{0}:{1}{0}", iIndex, strMaxLetter);
ea.SetValue(range, strTitle);
//水平居中
ea.SetHorisontalAlignment(range, 3);
ea.SetVerticalAlignment(range, 3);
Font font = new Font("楷体", 20, FontStyle.Bold);
ea.SetFont(range, font);
//合并首行
ea.SetMerge(range);
iIndex++;
}
#endregion
#region 循环处理数据集
Dictionary <string, long> dicColMaxValue = new Dictionary <string, long>(); //存放列值的最大字符串长度
string strColLetter = string.Empty; //列对应的字母
string strCellText = string.Empty; //单元格对应的文本内容
long lCellLenght; //单元格文本的长度
#region 处理列标题
for (int iCol = 0; iCol < iColCount; iCol++)
{
strColLetter = getColLetter(iCol + 1);
strCellText = dtExcel.Columns[iCol].Caption;
lCellLenght = StringTools.TextLenght(strCellText);
//ea.SetValue(iIndex, iCol + 1,strCellText); //这里的列是从1开始的，所以iCol + 1
ea.SetValue(string.Format("{0}{1}", strColLetter, iIndex), strCellText);
if (dicColMaxValue.ContainsKey(strColLetter)) //如果字典中已经包含该列的数据，则跟当前文本判断长度，存储较大的长度值
{
if (dicColMaxValue[strColLetter] < lCellLenght)
{
dicColMaxValue[strColLetter] = lCellLenght;
}
}
else
{
dicColMaxValue.Add(strColLetter, lCellLenght);
}
}
iIndex++;
#endregion
//foreach (DataRow row in dtExcel.Rows)
DataRow row = null;
for (int iRow = 0; iRow < dtExcel.Rows.Count; iRow++)
{
row = dtExcel.Rows[iRow];
if (row.RowState == DataRowState.Deleted)
{
continue;
}
for (int iCol = 0; iCol < iColCount; iCol++)
{
strColLetter = getColLetter(iCol + 1);
strCellText = row[iCol].ToString();
lCellLenght = StringTools.TextLenght(strCellText);
//ea.SetValue(iIndex, iCol + 1,strCellText); //这里的列是从1开始的，所以iCol + 1
ea.SetValue(string.Format("{0}{1}", strColLetter, iIndex), row[iCol].ToString());
if (dicColMaxValue.ContainsKey(strColLetter)) //如果字典中已经包含该列的数据，则跟当前文本判断，存储字符长度较长的文本
{
if (dicColMaxValue[strColLetter] < lCellLenght)
{
dicColMaxValue[strColLetter] = lCellLenght;
}
}
else
{
dicColMaxValue.Add(strColLetter, lCellLenght);
}
}
iIndex++;
}
//设置每个列的宽度
foreach (KeyValuePair <string, long> item in dicColMaxValue)
{
ea.SetColumnWidth(string.Format("{0}2", item.Key), item.Value + 2); //最大字符串长度+2，多出点空隙
}
#endregion
//ea.Save(saveFileDialog.FileName);
ea.Visible = true;
ea.Dispose();
}
public override void tool1_ExportExcel_Click(object sender, EventArgs e)
{
base.tool1_ExportExcel_Click(sender, e);
if (SysMessage.YesNoMsg("数据是否导入EXCEL文件？") == System.Windows.Forms.DialogResult.No)
{
return;
}
if (this.myDataGridViewHead.CurrentRow == null)
{
return;
}
//// 保存对话框
//SaveFileDialog saveFileDialog = new SaveFileDialog();
//saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
//saveFileDialog.FilterIndex = 0;
//saveFileDialog.RestoreDirectory = true;
//saveFileDialog.CreatePrompt = false;
//saveFileDialog.Title = "保存为Excel文件";
////导出的文件名默认为：“订单明细” +客户编码+ [“_” +订单号]（只有多订单的订货类别使用）
//// + “[”+日期(年月日时分秒,如：20141021163144)+“]”，
//// 例如成都人为备量仓导出的Excel文件应为：订单明细201099[20141021163144].xls
//saveFileDialog.FileName =string.Format("预先订单【{0}】",myDataGridViewHead.CurrentRow.Cells["出库单号"].Value.ToString()) ;
//if (saveFileDialog.ShowDialog() != DialogResult.OK) return;
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\制造单清单明细表.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\制造单清单明细表{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
//string fn = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\制造单清单明细表.xls");
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
ea.SetValue("A8", "出库单号:" + myDataGridViewHead.CurrentRow.Cells["出库单号"].Value.ToString());
ea.SetValue("D8", "CustNo：" + myDataGridViewHead.CurrentRow.Cells["客户代码"].Value.ToString());
ea.SetValue("F8", "DATE：" + Convert.ToDateTime(myDataGridViewHead.CurrentRow.Cells["出货日期"].Value).ToString("yyyy-MM-dd"));
IDataAccess dataAccess = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_Manufacture);
dataAccess.Open();
DataTable dtExcel = dataAccess.GetTable(string.Format("select * from 报关预先订单明细表 where 订单id={0}", myDataGridViewHead.CurrentRow.Cells["订单id"].Value), null);
dataAccess.Close();
int iExcelModelBeginIndex = 10;
int iExcelModelEndIndex = 30;
string 成品名称及商编 = string.Empty;
int iIndex = 0;
for (int iRow = 0; iRow < dtExcel.Rows.Count; iRow++)
{
DataRow row = dtExcel.Rows[iRow];
iIndex = iExcelModelBeginIndex + iRow;
if (iIndex > iExcelModelEndIndex - 1)
{
ea.InsertRows(iIndex);
}
ea.SetValue(string.Format("A{0}", iIndex), row["客人型号"] == DBNull.Value ? "" : row["客人型号"].ToString());
ea.SetValue(string.Format("B{0}", iIndex), row["优丽型号"] == DBNull.Value ? "" : row["优丽型号"].ToString());
ea.SetValue(string.Format("C{0}", iIndex), row["成品项号"] == DBNull.Value ? "" : row["成品项号"].ToString());
成品名称及商编 = row["成品名称及商编"] == DBNull.Value ? "" : row["成品名称及商编"].ToString().Trim();
if (成品名称及商编.Length > 0)
{
string[] arr = 成品名称及商编.Split('/');
if (arr.Length > 0)
{
成品名称及商编 = arr[0];
}
}
ea.SetValue(string.Format("D{0}", iIndex), 成品名称及商编);
ea.SetValue(string.Format("E{0}", iIndex), row["成品规格型号"] == DBNull.Value ? "" : row["成品规格型号"].ToString());
ea.SetValue(string.Format("F{0}", iIndex), row["订单数量"] == DBNull.Value ? "" : row["订单数量"].ToString());
ea.SetValue(string.Format("G{0}", iIndex), row["单位"] == DBNull.Value ? "" : row["单位"].ToString());
}
//ea.Save(saveFileDialog.FileName);
ea.Visible = true;
ea.Dispose();
}
public override void tool1_PrintView_Click(object sender, EventArgs e)
{
//base.tool1_PrintView_Click(sender, e);
if (SysMessage.YesNoMsg("数据是否导入EXCEL文件？") == System.Windows.Forms.DialogResult.No)
{
return;
}
FormMergeRelationProduct_SesialSelect objForm = new FormMergeRelationProduct_SesialSelect();
if (objForm.ShowDialog() == System.Windows.Forms.DialogResult.Cancel)
{
return;
}
DataTable dtHead = (DataTable)myDataGridViewHead.DataSource;
//DataTable dtDetails = (DataTable)myDataGridViewDetails.DataSource;
IDataAccess dataAccess = DataAccessFactory.CreateDataAccess(DataAccessEnum.DataAccessName.DataAccessName_Manufacture);
dataAccess.Open();
string strSQL = string.Format("SELECT '2' AS 类型,H.主料,H.四位大类序号, H.序号, H.商品编码, H.商品名称, H.商品规格, H.计量单位, H.币种,H.单价, H.法定单位, H.换算因子, Q.产品编号,Q.商品规格 AS 归并前商品规格, '2' AS 商品类型, Q.单价 AS 归并前单价 FROM 归并后成品清单 H left outer JOIN 归并前成品清单 Q ON H.归并后成品id = Q.归并后成品id where H.电子帐册号={0} and Q.序号>={1} and Q.序号<={2} and Q.归并后成品id={3} order by H.序号,Q.序号", StringTools.SqlQ(gstrManualNo), objForm.iSerialBegin, objForm.iSerialEnd, myDataGridViewHead.CurrentRowNew.Cells["归并后成品id"].Value);
DataTable dtDetails = dataAccess.GetTable(strSQL, null);
dataAccess.Close();
if (dtHead.Rows.Count > 0)
{
long n = 0;
string strSourceFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\归并关系表.xls");
string strDestFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, string.Format(@"ExcelTemp\归并关系表{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss")));
File.Copy(strSourceFile, strDestFile);
File.SetAttributes(strDestFile, File.GetAttributes(strDestFile) | FileAttributes.ReadOnly);
string fn = strDestFile;
ExcelTools ea = new ExcelTools();
ea.SafeOpen(fn);
ea.ActiveSheet(1); // 激活
n = 6;
DataGridViewRow gridViewRowHead = myDataGridViewHead.CurrentRowNew;
ea.SetValue(string.Format("N{0}", n), DateTime.Now.ToString("yyyy-MM-ddd"));
ea.SetValue(string.Format("J{0}", n), gridViewRowHead.Cells["序号"].Value.ToString());
ea.SetValue(string.Format("K{0}", n), gridViewRowHead.Cells["产品编号"].Value.ToString());
ea.SetValue(string.Format("L{0}", n), gridViewRowHead.Cells["商品编码"].Value.ToString());
ea.SetValue(string.Format("M{0}", n), gridViewRowHead.Cells["商品名称"].Value.ToString());
ea.SetValue(string.Format("N{0}", n), gridViewRowHead.Cells["商品规格"].Value.ToString());
ea.SetValue(string.Format("O{0}", n), gridViewRowHead.Cells["单价"].Value.ToString());
ea.SetValue(string.Format("P{0}", n), gridViewRowHead.Cells["计量单位"].Value.ToString());
ea.SetValue(string.Format("Q{0}", n), gridViewRowHead.Cells["法定单位"].Value.ToString());
ea.SetValue(string.Format("R{0}", n), gridViewRowHead.Cells["换算因子"].Value.ToString());
foreach (DataRow row in dtDetails.Rows)
{
ea.SetValue(string.Format("A{0}", n), row["序号"].ToString());
ea.SetValue(string.Format("B{0}", n), row["产品编号"].ToString());
ea.SetValue(string.Format("C{0}", n), row["商品编码"].ToString());
ea.SetValue(string.Format("D{0}", n), row["商品名称"].ToString());
ea.SetValue(string.Format("E{0}", n), row["商品规格"].ToString());
ea.SetValue(string.Format("F{0}", n), row["单价"].ToString());
ea.SetValue(string.Format("G{0}", n), row["计量单位"].ToString());
ea.SetValue(string.Format("H{0}", n), row["法定单位"].ToString());
ea.SetValue(string.Format("I{0}", n), row["换算因子"].ToString());
n++;
}
ea.Visible = true;
ea.Dispose();
}
}